Describe how you would use a CTE and window functions to rank purchases by date and then filter for the first and last occurrences for each customer-product pair.

First, create a CTE that selects completed orders with customer_id, product_id, order_date, and order_id. Then use ROW_NUMBER() OVER (PARTITION BY customer_id, product_id ORDER BY order_date ASC) as rn_first and ROW_NUMBER() OVER (PARTITION BY customer_id, product_id ORDER BY order_date DESC) as rn_last. In the outer query, filter where rn_first = 1 and rn_last = 1 to get the first and last dates. Join the two results on customer_id and product_id to produce a single row per pair. This method uses two window functions and a self-join, running in O(n) time with efficient index usage.