I have a table of transactions that includes txn_date and cust_id.
For each customer that had a transaction in December, I want to know how many transactions that customer had in the 90 days previous to the given transaction.
This seems to be a query that I could run with a window function and a RANGE sliding window, but Snowflake doesn't support the RANGE sliding window frame.
How can I run this query in Snowflake?
How about something like this:
WITH T1 AS (
SELECT CUSTOMER_ID, TX_DATE
FROM TRANSACTIONS
WHERE TX_DATE BETWEEN '2020-12-01' AND '2020-12-31')
SELECT T2.CUSTOMER_ID, T2.TX_DATE
FROM TRANSACTIONS T2
INNER JOIN T1 ON T2.CUSTOMER_ID = T2.CUSTOMER_ID
WHERE T2.TX_DATE BETWEEN (T1.TX_DATE - 90) AND T1.TX_DATE
So much the same is NickW's answer at first.
WITH data AS (
SELECT txn_date::timestamp_ntz as txn_date, cust_id, txn_id
FROM VALUES
('2020-12-04',0, 0),
('2020-12-03',1, 1),
('2020-11-04',1, 2),
('2020-10-04',1, 3),
('2020-09-04',1, 4), -- just on 90 days
('2020-09-02',1, 5), -- too far
('2021-01-05',1, 6) -- in the future
v(txn_date , cust_id, txn_id)
), dec_txn AS (
SELECT txn_id,
cust_id,
DATEADD('day',-90, txn_date) AS win_start,
txn_date AS win_end
FROM data
WHERE date_trunc('month', txn_date) = '2020-12-01'
)
SELECT dt.*
,t.*
,datediff('days', dt.win_end, t.txn_date) as win_time
FROM dec_txn AS dt
LEFT JOIN data AS t
ON t.cust_id = dt.cust_id
AND t.txn_date between dt.win_start and win_end AND t.txn_id != dt.txn_id
;
which gives:
TXN_ID CUST_ID WIN_START WIN_END TXN_DATE CUST_ID TXN_ID WIN_TIME
1 1 2020-09-04 00:00:00.000 2020-12-03 00:00:00.000 2020-11-04 00:00:00.000 1 2 -29
1 1 2020-09-04 00:00:00.000 2020-12-03 00:00:00.000 2020-10-04 00:00:00.000 1 3 -60
1 1 2020-09-04 00:00:00.000 2020-12-03 00:00:00.000 2020-09-04 00:00:00.000 1 4 -90
0 0 2020-09-05 00:00:00.000 2020-12-04 00:00:00.000 NULL NULL NULL NULL
thus to counts we:
WITH data AS (
SELECT txn_date::timestamp_ntz as txn_date, cust_id, txn_id
FROM VALUES
('2020-12-04',0, 0),
('2020-12-03',1, 1),
('2020-11-04',1, 2),
('2020-10-04',1, 3),
('2020-09-04',1, 4), -- just on 90 days
('2020-09-02',1, 5), -- too far
('2021-01-05',1, 6) -- in the future
v(txn_date , cust_id, txn_id)
), dec_txn AS (
SELECT txn_id,
cust_id,
txn_date,
DATEADD('day',-90, txn_date) AS win_start,
txn_date AS win_end
FROM data
WHERE date_trunc('month', txn_date) = '2020-12-01'
)
SELECT dt.cust_id
,dt.txn_id
,dt.txn_date
,count(t.txn_id) as c__prior_90_days_transaction
FROM dec_txn AS dt
LEFT JOIN data AS t
ON t.cust_id = dt.cust_id
AND t.txn_date >= dt.win_start and t.txn_date < dt.win_end AND t.txn_id != dt.txn_id
GROUP BY 1,2,3
ORDER BY 1,2
;
giving:
CUST_ID TXN_ID TXN_DATE C__PRIOR_90_DAYS_TRANSACTION
0 0 2020-12-04 00:00:00.000 0
1 1 2020-12-03 00:00:00.000 3
What is not well defined in the question is what to do if there are many requests in december for one customer What to do if there are multiple transactions in the same december day.
The above will return a row for each Dec transaction per customer, and it includes transactions that happen on the same day. But if you date/timestamp has time then it will only count transtions earlier in the same day. But if you want prior days and the txn_date is just a date then
AND t.txn_date >= dt.win_start and t.txn_date < dt.win_end AND t.txn_id != dt.txn_id
should be used.
if txn_date is a timestamp, then dec_txn should be altered to:
dec_txn AS (
SELECT txn_id,
cust_id,
DATEADD('day',-90, txn_date::date) AS win_start,
txn_date::date AS win_end
FROM data
WHERE date_trunc('month', txn_date) = '2020-12-01'
and now that the window timestamps are truncated to days, then you will have to workout if you want midnight transaction to count on the day, or if you don't have midnight timestamps...
