Foundations · Module 05

Advanced SQL for Analytics.

Move from querying one table to building trustworthy multi-table analysis with explicit grain, safe joins, CTE pipelines, window functions and control queries.

Core idea

Advanced SQL is not about writing longer queries. It is about controlling grain and meaning while the query becomes more complex.

Scenario

Find what actually drove a cancellation spike.

CompanyQuickCart QuestionWhich segments drove the cancellation spike? Required grainWeek × city × customer type Sourcesorders + customers + status events

Operations wants the driver, not just the headline. The danger is that status-event history has multiple rows per order, while revenue and the cancellation denominator live at order grain.

Query plan

Make every grain change visible.

Define grain

State what one row must represent before any join or aggregation.

Build a base

Create the smallest trustworthy dataset that answers the core question.

Join deliberately

Bring in context only after checking relationship and cardinality.

Transform in layers

Use CTEs so every grain change and business rule is inspectable.

Window & compare

Rank, deduplicate and compare rows without collapsing useful detail.

Validate

Reconcile counts, totals and edge cases to simpler control queries.

Rule

If you cannot state the grain of each CTE in plain English, the query is not ready to trust.

Join cardinality

A valid JOIN can still destroy the metric.

Danger: order value repeats for every payment attempt

SELECT
  o.order_id,
  o.order_value,
  p.attempt_id,
  p.status
FROM orders o
LEFT JOIN payment_attempts p
  ON o.order_id = p.order_id;

If one order has three payment attempts, that order now appears three times. A later SUM(order_value) silently inflates GMV.

Before joining, compare COUNT(*) with COUNT(DISTINCT order_id) on both sides and decide whether the many-side table should be pre-aggregated.

CTE pipeline

Build one trustworthy layer at a time.

Create one row per order before segmenting

WITH final_status AS (
  SELECT order_id, status
  FROM (
    SELECT
      order_id,
      status,
      ROW_NUMBER() OVER (
        PARTITION BY order_id
        ORDER BY event_time DESC
      ) AS rn
    FROM order_status_events
  ) ranked
  WHERE rn = 1
),

order_base AS (
  SELECT
    o.order_id,
    o.customer_id,
    o.city_id,
    o.created_at,
    o.order_value,
    s.status
  FROM orders o
  LEFT JOIN final_status s USING (order_id)
)

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

Read the layers

final_status becomes one row per order. order_base stays one row per order. Only the final SELECT moves to week × city grain.

Window functions

Compare rows without collapsing them.

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 order_base
  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;

LAG answers a real business question: which cities deteriorated most compared with their own previous week? The window is useful because it preserves each city-week row while adding the comparison.

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.

Deduplication

Keep the right row—not just any row.

Latest customer profile record

WITH ranked_profiles AS (
  SELECT
    customer_id,
    city,
    customer_tier,
    updated_at,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM customer_profiles
)
SELECT *
FROM ranked_profiles
WHERE rn = 1;

SELECT DISTINCT removes identical rows. It does not decide which of several valid records is the correct one. For that, define the rule explicitly and rank the records.

Analyst challenge

Review the AI-generated query.

Exercise · 15 minutes

Find the analytical bugs.

The AI joins orders directly to status events. What happens to order counts and GMV?
It uses event_time to define the reporting week instead of order created_at. Is that the requested population?
It calculates cancelled events / all events. What is wrong with that denominator?
It uses DISTINCT to “fix duplicates.” What business rule has been hidden?
It ranks cities by raw cancellation count. Would rate or contribution-to-change explain the spike better?

Reveal the review approach

01Check grain

Track row count and distinct business keys before and after every join.

02Check definitions

Timestamps, final status and customer type must match the agreed metric contract.

03Check dedupe

State which record wins and why instead of masking duplicates with DISTINCT.

04Check usefulness

Rank by the quantity that explains the business change, not the easiest column to sort.

Validation

Do not trust a query because it runs.

What is the grain before and after every join?

Can a one-to-many relationship duplicate a measure?

Is the query using the correct business timestamp?

Are CTEs changing grain intentionally and visibly?

Do window partitions and ordering match the business question?

Are nulls, cancellations, refunds and late-arriving events handled intentionally?

Can the final result reconcile 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 grain changes, deliberate joins, named business rules and simple reconciliation queries. Complexity is acceptable; invisible complexity is not.

← Previous · SQL Foundations Curriculum Next · Applied SQL Analysis →