Customers with 3 consecutive buying months
SQL
Hard
16 views
Problem Description
Find customers who placed at least one order in 3 consecutive months (any year). Return customer_id only. Hint: convert to month buckets, then use a window row_number trick.
Sample Test Case
Input:
Orders(customer_id, order_date)
Constraints
Assume PostgreSQL-style DATE_TRUNC and interval math
Official Solution
SELECT customer_id FROM (SELECT customer_id, month, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY month) AS rn FROM (SELECT DISTINCT customer_id, DATE_TRUNC('month', order_date) AS month FROM Orders) m) t GROUP BY customer_id, (month - (rn * INTERVAL '1 month')) HAVING COUNT(*) >= 3;
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!