Problem Statement
You must flag customers whose latest order value is above their category’s average order. Outline a clean SQL approach.
Explanation
First, identify each customer’s latest order using ROW_NUMBER over orders partitioned by customer and ordered by date. Filter to rn equals one to keep the latest row. Next, compute the average per category in a separate step.
Join the two steps and compare the latest order amount to the category average. This avoids correlated re-computations and keeps the intent simple to tune.
Code Solution
SolutionRead Only
WITH latest AS (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, id DESC) rn
FROM orders o
), avg_cat AS (
SELECT category_id, AVG(total) AS avg_total FROM orders GROUP BY category_id
)
SELECT l.customer_id, l.total > a.avg_total AS above_avg
FROM latest l
JOIN avg_cat a ON a.category_id = l.category_id
WHERE l.rn = 1;