4.1 Analytical Grain, Join Cardinality, and Metric Denominators: Prevent Double Counting in SQL

The first skill of analytical SQL isn’t window functions; it’s always knowing what one row in the result represents.

Learning Objectives

• Specify the result granularity for each CTE;
• Predict the number of rows after one-to-one, one-to-many, and many-to-many joins;
• Correctly use COUNT(*), COUNT(col), and conditional aggregation;
• Define the cohort, numerator, denominator, and time attribution for each rate.

1. Declare Granularity at the Start of a Query

Run this setup in a fresh DuckDB 1.5 connection, then run the lesson’s queries. The complete queries execute as written; standalone expressions are labeled as fragments. Equivalent syntax may require changes on other engines.

CREATE TEMP TABLE missions AS
SELECT * FROM (VALUES
 ('M1', 'survey', 'completed', TIMESTAMP '2026-08-01 10:00:00', 'S1', 10, 'U1'),
 ('M2', 'repair', 'failed', NULL, 'S1', 20, 'U1'),
 ('M3', 'survey', 'running', NULL, 'S2', 30, NULL)
) AS t(mission_id, mission_type, terminal_status, completed_at, system_id, cost, user_id);
CREATE TEMP TABLE mission_tags AS
SELECT * FROM (VALUES ('M1','hazardous'), ('M1','priority')) AS t(mission_id,tag);
CREATE TEMP TABLE mission_events AS
SELECT * FROM (VALUES ('M1','e1'), ('M1','e2'), ('M2','e3')) AS t(mission_id,event_id);
CREATE TEMP TABLE mission_costs AS
SELECT * FROM (VALUES ('M1',4), ('M1',6), ('M2',20)) AS t(mission_id,amount);
CREATE TEMP TABLE systems AS
SELECT * FROM (VALUES ('S1','north'), ('S2','south')) AS t(system_id,region);

-- Grain: one row per mission_id
SELECT
    mission_id,
    mission_type,
    terminal_status,
    completed_at
FROM missions;

Other common granularities:

one row per event_id
one row per mission_id per calendar day
one row per account per invoice
one row per experiment assignment

Aggregation and joins can change grain. A window function alone retains its input rows; a later filter may select a different result set. Annotate each CTE’s grain and check that its keys actually satisfy the promise.

2. Predict Cardinality Before Joining

Assumptions:

missions: one row per mission_id
mission_tags: multiple rows per mission_id

SELECT m.mission_id, m.cost, t.tag
FROM missions AS m
JOIN mission_tags AS t USING (mission_id);

The result granularity becomes "one row per mission and per tag." In this case, directly SUM(m.cost) will duplicate costs according to the number of tags.

Here M1 has two tags, so the inner join returns two rows and sums its cost to 20. M2 and M3 disappear because they have no tags. The preaggregated left join below preserves total cost 60 and hazardous cost 10; fan-out and dropped unmatched rows are separate problems.

If the goal is to determine whether a mission contains a hazardous tag, it's better to first aggregate down to the mission level:

WITH tag_flags AS (
    -- Grain: one row per mission_id
    SELECT
        mission_id,
        MAX(CASE WHEN tag = 'hazardous' THEN 1 ELSE 0 END) AS is_hazardous
    FROM mission_tags
    GROUP BY mission_id
)
SELECT
    SUM(m.cost) AS total_cost,
    SUM(CASE WHEN f.is_hazardous = 1 THEN m.cost ELSE 0 END) AS hazardous_cost
FROM missions AS m
LEFT JOIN tag_flags AS f USING (mission_id);

3. Use Uniqueness Checks to Validate Assumptions

SELECT mission_id, COUNT(*) AS rows_per_key
FROM missions
GROUP BY mission_id
HAVING COUNT(*) > 1;

A production model can declare uniqueness through table constraints or data validation tests. However, the query author must still verify that join keys are complete: querying a single dimension table based solely on customer_id while referencing a (customer_id, valid_from) versioned dimension may result in multiple version matches.

The duplicate-key query does not prove that keys are non-null: one null key will pass it. Check mission_id IS NULL separately. For a key with m left rows and n right rows, an equality join emits m×n matching pairs; neither a shared column name nor a distinct projection establishes entity identity.

SELECT DISTINCT cannot resolve unknown duplicate sources. It may mask incorrect joins or erroneously merge two events that happen to have identical numeric values but are otherwise valid and distinct.

4. Null Semantics of COUNT

SELECT COUNT(*) AS rows,
       COUNT(completed_at) AS completion_times,
       COUNT(DISTINCT user_id) AS known_users
FROM missions;

To calculate the number of completed tasks, if the completion time might be delayed or formatted incorrectly, the COUNT(completed_at) field and the status definition may not align. The appropriate field to use should be explicitly defined by the metric contract.

Portable syntax for conditional aggregation:

SELECT SUM(CASE WHEN terminal_status = 'completed' THEN 1 ELSE 0 END) AS completed
FROM missions;

Some dialects support COUNT(*) FILTER (WHERE ...), which is more concise but not available on all systems.

The sample returns counts 3, 1 and 1. Over an empty input without GROUP BY, COUNT returns 0 while SUM returns NULL. With GROUP BY, groups absent from the input produce no row at all. Use a complete group list when an output row for every system is required.

5. Compute the numerator and denominator first

WITH terminal_missions AS (
    -- Grain: one row per terminal mission
    SELECT mission_id, system_id, terminal_status
    FROM missions
    WHERE terminal_status IN ('completed', 'failed', 'cancelled')
)
SELECT
    system_id,
    SUM(CASE WHEN terminal_status = 'completed' THEN 1 ELSE 0 END) AS completed,
    COUNT(*) AS terminal,
    1.0 * SUM(CASE WHEN terminal_status = 'completed' THEN 1 ELSE 0 END)
        / NULLIF(COUNT(*), 0) AS completion_rate
FROM terminal_missions
GROUP BY system_id;

Keep completed and terminal intact, don’t just output rate. NULLIF prevents division-by-zero errors; returning NULL for undefined ratios is more accurate than defaulting to 0.

Whether to include cancelled in the denominator is a business decision, not a SQL technique.

This query reports terminal tasks in the supplied snapshot: S1 has 1/2 = 50%, while S2 has no terminal group and is absent. It is not a creation-cohort completion rate. A production metric must also specify the observation cutoff, timezone, treatment of late terminal events, and whether its time filter uses creation time or terminal time.

6. Aggregate First, Then Join, or Join First, Then Aggregate

The order matters. If two fact tables contain task events and cost details respectively, joining directly on mission will produce $m\times n$ rows.

WITH event_by_mission AS (
    SELECT mission_id, COUNT(*) AS event_count
    FROM mission_events
    GROUP BY mission_id
),
cost_by_mission AS (
    SELECT mission_id, SUM(amount) AS total_cost
    FROM mission_costs
    GROUP BY mission_id
)
SELECT *
FROM event_by_mission
JOIN cost_by_mission USING (mission_id);

Instead, aggregate both sides to a common granularity before joining, this avoids fan-out.

For M1, directly joining two events to two cost lines creates four rows and sums cost to 20. Preaggregation returns event_count=2 and total_cost=10. The inner join retains only missions present on both sides; start from missions with left joins if all missions must remain.

7. NULL in Joins Requires Explicit Handling

NULL = NULL evaluates to unknown, not true or false, so an equality join does not match two null keys. IS NOT DISTINCT FROM can provide null-safe equality, but unknown customers should not all become one customer. The PostgreSQL comparison documentation describes this three-valued logic.

Filtering a right-side column in WHERE after a LEFT JOIN:

SELECT m.mission_id, f.region
FROM missions AS m
LEFT JOIN systems AS f ON m.system_id = f.system_id
WHERE f.region = 'north';

This removes unmatched rows, producing an effect similar to an inner join. If the goal is to retain all tasks and join only with the North region dimension, the condition should be placed in ON, or NULL values should be explicitly handled.

For this predicate, the result has inner-join semantics. Moving f.region = 'north' into ON preserves M3 with a null dimension. Adding OR f.region IS NULL in WHERE is not generally equivalent: M3 already matched the south row and would still be removed.

8. Logical Execution Order Helps Understand Queries

Conceptual order:

FROM / JOIN
→ WHERE
→ GROUP BY
→ HAVING
→ window functions
→ SELECT
→ ORDER BY
→ LIMIT

Optimizers may reorder physical execution steps, but must preserve semantic correctness. Understanding the logical execution order explains why most query dialects cannot reference window function results within the same layer WHERE, and why filtering conditions appear before or after aggregation in different contexts.

Common Misconceptions

• Keys with the same name are safe to join: You must also verify uniqueness and time versioning.
• DISTINCT can fix duplicates: It often masks grain errors.
• COUNT(col) equals row count: NULL values are not included.
• Success rate only requires one division: The numerator and denominator are determined by cohort, state, and time scope.

Exercise

1. Define granularity for three tables (tasks, tags, and costs) and predict the number of direct rows that would result from a join between them.
2. Construct a query involving LEFT JOIN + WHERE right.col that accidentally loses rows and then fix it.
3. Compare COUNT(*), COUNT(status), and COUNT(DISTINCT mission_id).
4. Provide at least five definitions of scope for a completion rate query.

Summary

Evaluating SQL correctness starts with granularity. Joins alter row identity, aggregations change denominators, and NULL values affect matching and counting. Only by clearly documenting these transformations can window functions be said to operate on accurate facts.

The next lesson focuses specifically on windows: whether sorting is unique, which rows are included in a frame, and why "the previous row" does not necessarily mean "the previous day."