SQL for Analytics — The Queries Analysts Write Daily

Aggregations, joins, CASE, date handling, window functions and CTEs applied to real business questions, with links to the full SQL course.

Est. 40 min Updated Suggest a fix
// Part 01 — Ask a business question

Translate the Question into Rows and Measures

SQL lets an analyst ask a database for a defined slice of its data. Suppose a product team wants weekly completed orders and the share delivered late by city. Before writing a query, identify the row grain in each table, the population, the time window, the measures, and how the output will be grouped. This keeps a syntactically valid query from quietly answering a different question.

In a typical schema, an orders table might have one row per order and a customers table one row per customer. A one-to-many relationship, such as one order with several line items, can multiply rows after a join. Learn the keys and table grain before adding any join. Keep business definitions close to the query so a reviewer can see what counts as an order or a late delivery.

The query's grain should be clear before selecting columns.
SQL conceptAnalyst questionCheck before trusting
WHEREWhich records are in scope?Population, dates and null handling
GROUP BYHow should results be segmented?Output grain and group sizes
JOINWhich attributes belong together?Key uniqueness and row multiplication
Window functionHow does each row compare with peers?Partition, ordering and tie rules
💡 Note
Start by writing the expected output in plain language: "one row per city and week, with completed order count and late-order rate." That sentence becomes your grain check.
// Part 02 — Filter and group

Aggregate Carefully and Name Every Measure

WHERE filters rows before grouping; GROUP BY forms groups; aggregate functions such as COUNT, SUM, and AVG calculate values for each group. COUNT(*)counts rows, while COUNT(column) ignores nulls in that column. COUNT(DISTINCT customer_id)counts unique customers. These answer different questions, so choose based on the unit you mean to count.

orders_by_city.sql
sql
SELECT
    city,
    COUNT(*) AS completed_order_rows,
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(order_value) AS gross_order_value,
    AVG(delivery_minutes) AS average_delivery_minutes
FROM orders
WHERE status = 'completed'
  AND order_date >= DATE '2026-01-01'
  AND order_date < DATE '2026-02-01'
GROUP BY city
ORDER BY gross_order_value DESC;

The date literal syntax and date functions vary across database engines. The half-open interval includes the start and excludes the next boundary, so it works cleanly when a timestamp contains a time of day. If your field stores a local business date, document its timezone and calendar. Never use a display-formatted date assumption without checking the actual column type.

🎯 Pro Tip
Give output fields business-readable aliases with units where useful. A column called avg_delay_min is safer to reuse than an unqualified average.
// Part 03 — Conditional metrics

Use CASE to Make Definitions Explicit

CASE maps row-level conditions to labels or values. It is useful for categorising delays, mapping event types into a funnel, or calculating a numerator under the same filter as its denominator. A conditional aggregate can retain groups with zero qualifying events, unlike filtering those rows away before the group is formed.

late_rate_by_city.sql
sql
SELECT
    city,
    COUNT(*) AS completed_orders,
    SUM(CASE WHEN delivered_at > promised_at THEN 1 ELSE 0 END) AS late_orders,
    1.0 * SUM(CASE WHEN delivered_at > promised_at THEN 1 ELSE 0 END)
        / NULLIF(COUNT(*), 0) AS late_order_rate
FROM orders
WHERE status = 'completed'
  AND order_date >= DATE '2026-01-01'
  AND order_date < DATE '2026-02-01'
GROUP BY city
HAVING COUNT(*) >= 20
ORDER BY late_order_rate DESC;

NULLIF prevents division by zero. The multiplication by a decimal is a common way to avoid integer division, but exact casting syntax differs by engine. The HAVING condition filters groups after aggregation; it does not change the row-level population. Here the minimum-volume rule is part of the stated comparison, not a way to hide inconvenient cities.

Clause order is a logical explanation, not necessarily the database execution plan.
ClauseWhen it appliesExample use
WHEREBefore groups are builtKeep completed orders in the date window
GROUP BYForms output groupsOne result per city
HAVINGAfter aggregates existRestrict to groups with enough records
ORDER BYAfter result rows are formedSort by late rate or value
// Part 04 — Join tables

Join at the Right Grain and Reconcile the Row Count

A join combines rows using a relationship. An INNER JOIN keeps matching keys; a LEFT JOINkeeps all rows from its left side and adds matching values where available. If an order has several items, joining orders to items creates one row per item, not one row per order. Summing order-level revenue after that join can overcount. Aggregate line items to the order grain first, or use a measure whose grain matches the joined rows.

orders_with_customer.sql
sql
SELECT
    o.order_id,
    o.order_date,
    o.city,
    o.order_value,
    c.customer_segment
FROM orders AS o
LEFT JOIN customers AS c
    ON o.customer_id = c.customer_id
WHERE o.status = 'completed';

Check that the right-side key is unique if you expect one customer record per customer ID. Compare row counts before and after the join, inspect null match rates, and sample keys with multiple matches. If a dimension is versioned over time, a simple ID join may attach the wrong historical segment; use the effective dates and intended as-of-date rule defined by the data model.

  1. Write down the expected grain of each input and the output.
  2. Check key uniqueness on the side expected to be one-to-one or many-to-one.
  3. Run the join for a small sample and inspect duplicated IDs and unmatched keys.
  4. Reconcile totals and distinct entity counts against a pre-join baseline.
// Part 05 — Dates and cohorts

Use Calendar Boundaries Instead of Guessing at Dates

Date analysis depends on the business calendar, timezone, and timestamp semantics. Filtering a timestamp with an inclusive end-of-day value can omit later fractional seconds; a half-open range avoids that boundary problem. To group by week or month, use the function supported by your database and state whether weeks start on Monday, Sunday, or another business-defined day. Do not silently mix UTC timestamps and local reporting dates.

daily_orders.sql
sql
-- Date truncation syntax differs by SQL engine; adapt to your database.
SELECT
    CAST(order_date AS DATE) AS order_day,
    COUNT(*) AS completed_orders,
    SUM(order_value) AS gross_order_value
FROM orders
WHERE status = 'completed'
  AND order_date >= DATE '2026-01-01'
  AND order_date < DATE '2026-02-01'
GROUP BY CAST(order_date AS DATE)
ORDER BY order_day;
⚠️ Important
A missing date may mean there were no events, that ingestion failed, or that the source was not connected. Do not automatically fill gaps with zero until the data contract confirms what an absent row means.
// Part 06 — Window functions

Compare Rows Without Collapsing Them

Aggregations reduce each group to one row. A window function calculates over related rows while preserving each input row. Use PARTITION BY to define groups and ORDER BY to define sequence. Functions like ROW_NUMBER can select the latest event per customer; LAG compares a row with a previous row; an aggregate with a window can calculate a running total. Specify tie-breaking columns when ordering is not unique.

latest_order_per_customer.sql
sql
WITH ranked_orders AS (
    SELECT
        customer_id,
        order_id,
        order_date,
        order_value,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC, order_id DESC
        ) AS row_num
    FROM orders
    WHERE status = 'completed'
)
SELECT customer_id, order_id, order_date, order_value
FROM ranked_orders
WHERE row_num = 1;

Here the order ID breaks equal-date ties for a deterministic result; the correct tie-break should reflect the source's semantics. A CTE gives the ranking step a name and separates it from the final filter. CTEs improve readability but are not automatically faster; inspect the query plan and database documentation when performance matters.

📌 Real World Example
A running seven-day metric needs an explicit frame and a clear definition of whether seven rows or seven calendar days are intended. Missing dates make those meanings differ.
// Part 07 — Build and validate

Turn Queries into an Auditable Analysis

Write a query in small steps: inspect the base table, apply row filters, join validated dimensions, derive fields, aggregate, then sort. Use CTEs for meaningful stages instead of nesting a long expression that nobody can review. Keep assumptions near the query, parameterise dates in scheduled work, and avoid selecting every column when you need only a few. A smaller output is easier to inspect and may reduce unnecessary data movement.

Validate outputs with invariants: rates should be within their expected range; counts should not exceed the relevant population; category totals should reconcile; and a known small sample should produce a manually checked result. Query limits are useful for exploration, but a limited result is not a representative sample unless the selection method is appropriate. Follow your organisation's rules for access, retention, and personal data.

  • Confirm the output grain and the meaning of every measure.
  • Check nulls, duplicate keys, unmatched joins, and row-count changes.
  • Compare a small sample with a source-system or independently calculated total.
  • Record date boundaries, timezone, filters, definitions, and SQL dialect.
  • Use read-only access for analysis and follow data governance requirements.
🎯 Pro Tip
This lesson focuses on the analyst patterns used daily. For a complete SQL path covering fundamentals, joins, database behavior, and advanced query design, continue with the full SQL course.

What comes next

Load, clean, reshape, group and visualise data with pandas in a notebook, and turn the result into a shareable report.

Lesson 03 · Python for Analysts — The pandas Analysis Workflow →
// Part 08 — Pitfalls

Common SQL Analysis Mistakes

Counting rows and calling them customers or orders

Why: A join or line-item table can repeat the same entity across several rows.

Fix: Know each table’s grain and use a distinct entity key or an appropriately aggregated source.

Filtering away zero-activity groups before calculating a rate

Why: The denominator population or set of eligible groups may shrink without being noticed.

Fix: Keep the required base population and use conditional aggregation for the numerator.

Using a date boundary that excludes part of the final day

Why: Timestamp values include hours and fractional seconds.

Fix: Use a documented half-open time interval and confirm the reporting timezone.

Assuming a join cannot multiply rows

Why: The join key may match multiple records on the other side.

Fix: Check uniqueness, row counts, null matches, and measure grain before trusting aggregates.

// Part 09 — Practice

Answer a Weekly Operations Question

Practice

Build a query that a teammate can verify

  1. Write the output grain and define completed orders, late delivery, and the reporting date window.
  2. Query one row per city with completed order count, late-order count, late rate, and average delivery minutes.
  3. Join a city-to-region table and report unmatched cities without dropping the orders.
  4. Rank cities by late rate using a window function, while displaying the order count beside each rank.
  5. Use a CTE to select the latest completed order for each customer and state how ties are resolved.
  6. Validate the results with row counts, distinct order IDs, a small manual sample, and one independent total.

Hint: Start with the simplest query for one city and one week. Add joins and windows only after the base metric reconciles. Adapt date and casting syntax to your database.

🎯 Key Takeaways

  • ✓Translate a business question into population, period, measure, segment, and output grain before writing SQL.
  • ✓COUNT rows, non-null values, and distinct entities are different calculations.
  • ✓Use CASE with conditional aggregates for explicit numerators and denominators; use HAVING for group-level filters.
  • ✓Check join keys and grain because one-to-many joins can multiply rows and inflate measures.
  • ✓Handle timestamps with documented half-open boundaries and a clear timezone and calendar.
  • ✓Window functions preserve row detail; CTEs make analytical stages easier to read and test.
  • ✓Reconcile totals, counts, nulls and sample rows before communicating a result.

Next

What comes next

SQL retrieves and aggregates records. Next, use pandas for a repeatable notebook workflow that cleans and reshapes analysis data.

Lesson 03 · Python for Analysts — The pandas Analysis Workflow →

HELP US IMPROVE

How was this lesson?

Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.

Choose your feedback

Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.

Share

Discussion

0

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub
Loading...