How would you retrieve the first and last order for each customer from a sales table?

To get first and last order per customer, use window functions MIN/MAX over order dates, or FIRST_VALUE/LAST_VALUE. Example: SELECT customer_id, order_id, order_date, FIRST_VALUE(order_id) OVER (PARTITION BY customer_id ORDER BY order_date) AS first_order_id, LAST_VALUE(order_id) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order_id FROM sales. Simpler: SELECT customer_id, MIN(order_date) AS first_order, MAX(order_date) AS last_order FROM sales GROUP BY customer_id. To get full rows: WITH ranked AS (SELECT , ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) rn_first, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) rn_last FROM sales) SELECT FROM ranked WHERE rn_first=1 OR rn_last=1; Why it matters: Design choices compound at scale—wrong approach can cause 100× overhead. Scalability trade-offs: Profile before optimizing; validate on sample then full. Cost implications: Suboptimal choices multiply at billion-row scale.