SQL at Work #2: Finding Users Who Bought Again Within a Week

This is a practice solution for StrataScratch question ID 10553.

This query finds Amazon customers who made at least 2 purchases within any 7-day window, which can signal high purchase frequency and feed retargeting campaigns or churn analysis.

WITH datediff_cte AS (
    SELECT user_id,
    datediff(created_at,
        LAG(created_at) OVER (
            PARTITION BY user_id 
            ORDER BY created_at)
    ) AS datediff_purchase
    FROM amazon_transactions
)
SELECT DISTINCT user_id FROM datediff_cte
WHERE datediff_purchase BETWEEN 1 AND 7;

How it works

Partition by user
PARTITION BY user_id resets the window for each customer, so the date differences are calculated within each person’s own history.
Look back with LAG
LAG(created_at) fetches the previous purchase date for each row, ordered by date. datediff() then measures the date difference.
Filter
BETWEEN 1 AND 7 identifies cases in which customers made a follow-up purchase within a week, without a same-day purchase. DISTINCT ensures each customer appears once.