HomeInterview QuestionsWrite a SQL query that returns the first and last …

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.

🟡 Medium Coding Junior level
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