How to group records from 2 tables based on highest and lowest value of a column in oracle SQL?

I have 2 tables table_A and table_B with the below structure:

table_A

ref	amnt	dt_trans
B1	147.50	30-Nov-2024
B1	50.50	30-Nov-2024
B1	150.70	01-Nov-2024
B1	70.50	01-Nov-2024
B2	160.50	02-Dec-2024

table_B

policy	ref	anuity_amnt
P1	B1	1500
P2	B1	700
P3	B2	600

From the above 2 tables, first I have to group the records in table_A as per the date(dt_trans) with highest and lowest vales. For ex, for 30th Nov, 2024 the highest value is 147.50 and lowest value is 50.50, while for 1st Nov, 2024, highest value is 150.70 and lowest value is 70.50. For table_B, for Ref (B1), the highest value (1500) goes to P1 and lowest value (700) goes to P2. So, the final table will look like

table_C

ref	amnt	dt_trans	policy
B1	147.50	30-Nov-2024	P1
B1	50.50	30-Nov-2024	P2
B1	150.70	01-Nov-2024	P1
B1	70.50	01-Nov-2024	P2

and so on ....

So, far I have tried to arrange the table_A only as per dt_trans, but unable to find the solution, as I am not expert in SQL. Can anyone please help?

SELECT 
      ref
      ,amnt
      ,dt_trans OVER (PARTITION BY dt_trans) 
 FROM 
      table_A;

Solution

One of the options to do it could be to flag the rows with highest and lowest values in both tables separately (using case expressions) and then join the queries ON ref and flag columns.

SELECT     a.ref, a.amnt, a.dt_trans, b.policy 
FROM       ( Select ref, amnt, dt_trans, Count(*) Over(Partition By dt_trans) as cnt_dt,
                    Case When amnt = Min(amnt) Over(Partition By dt_trans) 
                         Then 'Low' 
                         When amnt = Max(amnt) Over(Partition By dt_trans) 
                         Then 'High' 
                    End as flag    -- flag high and low rows
             From   table_A 
           ) a 
INNER JOIN ( Select policy, ref, anuity_amnt, Count(*) Over(Partition By ref) as cnt_ref,
                    Case When anuity_amnt = Min(anuity_amnt) Over(Partition By ref) 
                         Then 'Low' 
                         When anuity_amnt = Max(anuity_amnt) Over(Partition By ref) 
                         Then 'High' 
                    End as flag    -- flag high and low rows
             From   table_B
           ) b ON( b.ref = a.ref And 
                   b.flag = a.flag )
WHERE     cnt_dt > 1 And cnt_ref > 1   -- omit this if you want one row match to be fetched
ORDER BY  dt_trans desc, policy

R e s u l t :

REF	AMNT	DT_TRANS	POLICY
B1	147.5	2024-11-30	P1
B1	50.5	2024-11-30	P2
B1	150.7	2024-11-01	P1
B1	70.5	2024-11-01	P2

fiddle

NOTE:
The Alex Poole's remarks below his answer are applicable to this answer too.