Exam 10: Mixed Challenge
Difficulty: Expert
Topics covered: CTEs, window functions, multi-table JOINs, CASE WHEN, aggregation, subqueries
Each question shows the target output. Write a SQL query that produces exactly those columns and rows. There is often more than one valid approach.
Question 1
For each customer, compute their total number of orders (order_count), total amount spent (total_spent), and a segment label: 'High Value' if total spent >= 1000, 'Mid Value' if between 100 and 999, 'Low Value' if below 100. Include customers with no orders, treating their total spent as 0. Return name, order_count, total_spent, and segment.
Question 2
For each product category, find the total number of orders (order_count) and total revenue (total_revenue). Join orders with products to get the category. Return category, order_count, and total_revenue.
Question 3
Using ROW_NUMBER(), find each customer's most recent order. Then filter to only customers whose most recent order was 'cancelled' or 'pending'. Return customer_name, last_order_date, and last_order_status.
Question 4
For each order, show the customer's total lifetime spend using SUM() OVER (PARTITION BY customer_id) with no ORDER BY. Return order_id, customer_id, total_amount, and lifetime_spend.
Question 5
Using a CTE and window functions, produce a monthly revenue summary. Group orders by strftime('%Y-%m', order_date) aliased as month. Return month, order_count, total_revenue, and running_total (cumulative revenue through that month).