How to Build a SQL Query From Scratch

Following a SQL solution and starting from a blank editor are different skills. You must decide what one row means, which tables produce it, where rows multiply, and how to test the answer. Build small, verifiable transformations instead of guessing the final query.

The reusable framework is simple: define the result, map the data, build in stages, and validate every change in grain.

Start with a precise business request

Suppose an ecommerce team asks for a report for the last full calendar month. Return one row for every customer with at least one completed order, with these columns:

• customer_id
• completed_order_count
• net_revenue, defined here as the sum of quantity * unit_price on completed-order items
• average_order_value, defined as net revenue divided by completed orders
• last_order_at
• top_category and its revenue

Break category-revenue ties alphabetically. Keep customers whose completed orders have no items, showing zero revenue and no top category. Assume the PostgreSQL session timezone is the reporting timezone.

The example source contract is orders at one row per order with customer_id BIGINT NOT NULL; order_items at one row per line with quantity INTEGER NOT NULL and unit_price NUMERIC(12,2) NOT NULL; and products at one row per product.

Step 1: write the grain and output contract

The grain is what one row represents. Write it before writing SQL:

Final grain: one row per customer who had at least one completed order in the last full calendar month.

Turn the requested columns into an output contract so hidden decisions become visible.

Now every intermediate result can have a named grain and every output a derivation.

Step 2: map tables, keys, and join cardinality

Map the keys and cardinality:

• orders.order_id identifies one order.
• order_items.order_id connects many lines to one order.
• products.product_id connects one product to many lines.
• orders.customer_id connects many orders to one customer.

Joining items changes order grain to line grain, so COUNT(*) would count lines. Keep order and item metrics separate until both reach customer grain.

Before trusting a dimension join, confirm that its key is unique. This check should return no rows:

SELECT
    product_id,
    COUNT(*) AS row_count
FROM products
GROUP BY product_id
HAVING COUNT(*) > 1;

If it returns rows, resolve the key violation instead of hiding it with DISTINCT.

Step 3: isolate eligibility before adding detail

Define the window once and filter while the data is at order grain. The complete query below uses a params CTE for the first instants of the previous and current months, then an eligible_orders CTE with ordered_at >= start_at and ordered_at < end_at. This half-open interval handles every time of day without inventing an end-of-month timestamp.

The stage contract is one row per eligible order. Filtering here gives every downstream measure the same date and status definition. Item and product joins come later because they change the grain.

Step 4: choose aggregation or a window function by intent

Use aggregation when several source rows must become one result row. It produces orders per customer, revenue per customer, and revenue per customer-category. Use a window when rows stay separate but need context such as rank. Here the window belongs after category aggregation, when each category already has one revenue value per customer.

PostgreSQL evaluates windows after grouping, so ranking sees category totals rather than item rows. See the table-expression documentation.

The order branch groups eligible_orders by customer for the count and latest timestamp. The item branch creates line_facts at one row per order item, calculates line revenue, then groups it into customer and category totals. A left join to products keeps valid item revenue when a product lookup is missing and labels the category Uncategorized.

ROW_NUMBER() directly guarantees one winner. RANK() ordered by revenue alone would preserve revenue ties. With revenue and category in ORDER BY, rows are peers only when every ordering expression ties, so the alphabetical category expression also breaks the tie.

Step 5: assemble the complete query from staged CTEs

Combine branches only after each is one row per customer, or at most one after filtering category rank to one. The joins then preserve final grain.

WITH params AS (
    SELECT
        date_trunc('month', CURRENT_DATE) - INTERVAL '1 month' AS start_at,
        date_trunc('month', CURRENT_DATE) AS end_at
),
eligible_orders AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.ordered_at
    FROM orders AS o
    CROSS JOIN params AS p
    WHERE o.status = 'completed'
      AND o.ordered_at >= p.start_at
      AND o.ordered_at < p.end_at
      AND o.customer_id IS NOT NULL
),
customer_order_metrics AS (
    SELECT
        customer_id,
        COUNT(*) AS completed_order_count,
        MAX(ordered_at) AS last_order_at
    FROM eligible_orders
    GROUP BY customer_id
),
line_facts AS (
    SELECT
        eo.customer_id,
        eo.order_id,
        oi.order_item_id,
        COALESCE(p.category, 'Uncategorized') AS category,
        oi.quantity * oi.unit_price AS line_revenue
    FROM eligible_orders AS eo
    JOIN order_items AS oi
      ON oi.order_id = eo.order_id
    LEFT JOIN products AS p
      ON p.product_id = oi.product_id
),
customer_revenue AS (
    SELECT
        customer_id,
        SUM(line_revenue) AS net_revenue
    FROM line_facts
    GROUP BY customer_id
),
category_revenue AS (
    SELECT
        customer_id,
        category,
        SUM(line_revenue) AS category_revenue
    FROM line_facts
    GROUP BY customer_id, category
),
ranked_categories AS (
    SELECT
        customer_id,
        category,
        category_revenue,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY category_revenue DESC NULLS LAST, category ASC
        ) AS category_rank
    FROM category_revenue
)
SELECT
    com.customer_id,
    com.completed_order_count,
    COALESCE(cr.net_revenue, 0) AS net_revenue,
    ROUND(
        COALESCE(cr.net_revenue, 0::numeric)
        / NULLIF(com.completed_order_count, 0)::numeric,
        2
    ) AS average_order_value,
    com.last_order_at,
    rc.category AS top_category,
    rc.category_revenue AS top_category_revenue
FROM customer_order_metrics AS com
LEFT JOIN customer_revenue AS cr
  ON cr.customer_id = com.customer_id
LEFT JOIN ranked_categories AS rc
  ON rc.customer_id = com.customer_id
 AND rc.category_rank = 1
ORDER BY com.customer_id;

Each CTE has one job and a stated grain.

Step 6: validate checkpoints instead of trusting the final table

A running query is not necessarily correct. Keep the CTEs and temporarily replace the final SELECT with focused probes.

Check the eligible-order grain

Compare COUNT(*) with COUNT(DISTINCT order_id) in eligible_orders. They should match; a difference means the base stage is duplicated. Record distinct customers as the expected final row count.

Preflight the same date and status window for null customer_id; stop and resolve any matches. The query also excludes them defensively because no customer can receive their metrics. Check eligible items for null quantity or unit_price and stop on those too.

Check the final grain

Wrap the final selection in a final_result CTE, group by customer_id, and apply HAVING COUNT(*) > 1. Any returned row breaks the one-row-per-customer promise.

Reconcile totals across stages

Build a control from eligible orders and items before products, then compare it with final_result. They must match.

SELECT
    COALESCE(SUM(oi.quantity::numeric * oi.unit_price), 0::numeric)
        AS control_revenue,
    (
        SELECT COALESCE(SUM(net_revenue), 0::numeric)
        FROM final_result
    ) AS final_revenue
FROM eligible_orders AS eo
JOIN order_items AS oi ON oi.order_id = eo.order_id;

Trace one customer end to end

Pick a customer with several orders and categories. Filter each CTE to that identifier and inspect their orders, lines, category totals, and final row. A narrow trace exposes incorrect assumptions faster than scanning the full report.

Decide edge cases explicitly

Edge cases are part of the output contract, not cleanup after the query is written.

Also flag completed orders without lines by left joining eligible_orders to items, grouping by order, and checking HAVING COUNT(order_item_id) = 0. The report handles the condition, but the source may still need investigation.

Explain the query under interview pressure

Explain decisions in build order instead of narrating every line:

1. Grain: one row per purchasing customer.
2. Eligibility: one half-open month window and one completed-order filter.
3. Cardinality: item joins change order grain to line grain, so order counts stay separate.
4. Measures: each branch aggregates to customer grain before joining.
5. Winner: category totals are ranked with a deterministic tie-break.
6. Checks: key uniqueness, final uniqueness, reconciled revenue, and one customer trace.

Tie each choice to the contract. ROW_NUMBER(), for example, follows from the requirement for exactly one top category.

A blank-editor checklist you can reuse

1. Write the final grain and the rule for every output.
2. Identify source grains, keys, and each join's cardinality.
3. Apply shared eligibility filters at the earliest correct grain.
4. Use aggregation to collapse rows and windows to add context.
5. Give each CTE one transformation and a stated grain.
6. Inspect counts and keys whenever grain changes.
7. Reconcile important measures to a simpler source total.
8. Resolve ties, nulls, missing matches, and time boundaries.

When stuck, return to the first unchecked item. Write the statement that proves a grain, builds one measure, or tests one assumption.

FAQ

Should I start by writing the final SELECT list?

Write the contract, then build the earliest reliable grain. Joining every source immediately hides where rows multiply.

When should I use COUNT(DISTINCT order_id)?

Use it for intentional distinct counting, not to conceal unexpected duplication. Counting from order grain is clearer here.

What if my totals do not reconcile?

Find the first stage where it changes. Check keys, cardinality, filters, null arithmetic, and unmatched rows.

Do I need window functions to solve complex SQL questions?

Only when output rows need context such as rank, a prior value, or a running measure. This report needs one window for the category winner.