How can you retrieve the latest order per customer and filter customers with total spend greater than 500 using SQL?

To get the latest order per customer and then filter customers whose total spend exceeds $500, you can combine a window function with an aggregation. One approach is:

WITH latest AS (

SELECT customer_id,

     order_id,
     order_date,
     ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) AS rn

FROM orders

),

spend AS (

SELECT customer_id,

     SUM(amount) AS total_spend

FROM orders

GROUP BY customer_id

)

SELECT l.customer_id,

   l.order_id,
   l.order_date,
   s.total_spend

FROM latest l

JOIN spend s ON l.customer_id = s.customer_id

WHERE l.rn = 1

AND s.total_spend > 500;

The CTE 'latest' picks the most recent order per customer. The CTE 'spend' aggregates total spend per customer. The final query joins them, keeps only the latest order rows, and filters on total_spend > 500. Complexity is O(n) for the scans and O(n log n) for the windowing, which is acceptable for typical data volumes.