I have a table of client leads and a table of client transactions. I want to use a window function to match leads to transactions in a way that would join transactions with the oldest valid lead (or leads), that haven't been matched yet. In other words, after a lead is matched with a transaction, it should not be matched with any newer transactions (however it should be matched with all transactions that client made that day).
Each lead is only valid for 0 - 10 days and is client specific.
Example and code:
In this example transaction_id 1 matches with lead_id 1 and lead_id 2. Transaction_id 2 matches with lead_id 3. etc.
leads
| lead_id | lead_date | client_id |
|---|---|---|
| 1 | 2022-01-01 | 1 |
| 2 | 2022-01-01 | 1 |
| 3 | 2022-01-02 | 1 |
| 4 | 2022-01-05 | 1 |
| 5 | 2022-01-09 | 1 |
| 6 | 2022-01-30 | 1 |
| 7 | 2022-01-09 | 2 |
| 8 | 2022-01-10 | 2 |
| 9 | 2022-01-11 | 2 |
transactions
| transaction_id | transaction_date | client_id |
|---|---|---|
| 1 | 2022-01-02 | 1 |
| 2 | 2022-01-03 | 1 |
| 3 | 2022-01-04 | 1 |
| 4 | 2022-01-17 | 1 |
| 5 | 2022-01-15 | 2 |
| 6 | 2022-01-15 | 2 |
| 7 | 2022-01-17 | 2 |
DDL:
CREATE TABLE #lead ( lead_id int, lead_date date, client_id int)
INSERT INTO #lead
SELECT *
FROM
( VALUES
(1, '20220101', 1 )
,(2, '20220101', 1 )
,(3, '20220102', 1 )
,(4, '20220105', 1 )
,(5, '20220109', 1 )
,(6, '20220130', 1 )
,(7, '20220109', 2 )
,(8, '20220110', 2 )
,(9, '20220111', 2 )
) x (lead_date, lead_id, client_id)
CREATE TABLE #transaction (transaction_id int, transaction_date date, client_id int)
INSERT INTO #transaction
SELECT *
FROM (
VALUES
(1, '20220102',1)
,(2, '20220103',1)
,(3, '20220104',1)
,(4, '20220117',1)
,(5, '20220115',2)
,(6, '20220115',2)
,(7, '20220117',2)
) x (transaction_id, transaction_date, client_id)
This joins client leads to transactions based on 10 day validity:
SELECT *
FROM #lead l
JOIN #transaction t
ON l.client_id = t.client_id
AND DATEADD(DD,10, l.lead_date) > t.transaction_date
Expected result:
| lead_id | lead_date | client_id | transaction_id | transaction_date |
|---|---|---|---|---|
| 1 | 20220101 | 1 | 1 | 20220102 |
| 2 | 20220101 | 1 | 1 | 20220102 |
| 3 | 20220102 | 1 | 2 | 20220103 |
| 5 | 20220109 | 1 | 4 | 20220117 |
| 7 | 20220109 | 2 | 5 | 20220115 |
| 7 | 20220109 | 2 | 6 | 20220115 |
| 8 | 20220110 | 2 | 7 | 20220117 |
Note that using DENSE_RANK() OVER (PARTITION BY client_id ORDER BY date_column) to calculate the order on both tables and then simply joining them by the new column does not work. In my example transaction_id 3 should not be part of the result, since lead_id 1,2 and 3 have already been matched to earlier transactions. Therefore there are no valid leads to match with transaction_id 3.
SELECT *
FROM (
SELECT
l.lead_id
,l.lead_date
,l.client_id
,t.transaction_id
,t.transaction_date
,lead_rank = DENSE_RANK() OVER(PARTITION BY l.client_id ORDER BY l.lead_date)
,transaction_rank = DENSE_RANK() OVER(PARTITION BY t.client_id ORDER BY t.transaction_date)
FROM #lead l
JOIN #transaction t
ON l.client_id = t.client_id
AND DATEADD(DD,10, l.lead_date) > t.transaction_date
) x
WHERE lead_rank = transaction_rank
Is there a way how to utilize window functions here without using loops or multiple statements? I feel like I'm missing a simple solution to this, but i can't quite put my finger on it.