Write a SQL query that returns the first and last purchase dates for each customer-product pair, only including pairs with at least two completed orders.
1Times asked
Jul 2026Last seen
Jul 2026First seen
💡 Model Answer
Create a CTE named completed_orders that joins orders and order_items, filtering status = 'completed'. In the main query, group by customer_id and product_id, selecting MIN(order_date) as first_date, MAX(order_date) as last_date, and COUNT(DISTINCT order_id) as order_cnt. Add a HAVING clause to keep only rows where order_cnt >= 2. This query uses a single aggregation pass and is efficient for large tables.
This answer was generated by AI for study purposes. Use it as a starting point — personalize it with your own experience.
🎤 Get questions like this answered in real-time
Assisting AI listens to your interview, captures questions live, and gives you instant AI-powered answers on a discreet on-screen overlay.
Get Assisting AI — Starts at ₹500