How would you write a SQL query to find the first and last purchase dates for each customer-product pair, ensuring that each pair has at least two completed orders?

To solve this, first filter the orders table for rows where status = 'completed'. Use a CTE to join orders with order_items and keep only the relevant columns. Then aggregate by customer_id and product_id: compute MIN(order_date) as first_purchase_date, MAX(order_date) as last_purchase_date, and COUNT(DISTINCT order_id) as order_count. Finally, filter the aggregated result where order_count >= 2. The query runs in O(n) time, where n is the number of completed order rows, because each step is a single scan or hash aggregation. This approach is straightforward, readable, and performs well on large datasets.