Foundations · Module 03

SQL for Analytics.

SQL is not the job. SQL is the language you use to turn a business question into evidence you can inspect, validate and explain.

Core idea

Good SQL starts before SELECT. It starts with grain, definitions and a clear plan for what the output must mean.

Context

Find where cancellations actually increased.

CompanyQuickCart QuestionWhich segments drove the cancellation spike? Required grainWeek × city × customer type OutputOrders, cancellations, cancellation rate

Operations wants to know whether the spike is concentrated in specific cities and whether new customers are affected more than returning customers.

Query plan

Break the analysis into transformations.

Filter

Reduce the data to the business scope you actually care about.

Aggregate

Turn raw rows into business measures at the correct grain.

Join

Bring in dimensions or summaries without changing the intended grain.

Window

Compare rows across time, rank segments and calculate running logic.

Validate

Check counts, totals and edge cases before trusting the result.

Rule

Write the query plan in plain English first. If the steps are unclear in English, adding SQL syntax will not fix the logic.

Foundation query

Start from a result you can explain.

Completed and cancelled orders by week

SELECT
  DATE_TRUNC('week', created_at) AS week,
  COUNT(*) AS placed_orders,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders,
  1.0 * COUNT(*) FILTER (WHERE status = 'cancelled')
      / NULLIF(COUNT(*), 0) AS cancellation_rate
FROM orders
WHERE created_at >= DATE '2026-08-31'
  AND created_at <  DATE '2026-09-14'
GROUP BY 1
ORDER BY 1;

This query is intentionally simple. Before adding cities, customers or event histories, make sure the basic weekly cancellation rate matches the trusted top-line metric.

Complex analysis should usually have a simpler control query that you can reconcile against.

CTEs & joins

Make grain changes explicit.

Build one row per order before segmenting

WITH customer_first_order AS (
  SELECT
    customer_id,
    MIN(created_at) AS first_order_at
  FROM orders
  GROUP BY 1
),

order_base AS (
  SELECT
    o.order_id,
    o.customer_id,
    o.city_id,
    o.created_at,
    o.status,
    CASE
      WHEN o.created_at = f.first_order_at THEN 'new'
      ELSE 'returning'
    END AS customer_type
  FROM orders o
  LEFT JOIN customer_first_order f
    ON o.customer_id = f.customer_id
)

SELECT
  DATE_TRUNC('week', created_at) AS week,
  city_id,
  customer_type,
  COUNT(*) AS placed_orders,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders
FROM order_base
GROUP BY 1, 2, 3;

Review it

This query still has a subtle business-definition question: does “new customer” mean the customer's first-ever order attempt, or first completed order? SQL cannot decide that for you.

Window functions

Compare segments without losing detail.

Week-over-week cancellation-rate change

WITH weekly AS (
  SELECT
    DATE_TRUNC('week', created_at) AS week,
    city_id,
    COUNT(*) AS orders,
    1.0 * COUNT(*) FILTER (WHERE status = 'cancelled')
      / NULLIF(COUNT(*), 0) AS cancellation_rate
  FROM orders
  GROUP BY 1, 2
)

SELECT
  week,
  city_id,
  orders,
  cancellation_rate,
  cancellation_rate
    - LAG(cancellation_rate) OVER (
        PARTITION BY city_id
        ORDER BY week
      ) AS wow_change
FROM weekly
ORDER BY wow_change DESC NULLS LAST;

The window function is not “advanced SQL” for its own sake. It answers a business question: which cities deteriorated most compared with their own previous week?

LIVE

Join explosion · interactive visual

A valid JOIN can inflate a valid metric.

orders3 orders

1001$25

1002$40

1003$45

GMV$110

LEFT JOIN

paymentsAggregated to orderPayment attempts

1001 · failed$25

1001 · paid$252 rows for order 1001

1002 · paid$40

1003 · paid$45

GMV after join $110 $135

✓ many-to-one join × one-to-many explosion

Summarize payment attempts to one row per order before joining.

Row count rises from 3 to 4 while distinct orders stay 3. That is your warning.

Challenge

Review the AI-generated query.

Exercise · 15 minutes

Find the analytical bugs.

The AI joins orders directly to order_status_events. What happens to order counts?
It uses event_time to define the reporting week. Is that necessarily the right business timestamp?
It defines cancellation rate as cancelled events / all events. What is wrong with the denominator?
It labels customers “new” when signup_date is in the same week. Is that the same as first-order customer?
It ranks cities by number of cancellations only. Why might rate or contribution-to-change be more useful?

Reveal the review approach

01Check grain

One row per order should remain one row per order until intentional aggregation.

02Check definitions

Timestamps, customer type and cancellation logic must match the agreed metric contract.

03Check denominator

Cancellation rate is typically cancelled orders divided by eligible/placed orders, not event rows.

04Check usefulness

Rank by the quantity that best explains the business change, not whatever is easiest to calculate.

AI assist

Ask for SQL plus a validation plan.

AI SQL request

Business question:
Which cities and customer types contributed most to the week-over-week increase in cancellation rate?

Required output grain:
week × city × customer_type

Metric definitions:
- placed_orders = distinct order_id created in the reporting week
- cancelled_orders = distinct order_id whose final status is cancelled
- cancellation_rate = cancelled_orders / placed_orders
- customer_type = new if this is the customer's first-ever placed order, otherwise returning

Before writing SQL:
1. Restate the grain and definitions.
2. Explain how you will avoid duplicate orders from status-event joins.
3. Write the SQL.
4. Provide 3 control queries I can use to validate the result.
5. Call out any business definitions that are still ambiguous.

Validation

Do not trust a query because it runs.

Does the query answer the business question, not just produce a table?

Is the filter using the correct business timestamp?

Is the aggregation at the intended grain?

Can any join duplicate orders or customers?

Are nulls, cancellations and refunds handled intentionally?

Does a window function use the correct partition and order?

Can I reconcile the result to a simpler control query?

PRACTICE THIS NOW ↓

Interactive case · L02

The Join Explosion

Run a payment-attempt join that quietly multiplies order value, then diagnose it with control queries.

Red = inject failureGreen = run validationRUN THIS CASEOpen practice lab →

Takeaway

Readable, testable SQL beats clever SQL.

Analyst habit

Build the answer in layers you can validate: a trusted base, explicit transformations, deliberate joins and simple reconciliation queries. AI can write syntax quickly; you are responsible for whether the query means what the business thinks it means.

← Previous · Data Foundations Curriculum Next · Business Metrics →