SQL Aggregations SQL Topic exerciseHardVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Governed Monthly KPI Table

Return one governed KPI row per region and calendar month that has an eligible order.

  • Joins
  • Subqueries
  • Aggregation
  • CASE expressions
  • Date analysis

Exercise brief

Understand the request

Analytics engineering manager The executive dashboard needs reusable monthly KPIs with one declared grain and one documented eligible-order population.

Build a reconciled region-month metric table from normalized order, item, and payment facts.

Return

  • Return region_id, region_code, order_year, order_month, eligible_order_count, identified_customer_count, recognized_revenue, captured_order_count, captured_order_rate in this exact left-to-right order.

Constraints

  • Use orders from 2025-01-01 through 2025-06-30 with a half-open upper boundary and exclude CANCELLED orders.
  • Normalize valid item revenue and whether each order has any CAPTURED payment before calculating region-month metrics.
  • Count distinct non-NULL customers; retain eligible orders with unresolved customers or no valid item revenue.
  • Calculate captured_order_rate with eligible orders as the denominator: captured orders divided by eligible orders, multiplied by 100 and rounded to 2 decimals.
  • Sort by order_year, order_month, and region_id.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

regions

  • region_idINTEGER
  • region_codeVARCHAR(30)

orders

  • order_idINTEGER
  • customer_idINTEGER
  • region_idINTEGER
  • statusVARCHAR(30)
  • ordered_atDATETIME

order_items

  • order_idINTEGER
  • quantityINTEGER
  • unit_priceDECIMAL(10,2)

payments

  • order_idINTEGER
  • payment_statusVARCHAR(30)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Declare the order fact first: one eligible order with one revenue value and one captured-payment flag.

Hint 2

Pre-aggregate items and payment attempts to order_id, join those summaries to filtered orders, then aggregate the normalized facts by region and month.

Hint 3

WITH item_totals AS (/* one row per order */), payment_flags AS (/* one row per order */), order_facts AS (/* eligible order grain with period */) SELECT /* region-month keys and governed metrics */ FROM order_facts f JOIN regions r ON /* stable region key */ GROUP BY /* complete region-month grain */ ORDER BY /* chronological grain */;

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH item_totals AS (SELECT order_id, SUM(CASE WHEN quantity IS NOT NULL AND unit_price IS NOT NULL THEN quantity * unit_price ELSE 0 END) AS item_revenue FROM order_items GROUP BY order_id), payment_flags AS (SELECT order_id, MAX(CASE WHEN payment_status = 'CAPTURED' THEN 1 ELSE 0 END) AS captured_flag FROM payments GROUP BY order_id), order_facts AS (SELECT o.order_id, o.customer_id, o.region_id, CAST(strftime('%Y', o.ordered_at) AS INTEGER) AS order_year, CAST(strftime('%m', o.ordered_at) AS INTEGER) AS order_month, COALESCE(i.item_revenue, 0) AS recognized_revenue, COALESCE(p.captured_flag, 0) AS captured_flag FROM orders o LEFT JOIN item_totals i ON i.order_id = o.order_id LEFT JOIN payment_flags p ON p.order_id = o.order_id WHERE o.ordered_at >= '2025-01-01 00:00:00' AND o.ordered_at < '2025-07-01 00:00:00' AND o.status <> 'CANCELLED') SELECT r.region_id, r.region_code, f.order_year, f.order_month, COUNT(*) AS eligible_order_count, COUNT(DISTINCT f.customer_id) AS identified_customer_count, SUM(f.recognized_revenue) AS recognized_revenue, SUM(f.captured_flag) AS captured_order_count, ROUND(CAST(SUM(f.captured_flag) * 100.0 / NULLIF(COUNT(*), 0) AS NUMERIC), 2) AS captured_order_rate FROM order_facts f INNER JOIN regions r ON r.region_id = f.region_id GROUP BY r.region_id, r.region_code, f.order_year, f.order_month ORDER BY f.order_year, f.order_month, r.region_id;

Why this works

Correctness: the order_facts stage gives every KPI the same eligible-order population and prevents item or payment cardinality from changing order counts. Edge case: February Northwest includes an incomplete-revenue order, UNASSIGNED has an unresolved customer, and cancelled order 1005 is excluded at the half-open boundary. Portability: the staged aggregation is portable, while year/month extraction differs across engines.

Success check

Every region-month key is unique, all metrics reconcile to the same eligible-order population, and captured_order_rate remains between 0 and 100.

Expected result

Use this output to verify values, aliases, ordering, and row count.

region_idregion_codeorder_yearorder_montheligible_order_countidentified_customer_countrecognized_revenuecaptured_order_countcaptured_order_rate
1NORTHEAST20251111901100
1NORTHEAST20252111001100
2NORTHWEST2025222000
6UNASSIGNED2025210801100
4WEST20253111501100
3SOUTHEAST20254112401100
4WEST2025411601100
1NORTHEAST20255119000
2NORTHWEST20255111201100
2NORTHWEST20256111201100

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.