Exam 9: Window Functions
Difficulty: Advanced
Topics covered: OVER(), PARTITION BY, ROW_NUMBER, RANK, LAG, running totals with SUM() OVER
Each question shows the target output. Write a SQL query that produces exactly those columns and rows.
Question 1
Rank all orders by total_amount (highest first) using RANK(). Return order_id, total_amount, and rank.
Question 2
Rank each customer's orders by total_amount descending using RANK() OVER (PARTITION BY customer_id ...). Return customer_id, order_id, total_amount, and rank_within_customer.
Question 3
Calculate a running total of total_amount across all orders ordered by order_date. Return order_id, order_date, total_amount, and running_total.
Question 4
For each order (ordered by order_date), show the previous order's total_amount using LAG aliased as prev_order_amount, and the difference from the current order as amount_change. Return order_id, order_date, total_amount, prev_order_amount, and amount_change.
Question 5
Using ROW_NUMBER() partitioned by customer_id and ordered by order_date descending, find each customer's most recent order (row number = 1). Return customer_id, order_id, order_date, total_amount, and status.