SQL Core 10-Exercise Track

This is a full graded practice track from easy to hard.

Prerequisites:

• SELECT, WHERE, GROUP BY
• JOIN basics
• CTEs and window function basics

Milestone A (Exercises 1-3): filter and base aggregations
Milestone B (Exercises 4-7): joins and business metrics
Milestone C (Exercises 8-10): window functions and fanout-safe KPI modeling

Exit criteria:

• pass at least 8 of 10 exercises
• pass both Exercise 9 and Exercise 10

Return completed orders with order_id, customer_id, order_date, order_total sorted by date then id.

Return top 5 customers by completed-order revenue with columns customer_id, completed_orders, revenue.

Join orders and customers and return order_id, customer_name, country, order_total for completed orders.

Using completed orders only, return category-level metrics: category, gross_sales, item_rows.

Compute category-level gross_sales, return_amount, and return_rate (returns/gross_sales).

Build month-level KPI output: month_start, revenue, return_amount, net_revenue, return_share.

Return customers with at least 2 completed orders and columns customer_id, first_order_date, completed_orders, total_revenue.

Using window functions, return top 2 customers by completed-order revenue per month with month_start, customer_id, revenue (rounded to 2 decimals), rank_in_month. Rank with ROW_NUMBER ordered by revenue descending, breaking revenue ties by customer_id ascending. Some months have more than 2 purchasing customers, so the rank filter must actually drop rows.

Compute net order totals for completed orders using a fanout-safe pattern. Return order_id, order_total, return_amount, net_order_total.

Capstone drill: build month x country KPIs for completed orders.
Return:

• month_start
• country
• orders
• revenue
• return_amount
• net_revenue
• aov

Recommended pass target:

• 8/10 exercises passed
• both hard fanout-safe KPI exercises passed (9 and 10)

If you fail an exercise, rewrite from scratch once. This usually improves retention more than small edits.

After this track:

• move to Joins and Aggregation Drills
• then complete an industry assignment:
• Insurance KPI Assignments
• eCommerce KPI Assignments