Reviewed by Aditya Kumar · Last reviewed 2026-03-24
SELECT customer_id FROM (SELECT customer_id, order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order, DATEDIFF(order_date, LAG(order_date) OVER (...)) AS days_since_prev FROM orders) t WHERE prev_order IS NOT NULL AND days_since_prev > 90....
SELECT customer_id FROM (SELECT customer_id, order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order, DATEDIFF(order_date, LAG(order_date) OVER (...)) AS days_since_prev FROM orders) t WHERE prev_order IS NOT NULL AND days_since_prev > 90. Why: LAG gives previous order date; DATEDIFF to gap. Refine per definition of "second order" (e.g., first repeat). Business impact: Churn/retention signals.
Red Flag: Ignoring customers with only one order (prev_order IS NULL). Pro-Move: 'We exclude single-order; 90-day threshold tuned by cohort analysis.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.