SQL Quest

Designing an analytical query

Lesson 1 of 11 · 20 min0/4 checked in practice
Objective

By the end of this lesson you can restate a business request as a metric, a result grain, filters and a period, write the query in that order, and check that the answer is plausible before anyone sees it.

Why it matters: Most wrong numbers on dashboards are not SQL errors: the query runs, but it answers a slightly different question (lines instead of orders, calendar year instead of fiscal year, Saturday/Sunday instead of the Saudi weekend). Designing the question first is how analysts avoid that.

Dataset: Wadi Market analytics mart. Examples run in the editor on the right.

This track works mostly on the Wadi Market analytics mart (mart): a reporting database that the data team loads every night from the operational systems. It is shaped for analysis, not for taking orders, so its tables are called facts (things that happened and can be measured, such as order lines) and dimensions (the context you slice by, such as dates, products, customers and channels). You will study that design properly in the star-schema lessons. For now, one rule: facts join to dimensions on the *_key columns.

Before any of that, though, comes the habit that separates a BI analyst from someone who can write SQL: designing the question before writing the query.

Five decisions before the first SELECT

A request such as “Can I get sales by month for this year?” hides at least five decisions:

  1. Metric. Which number is “sales”? Gross amount before discounts, net amount after discounts, net of returns? Orders, or order lines?
  2. Result grain. What does one row of the answer represent? Here: one row per calendar month. Saying it out loud tells you the GROUP BY and the expected row count (12).
  3. Source grain. What does one row of the table you aggregate represent? fact_sales is one order line, so count(*) counts lines, not orders.
  4. Filters. Which rows are in scope? All channels? Returned lines? Guest checkouts?
  5. Period. “This year” calendar 2025, or Wadi Market’s fiscal year, which runs April to March?

Write those answers down, even as a comment at the top of the query. The SQL then almost writes itself, and anyone reviewing your work can check the decisions instead of reverse-engineering them.

-- Question : <the business question in one sentence>
-- Metric   : <exact formula, e.g. sum(net_amount) / count(DISTINCT order_id)>
-- Grain    : one row per <month | channel | customer ...>
-- Filters  : <what is in and out of scope>
-- Period   : <calendar or fiscal, dates inclusive>
SELECT <grain columns>, <metrics>
FROM <fact table>
JOIN <dimensions> ON <surrogate keys>
WHERE <filters and period>
GROUP BY <grain columns>
ORDER BY <grain columns>;
A query spec as a comment block, then the query that implements it.
ExampleKnow your source grain: lines versus orders
SELECT count(*)                 AS order_lines,
       count(DISTINCT order_id) AS orders,
       round(count(*)::numeric / count(DISTINCT order_id), 2) AS lines_per_order
FROM fact_sales;

fact_sales has one row per order line, so count() is more than twice the number of orders. Any metric “per order” (average order value, items per order) must divide by count(DISTINCT order_id), never by count().

Write it in the order you designed it

With the decisions made, build the query in small steps: join the fact to the dimensions you need, filter, group at the result grain, and compute the metrics. Use the date dimension (dim_date) for calendar logic instead of re-deriving it with date functions: it already knows each day’s month, fiscal year, weekend flag and season, and every dashboard that uses it agrees.

ExampleNet sales and average order value by year
SELECT d.year,
       count(DISTINCT f.order_id) AS orders,
       sum(f.net_amount)          AS net_sales,
       round(sum(f.net_amount) / count(DISTINCT f.order_id), 2) AS avg_order_value
FROM fact_sales AS f
JOIN dim_date AS d ON d.date_key = f.date_key
GROUP BY d.year
ORDER BY d.year;

Three rows, one per calendar year (the declared grain). Net sales grow every year. Average order value is a ratio of two sums (total net sales divided by number of orders), which is what “average order value” means; averaging net_amount over lines would give the average line value instead.

Quick check
Quick check

A dashboard tile labelled “Average order value” is computed as AVG(net_amount) over fact_sales. What does it actually show?

Verify before you publish

A result that runs is not yet a result you can trust. Three quick checks catch most mistakes:

  • Row count matches the grain. Monthly for one year → 12 rows. Seven rows means missing months; 24 means a second grouping column slipped in.
  • Totals reconcile. The sum of your breakdown should equal the total computed directly from the fact table. If it does not, a join dropped or duplicated rows.
  • Spot-check one value. Recompute one cell (one month, one channel) with the simplest possible query.
ExampleReconcile a breakdown to the total
WITH by_channel AS (
  SELECT f.channel_key, sum(f.net_amount) AS net_sales
  FROM fact_sales AS f
  JOIN dim_date AS d ON d.date_key = f.date_key
  WHERE d.year = 2025
  GROUP BY f.channel_key
)
SELECT (SELECT sum(net_sales) FROM by_channel) AS sum_of_breakdown,
       (SELECT sum(f.net_amount)
        FROM fact_sales AS f
        JOIN dim_date AS d ON d.date_key = f.date_key
        WHERE d.year = 2025)                 AS direct_total;

Both columns show the same figure, which proves the channel breakdown neither lost nor double-counted any 2025 sales. Keep a check like this next to any breakdown you build; it takes seconds and catches fan-out and filter mistakes immediately.

Now practise
4 exercises on real data, checked in your browser (practice).
PreviousNext: Defining KPIs precisely