SQL - Get MIN of a column for each key in other table-CodePudding

I have two tables as such:

ORDERS

Date	TransactID	COL3
2021-06	1234	4
2021-09	1238	8

Agg

Date	User	TransactID
2021-06	3333	1234
2021-03	3333	XXXX
2021-02	3333	XXXX
2021-09	4444	1238
2021-05	4444	XXXX
2021-01	4444	XXXX

In AGG, a User can have many transactions, the ORDERS table is just a subset of it.

For each TransactID in Orders, I need to go into the Agg table and get the MIN date for the User associated with the TransactID.

Then, I need to calculate the date difference between the ORDERS.Date and the minimum AGG.DATE. The result is stored in SDP.COL3. COL3 can basically be described as Days Since First Transaction.

I have never done a SQL problem that is this multistep, and need some guidance. Any help would be greatly appreciated!

CodePudding user response：

If I've got it right

select SDP.TXN_ID, sdp.dt, datediff(sdp.dt, min(a1.DT)) diff
from SDP
join AGG a1 on a1.UserID =
  (select a2.UserID 
   from AGG a2 
   where SDP.TXN_ID = a2.TXN_ID
   order by a2.UserID
   limit 1)
group by SDP.TXN_ID, sdp.dt

You can omit

   order by a2.UserID
   limit 1

provided each transaction is always belonging to a single user.

The fiddle

CodePudding user response：

based on your SQL Fidddle (http://sqlfiddle.com/#!9/101497/1) this should get you started

SELECT TXN_ID, DT, USERID
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY sdp.TXN_ID ORDER BY  sdp.DT ASC) AS [index],
             sdp.TXN_ID,
             sdp.DT,
             agg.USERID 
      FROM sdp
      LEFT JOIN agg ON sdp.TXN_ID = agg.TXN_ID) A
WHERE [index] = 1

For more information you should look at

https://www.sqlshack.com/sql-partition-by-clause-overview/

https://www.sqltutorial.org/sql-window-functions/sql-partition-by/

https://learnsql.com/blog/partition-by-with-over-sql/