SQL Fundamentals for Data Engineers

Data Engineering & Modern Data Platforms · Lesson 06 · Data Engineering Foundations

Lesson Overview

The Python worker from lesson 5 had accepted a clean batch. The Monday completion rate was still wrong, and this time the parser tests were green. An analyst had joined progress events to enrollments, filtered to event_type = 'completed', and counted rows. Learners with two enrollment records—one refunded, one active—were counted twice. Learners who completed a lesson and whose enrollment row had a null course_id during a bad load disappeared, because the join was an inner join. A second query used WHERE status <> 'refunded', which in SQL does not mean "everyone who is not refunded" when status is null. The warehouse was doing exactly what it was asked. The ask multiplied the grain.

SQL is the language of the curated mart in this course. It is set-oriented: a query describes the set you want, and the engine chooses a plan. That power is how a join becomes a fanout without a loop you can see. This lesson builds the queries a data engineer must be able to read and reject: filters, joins, aggregates, subqueries, and common table expressions, aimed at enrollment and lesson completion. It is not a reprint of /learn/sql or /learn/postgresql. Those primers own the language and the engine. Here every statement is judged by whether the grain from lesson 1 survives it.

Window functions, which are the right tool for "previous attempt" and "rank inside a partition," are lesson 7. Storage internals, indexes, and query plans beyond a first glance are lesson 8. This lesson uses PostgreSQL 16 syntax because that is the teaching engine from lesson 2. The statements stick to core SQL where they can, and they name PostgreSQL when they use timestamptz, ON CONFLICT, or the FILTER clause. They do not use a cloud-warehouse dialect. If a feature is not in the PostgreSQL 16 manual, it is not in this lesson.

Planning assumptions, still labeled: a completion is one row per (learner_id, lesson_id, attempt_number) under definition completion-v3. The official count for the Monday review is distinct learners who completed lesson les_07 on attempt 1 and whose enrollment for the course was active at the event time. Null is a value you must decide about, not a value the predicate will quietly classify for you.

Learning Objectives

By the end of this lesson you will be able to:

1. State the grain of a query result and compare it to the grain of each input table.
2. Choose INNER, LEFT, and anti-joins on purpose, and explain who disappears with each choice.
3. Explain why WHERE status <> 'refunded' drops null statuses, and write the predicate you actually mean.
4. Aggregate at the grain you named, and use HAVING only for conditions on aggregates.
5. Use a subquery or a CTE to force "events aggregated before enrollment is joined" so the join cannot multiply facts.
6. Insert a restatement as a replacement in one transaction, not as an append that doubles Tuesday.
7. Read a trivial EXPLAIN output well enough to see a nested loop over an unexpected row count, and know when to stop and open lesson 8.
8. Review a metric query and reject SELECT DISTINCT used as a substitute for a key.

Prerequisites

• Lessons 1 and 2: grain, event time, publication, and the logical tables raw_progress_event, lesson_completion, and mart_publication.
• Lesson 4's rule that attempt numbers are integers and timestamps are timezone-aware. SQL will assume the loader already enforced that. It should still constrain the table.
• You can read a SELECT with a WHERE clause. Join types and GROUP BY are taught here with warehouse consequences. For a slower pass over syntax alone, use /learn/sql, then return.
• A local or disposable PostgreSQL 16 is ideal and not mandatory. The statements are short enough to reason about from the sample rows printed in the lesson. Do not run them against the operational primary.

Detailed Core Concepts

Tables, rows, and grain, again

A table is a set of rows of a declared type, plus the constraints you bothered to add. Without a primary key, PostgreSQL will store two identical completion rows and every sum will believe both. The mart's primary key is the grain. The raw event's primary key is event_id. Enrollment's primary key in the operational system may be an enrollment id, with a uniqueness rule of one active enrollment per learner and course. If that uniqueness is not in the analytical copy, the analytical copy will fan out. Copy the constraint or aggregate before you join. Do not assume the extract "usually" has one row.

NULL means unknown, not zero, not false, and not empty string. It is how a missing course_id or a missing status arrives if the loader was sloppy. SQL uses three-valued logic. A comparison with NULL yields unknown, and WHERE keeps only rows where the predicate is true. Unknown is discarded. That single rule explains more broken metrics than any join type.

Projection and filter

SELECT chooses columns. WHERE chooses rows before aggregation. Both are predicates over one row. They cannot, by themselves, repair a duplicate event_id that the loader accepted. If you are writing WHERE event_id IS NOT NULL in the mart query, the contract failed earlier and the query is hiding it. Fix the load. A filter that belongs in the metric—"attempt 1 only," "event day in the review window"—stays in the query and in the spec, in the same words.

SELECT DISTINCT is a projection that removes duplicate rows of the selected columns. It is occasionally the honest way to ask "which learners appear at all." It is a defect when it is applied to hide a join that multiplied rows. The count changes if someone adds a column to the select list. Prefer a unique constraint and a query that does not multiply. If you use DISTINCT, say in a comment what duplicated and why the select list is the grain.

Joins

A join builds a row for each match of the predicate. It does not "attach" a dimension in the way a spreadsheet lookup attaches the first match. If five enrollment rows match one completion, you get five rows.

The anti-join, "completions with no enrollment," is a LEFT JOIN plus WHERE enrollment.learner_id IS NULL, or a NOT EXISTS subquery. Use it as a quarantine report. Do not subtract it from a rate in your head.

Join predicates should be keys you understand: learner and course, or learner and lesson when the lesson table says which course it belongs to. Joining only on learner_id attaches every course enrollment to every lesson completion. That query is the fanout. It will look plausible because the numbers got bigger, and bigger numbers during enrollment week feel like success.

Aggregation

GROUP BY collapses many rows into one per group. The select list may contain grouped columns and aggregates (count, sum, min, max). PostgreSQL will reject a bare column that is neither grouped nor aggregated. Some other engines pick an arbitrary value. Do not learn the arbitrary behavior. If you need one course title, group by it or aggregate it.

count(*) counts rows. count(course_id) counts rows where course_id is not null. They differ, and the difference is the learners your left join did not match. count(distinct learner_id) counts learners in the group. It is the Monday grain. count(*) on a joined table is the attempt-row grain, or the fanout grain, depending on what you joined.

WHERE filters input rows. HAVING filters groups after aggregation. WHERE count(*) > 1 is a syntax error. HAVING count(*) > 1 is the duplicate hunt. Filtering event_type in HAVING without an aggregate is a sign the predicate belongs in WHERE.

FILTER is a PostgreSQL aggregate clause: count(*) FILTER (WHERE event_type = 'completed'). It is documented in PostgreSQL 16 and is clearer than a CASE inside sum when you need several counts in one pass. It is not universal SQL. If you use it, know that you are in PostgreSQL. A CASE expression inside count is the portable form. Either is fine in this course's teaching database. Mixing them without noticing is how a port to another engine changes the rate.

Subqueries and CTEs

A subquery is a query inside a query. In WHERE learner_id IN (SELECT ...), the inner query builds a set. In the FROM clause, a derived table is a named result you join as if it were a table. Derived tables are how you aggregate events down to the completion grain before you join enrollment, so enrollment cannot multiply events.

A common table expression, WITH name AS (SELECT ...), is a named subquery you can read from the top. It does not, by itself, materialize a table, and it does not make the query faster. PostgreSQL may inline it. Use a CTE because the grain of each step should be visible to a reviewer, not because you heard CTEs are optimized. WITH also allows INSERT ... SELECT pipelines in one statement, which is useful and still one statement: if it fails, it fails as a unit only if you are inside a transaction you control. A single statement is already atomic in PostgreSQL. Several statements need an explicit transaction. Lesson 2's restatement rule—delete the window and insert the replacement together—means one transaction, which may contain more than one statement.

EXISTS and NOT EXISTS are subqueries that ask whether a row is present. They do not multiply the outer row when many inner rows match. That is why they are safer than a join when the question is "has at least one active enrollment" and the enrollment table is not unique. IN (SELECT ...) has a famous null trap: if the subquery produces a null, NOT IN becomes unknown for every outer row and the result is empty. Prefer NOT EXISTS for anti-joins. The trap is in the PostgreSQL manual's discussion of subqueries and nulls, and it has emptied more audit reports than any downed cluster.

Time in the filter

Store timestamptz. Compare instants, not strings. A calendar day for the metric is a conversion to a zone the spec names. This course uses UTC for the official day: event_time AT TIME ZONE 'UTC' produces a timestamp without time zone representing the UTC clock, and casting that to date is the event day. Do not use event_time::date and hope the session TimeZone is UTC. Session time zone is a connection setting. An analyst's laptop in a different zone will move events across midnight. Set the conversion in the query, or set the database default and still write the zone in the metric SQL so the spec does not depend on a setting.

The restatement window is a predicate on event day, not on ingestion_time. Filtering the load on ingestion time and labeling the result as Tuesday's completions is the clock bug from lesson 1, now in SQL.

Transactions and replacement

sqlCopy
BEGIN;
DELETE FROM lesson_completion
WHERE event_time >= timestamptz '2026-03-02 00:00:00+00'
  AND event_time <  timestamptz '2026-03-05 00:00:00+00'
  AND definition_version = 'completion-v3';
INSERT INTO lesson_completion (...)
SELECT ...;
INSERT INTO mart_publication (definition_version, event_day, source_batch_id)
VALUES ('completion-v3', DATE '2026-03-02', 'b1');
COMMIT;

If you COMMIT after the delete and the insert fails, the days are gone. One transaction. Readers in the default READ COMMITTED isolation see the delete and the insert only after you commit, not a mix from this transaction's uncommitted work. They can still see a mix if some other session commits a different write in between statements they themselves run. The publication row remains the contract for official reads: the studio selects facts that belong to a published day. Do not serve a query that scans lesson_completion with no publication predicate and call it the Monday number.

ON CONFLICT is how an insert of a raw event becomes idempotent at event_id. It is PostgreSQL syntax, documented, and it matches the duplicate-delivery rule: do nothing when the id exists, and do not update a different payload over a good one. An ON CONFLICT DO UPDATE that overwrites is latest-wins, which lesson 1 rejected. If the new payload differs, the load should fail or quarantine, which means you cannot express the whole rule as a blind upsert. Insert new ids. Let a separate check find conflicts. Blind upserts are how the spreadsheet backfill from lesson 4 replaces a real event.

What the planner is allowed to do

The engine may reorder joins and choose indexes. It must return the set your SQL describes, not the set you pictured. EXPLAIN shows the plan without running it. EXPLAIN ANALYZE runs it. Use EXPLAIN ANALYZE on a disposable database or a replica you are allowed to load, never as a casual experiment on the primary. This lesson only asks you to notice absurdity: a plan that expects millions of rows at a step you thought was one enrollment per learner means the uniqueness you assumed is not there. Lesson 8 reads plans properly. A reviewer who cannot yet read a plan can still reject a query whose join key is only learner_id.

Architecture and Design Diagrams

The metric is a data flow of sets. Each box has a grain. If you cannot write the grain under the box, the query is not reviewable.

mermaidCopy
flowchart LR
    raw[(raw_progress_event grain: event_id)]
    per_attempt[Aggregate to attempt grain]
    enroll[(enrollment grain: one active row per learner and course)]
    lesson[(lesson grain: lesson_id)]
    fact[Completion fact grain: learner, lesson, attempt]
    rate[Monday set grain: one row per learner for les_07 attempt 1]
    raw --> per_attempt --> fact
    lesson --> fact
    enroll --> rate
    fact --> rate

The arrow from enrollment joins at the rate, after the attempt grain exists, and only for the course the lesson belongs to. Joining enrollment to raw events is the fanout path. It is not on this diagram, and a query that does it anyway is a different architecture than the one you reviewed.

A restatement's control flow:

mermaidCopy
flowchart TD
    start[Begin transaction]
    del[Delete completion rows in the event-time window and version]
    ins[Insert facts computed at attempt grain]
    pub[Insert publication rows for those event days]
    commit[Commit]
    fail[Rollback: previous publication remains]
    start --> del --> ins --> pub --> commit
    del -.->|any error| fail
    ins -.-> fail
    pub -.-> fail

Official reads are a separate small flow. They do not appear inside the transaction as a consumer the job waits for. They follow publication.

mermaidCopy
flowchart LR
    client[Instructor studio or Monday query]
    pub[(mart_publication)]
    fact[(lesson_completion)]
    client --> pub
    pub -->|latest published event_day and version| fact

If the client arrow goes straight to lesson_completion, the diagram in lesson 2 has been bypassed in SQL. The bypass often looks like a view created "for convenience" that forgets the publication table. Treat that view as a defect.

Technical Explanation

Sample population

The rows below are the whole population for the examples. They are small enough to compute by hand, which is the point. You should be able to say what each query returns before you run it.

lesson maps les_07 to course crs_python. lrn_1042 completed attempt 1 and also has a checkpoint on attempt 2. lrn_1048 completed attempt 1 and has two enrollment rows, one refunded and one active. lrn_1055 completed attempt 1 and has an enrollment whose status is null. lrn_1060 has a completion and no enrollment row at all.

If your mental count of "completed learners with an active enrollment" is not exactly one (lrn_1042) until you decide what null means, the queries are doing their job. lrn_1048 is active on one row and refunded on another. A sloppy join counts them twice or, with a bad status predicate, drops them. lrn_1055 is the null case. lrn_1060 is the missing-dimension case. None of these are exotic. They are Tuesday.

Inner join fanout

sqlCopy
SELECT c.learner_id, e.status
FROM lesson_completion AS c
JOIN enrollment AS e
  ON e.learner_id = c.learner_id
WHERE c.lesson_id = 'les_07'
  AND c.attempt_number = 1
  AND c.definition_version = 'completion-v3';

This joins only on learner. lrn_1048 appears twice. lrn_1055 appears once with a null status. lrn_1060 disappears. A count(*) of this result is not a learner count. Adding AND e.course_id = 'crs_python' is necessary and still not sufficient, because two statuses for the same course still yield two rows. The fix is to reduce enrollment to the grain you mean before the join, or to use EXISTS.

The predicate you meant

sqlCopy
-- Drops null status. lrn_1055 disappears.
SELECT learner_id FROM enrollment WHERE status <> 'refunded';

-- Keeps active only. Null does not count as active.
SELECT learner_id FROM enrollment WHERE status = 'active';

-- Explicit if the spec says null is unpaid attention, not active.
SELECT learner_id FROM enrollment WHERE status IS NULL;

status <> 'refunded' is not the complement of status = 'refunded' in the presence of null. The complement also includes unknown. If the spec says "not refunded," write (status <> 'refunded' OR status IS NULL) and be sure the spec really wants nulls in the rate. For TechLearningPro it does not. Active means the string active. Null is a quarantine, listed by the third query, owned as a data defect rather than counted as a learner.

Aggregate before join

sqlCopy
WITH attempt_completion AS (
    SELECT learner_id, lesson_id, attempt_number, min(event_time) AS event_time
    FROM raw_progress_event
    WHERE event_type = 'completed'
    GROUP BY learner_id, lesson_id, attempt_number
),
active_enrollment AS (
    SELECT learner_id, course_id
    FROM enrollment
    WHERE status = 'active'
    GROUP BY learner_id, course_id
)
SELECT c.learner_id
FROM attempt_completion AS c
JOIN lesson AS l
  ON l.lesson_id = c.lesson_id
JOIN active_enrollment AS e
  ON e.learner_id = c.learner_id
 AND e.course_id = l.course_id
WHERE c.lesson_id = 'les_07'
  AND c.attempt_number = 1;

Each CTE has a grain you can say out loud. attempt_completion is one row per learner, lesson, and attempt, even if the raw zone illegally contained two completed events. The min(event_time) is a choice: the earliest event time wins for the fact's timestamp. If two completed events for the same attempt disagree, min hides the conflict. The strict approach is to HAVING count(*) = 1 and quarantine groups with a count above one. That is the SQL form of ID_CONFLICT when the raw key was not enforced. Prefer the constraint. Use the HAVING check as a test that the constraint held.

active_enrollment groups so two identical active rows cannot multiply. Two different statuses were already removed by the filter. lrn_1048 remains once. lrn_1055 and lrn_1060 are absent. The Monday count is 1, learner lrn_1042, plus lrn_1048 if their active row's course matches. In the sample, lrn_1048's active enrollment is crs_python, so both lrn_1042 and lrn_1048 qualify, and the null and the missing enrollment do not. Compute that before you trust the screen.

EXISTS, when you only need presence

sqlCopy
SELECT c.learner_id
FROM lesson_completion AS c
JOIN lesson AS l ON l.lesson_id = c.lesson_id
WHERE c.lesson_id = 'les_07'
  AND c.attempt_number = 1
  AND c.definition_version = 'completion-v3'
  AND EXISTS (
      SELECT 1
      FROM enrollment AS e
      WHERE e.learner_id = c.learner_id
        AND e.course_id = l.course_id
        AND e.status = 'active'
  );

Many active enrollment rows would still return each completion once. That is the right shape when the fact is already at the attempt grain and the question is existence. It is the wrong shape when you also need a column from enrollment, such as a plan name: existence cannot project the plan, and a join that projects the plan can multiply again. If you need the plan, the plan has to be unique per learner and course, enforced with a constraint, or you have not finished the model.

NOT IN and the empty report

sqlCopy
-- If any enrollment.learner_id is null, this returns no rows at all.
SELECT c.learner_id
FROM lesson_completion AS c
WHERE c.learner_id NOT IN (SELECT learner_id FROM enrollment);

Prefer:

sqlCopy
SELECT c.learner_id
FROM lesson_completion AS c
WHERE NOT EXISTS (
    SELECT 1 FROM enrollment AS e WHERE e.learner_id = c.learner_id
);

The quarantine of lrn_1060 should use the second form. The first form is a land mine left for the day a loader inserts a null learner id into enrollment, which the contract should have rejected and someday will not.

Building the fact table

The transform from raw completed events to lesson_completion for a window, assuming conflicts have already failed the load:

sqlCopy
INSERT INTO lesson_completion (
    learner_id, lesson_id, attempt_number, event_time, definition_version, source_event_id
)
SELECT
    learner_id,
    lesson_id,
    attempt_number,
    min(event_time) AS event_time,
    'completion-v3',
    min(event_id) AS source_event_id
FROM raw_progress_event
WHERE event_type = 'completed'
  AND event_time >= timestamptz '2026-03-02 00:00:00+00'
  AND event_time <  timestamptz '2026-03-03 00:00:00+00'
GROUP BY learner_id, lesson_id, attempt_number
HAVING count(*) = 1;

HAVING count(*) = 1 drops conflicting groups from the insert. That is dangerous if you do not also detect the dropped groups. Split the work: a CTE or a first query lists groups with count(*) > 1 and must return zero rows before the insert runs. A job that inserts the clean groups and ignores the dirty ones is the silent quarantine lesson 1 forbade. The practical pattern is to run the conflict query, abort the transaction if it returns rows, then insert. min(event_id) as a source pointer is only valid when the count is one. When the count is one, min and max agree.

Publication filter

sqlCopy
SELECT c.learner_id
FROM lesson_completion AS c
JOIN mart_publication AS p
  ON p.definition_version = c.definition_version
 AND p.event_day = (c.event_time AT TIME ZONE 'UTC')::date
WHERE c.lesson_id = 'les_07'
  AND c.attempt_number = 1
  AND p.definition_version = 'completion-v3'
  AND p.event_day = DATE '2026-03-02';

If the publication row is missing, the join returns nothing rather than an unofficial partial day. That empty result is what the studio must render as "not published," not as zero completions. The application can tell "query returned no learners and a publication exists" from "no publication row" by selecting the publication first. Do not collapse those in SQL with a function that turns absence into zero. Zero is a number. Absence of a publication is a status.

Constraints that belong in the table

sqlCopy
ALTER TABLE lesson_completion
    ADD CONSTRAINT lesson_completion_grain
    PRIMARY KEY (learner_id, lesson_id, attempt_number);

ALTER TABLE enrollment
    ADD CONSTRAINT enrollment_one_status
    UNIQUE (learner_id, course_id, status);

The unique constraint on enrollment does not stop two statuses. It stops two identical triples. If the spec is one row per learner and course regardless of status history, the analytical enrollment table is the wrong shape for history. Keep status history as events, and derive "current status" with a query you will write properly in lesson 7 using windows, or with a current-state table the operational system already has. Do not DISTINCT ON your way to a current status in this lesson. DISTINCT ON is a PostgreSQL feature and a frequent way to hide an arbitrary row. It waits until you can say which row must win. Lesson 7's window functions make the "which row" explicit.

Practical Implementation or Design Walkthrough

Problem

Load the sample population into a disposable PostgreSQL 16 database, prove the fanout, and publish a one-day completion fact inside a transaction that a second session cannot observe halfway.

Schema and rows

sqlCopy
CREATE TABLE lesson (
    lesson_id text PRIMARY KEY,
    course_id text NOT NULL
);

CREATE TABLE enrollment (
    enrollment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    learner_id text NOT NULL,
    course_id text NOT NULL,
    status text
);

CREATE TABLE raw_progress_event (
    event_id text PRIMARY KEY,
    learner_id text NOT NULL,
    lesson_id text NOT NULL,
    event_type text NOT NULL CHECK (event_type IN ('checkpoint', 'completed')),
    attempt_number integer NOT NULL CHECK (attempt_number >= 1),
    event_time timestamptz NOT NULL
);

CREATE TABLE lesson_completion (
    learner_id text NOT NULL,
    lesson_id text NOT NULL,
    attempt_number integer NOT NULL CHECK (attempt_number >= 1),
    event_time timestamptz NOT NULL,
    definition_version text NOT NULL,
    source_event_id text NOT NULL,
    PRIMARY KEY (learner_id, lesson_id, attempt_number)
);

CREATE TABLE mart_publication (
    publication_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    definition_version text NOT NULL,
    event_day date NOT NULL,
    published_at timestamptz NOT NULL DEFAULT now(),
    source_batch_id text NOT NULL
);

INSERT INTO lesson (lesson_id, course_id) VALUES ('les_07', 'crs_python');

INSERT INTO enrollment (learner_id, course_id, status) VALUES
    ('lrn_1042', 'crs_python', 'active'),
    ('lrn_1048', 'crs_python', 'refunded'),
    ('lrn_1048', 'crs_python', 'active'),
    ('lrn_1055', 'crs_python', NULL);

INSERT INTO raw_progress_event
    (event_id, learner_id, lesson_id, event_type, attempt_number, event_time)
VALUES
    ('evt_1001', 'lrn_1042', 'les_07', 'completed', 1, '2026-03-02T14:02:11Z'),
    ('evt_1002', 'lrn_1042', 'les_07', 'checkpoint', 2, '2026-03-02T16:00:00Z'),
    ('evt_1003', 'lrn_1048', 'les_07', 'completed', 1, '2026-03-02T15:00:00Z'),
    ('evt_1004', 'lrn_1055', 'les_07', 'completed', 1, '2026-03-02T15:30:00Z'),
    ('evt_1005', 'lrn_1060', 'les_07', 'completed', 1, '2026-03-02T18:00:00Z');

Prove the fanout, then the set you meant

Prove the fanout against raw_progress_event before you publish. lesson_completion is still empty at this step, so the technical-section query would return nothing and teach the wrong lesson.

sqlCopy
SELECT r.learner_id, e.status
FROM raw_progress_event AS r
JOIN enrollment AS e ON e.learner_id = r.learner_id
WHERE r.event_type = 'completed' AND r.attempt_number = 1;

Four completed attempt-1 events exist. lrn_1060 has no enrollment and drops off the inner join. lrn_1048 matches two enrollment rows and is emitted twice. lrn_1042 and lrn_1055 match once each. The result is four rows, not four learners. That is the fanout. The EXISTS query in the technical section is the set you meant, and it should be run after the publication insert, or pointed at raw_progress_event with the same active-enrollment predicate.

Then run the EXISTS query. Expected learner ids: lrn_1042 and lrn_1048. Not lrn_1055. Not lrn_1060.

Then the quarantine:

sqlCopy
SELECT r.learner_id
FROM raw_progress_event AS r
WHERE r.event_type = 'completed'
  AND NOT EXISTS (
      SELECT 1
      FROM enrollment AS e
      WHERE e.learner_id = r.learner_id
        AND e.course_id = 'crs_python'
        AND e.status = 'active'
  );

Expected: lrn_1055 and lrn_1060. Those ids go to a defect list. They do not go into the rate, and they are not deleted from raw storage.

Publish one day

sqlCopy
BEGIN;

INSERT INTO lesson_completion (
    learner_id, lesson_id, attempt_number, event_time, definition_version, source_event_id
)
SELECT learner_id, lesson_id, attempt_number, event_time, 'completion-v3', event_id
FROM raw_progress_event
WHERE event_type = 'completed'
  AND attempt_number = 1
  AND event_time >= timestamptz '2026-03-02 00:00:00+00'
  AND event_time <  timestamptz '2026-03-03 00:00:00+00';

INSERT INTO mart_publication (definition_version, event_day, source_batch_id)
VALUES ('completion-v3', DATE '2026-03-02', 'sample-b1');

COMMIT;

This insert is legal because the sample has one completed event per grain. Add a second completed event for lrn_1042 attempt 1 and the primary key must abort the statement. That abort is the outcome you want. Do not catch it in application code and continue to the publication insert. If the insert aborts, the transaction aborts, and mart_publication does not gain a row for a day that did not load. Verify by replacing COMMIT with a deliberate error in a drill: the previous publication, if any, is still the only one a second session can see.

A query you should refuse to merge

sqlCopy
SELECT count(*)
FROM lesson_completion AS c
JOIN enrollment AS e ON e.learner_id = c.learner_id
WHERE e.status <> 'refunded';

Refuse it in review. It joins on learner only, uses count(*), and treats null status as not worth keeping. Write the replacement using the CTE or EXISTS form, count(distinct c.learner_id), status = 'active', and the lesson's course id. Put the refused query in the pull request as the "before," so the grain change is visible.

Expected checks

Operational note

Run this on a disposable database. The role that inserts into lesson_completion is the job role from lesson 3. The role that runs the Monday SELECT cannot insert. A view named monday_completion should include the publication join so a convenience view cannot bypass it. Create that view as part of the exercise if you want the serving contract in SQL rather than in a promise.

Enterprise Real-World Scenarios

The doubled instructor count

Instructor studio showed 2,400 completers for a course whose raw completed events, counted with count(distinct learner_id), were 1,600. The studio query joined lesson_completion to enrollment on learner_id and counted rows. Many learners had an active enrollment and a historical refund row that the extract had started including "for finance." Finance needed the history. The studio did not. Both were pointed at the same join.

The repair was the split already required by the decision lens. Enrollment history stayed in an enrollment-event table. A derived active_enrollment relation, unique on (learner_id, course_id), fed the studio query. The studio counted distinct learner_id only as a second lock, not as the only lock. The publication timestamp was added to the page. Analytics owned the spec change from "any enrollment row" to "active enrollment for this course," and the data engineer owned the unique constraint. The 2,400 figure was withdrawn in the same day. It was not restated as a new official history, because it had never been a published grain. It was a bad query on top of a good fact table. That distinction matters: do not backfill the mart to match a fanout.

Fieldnote's revenue lines, the same join in another business

Fieldnote Retail, the staff-training retailer, reported net units sold by joining order lines to shipments on order_id alone. An order with two shipments duplicated every line. Returned lines were excluded with WHERE return_qty <> 0 reversed by mistake, and lines with a null return quantity, meaning "no return recorded," dropped out of gross sales as well. The Monday meeting subtracted a number that was not the complement of returns.

The SQL repair was identical in shape to the completion repair, which is why it belongs in a fundamentals lesson rather than in a retail case study. Aggregate shipments to one row per order line, or join them only when the grain of the question is the shipment. Use IS NULL deliberately for "no return recorded." Use EXISTS when the question is "was this line shipped at least once." Publish the result in a transaction that replaces the day. Fieldnote's analyst and TechLearningPro's analyst had not read each other's queries. They had both assumed a join attaches one row. The engine attached the number of matches.

Best Practices

• Write the grain of the result above the query. If a join can increase the count, aggregate or constrain the other side first.
• Prefer EXISTS when you mean presence. Prefer a real unique key when you need columns from the other table.
• Treat null as a case in the spec. Do not use <> as if it were a boolean complement.
• Use count(*), count(column), and count(distinct ...) as different questions. The Monday learner rate is a distinct count at a defined grain, or a count of a table that is already that grain.
• Put row filters in WHERE and group filters in HAVING.
• Name CTEs for the grain they produce. Do not add them for decoration, and do not expect them to act as an optimization fence.
• Use NOT EXISTS, not NOT IN, for anti-joins fed by a subquery.
• Convert timestamps to a named zone inside the metric query. Do not depend on the session TimeZone.
• Replace a restatement window inside one transaction, then insert the publication row in that same transaction.
• Let primary keys fail the load. Do not ON CONFLICT DO UPDATE a different payload over an accepted event.
• Point official views at published days only.
• Refuse SELECT DISTINCT as a fanout cure. It does not survive the next added column.

Common Mistakes and Anti-Patterns

Join on the wrong key, then distinct. Completions joined to enrollments on learner id, wrapped in SELECT DISTINCT so the chart looks stable. A new column in the select list brings the duplicates back, or the distinct hides a double count inside a sum of another measure. The repair is a unique active-enrollment relation and a join that includes course_id.

WHERE status <> 'refunded'. Null statuses vanish. In a left join, unmatched rows vanish too, which turns the left join back into an inner join. The repair is an explicit status = 'active' or an explicit null policy, and filters on the preserved table's columns only in the ON clause when you mean to keep the unmatched row. A predicate on the right table in WHERE is an inner join wearing a left join's clothes.

Counting rows of a join. count(*) after a join counts matches. The metric wanted learners. The repair is to count a key on a relation that has one row per learner, or count(distinct learner_id) after you have proved the join does not represent multiple real completions. Distinct is a check, not a design.

NOT IN against a nullable column. One null in the subquery empties the report. The repair is NOT EXISTS. Add a constraint that the key is not null so the trap has nothing to stand on, and still do not use NOT IN.

CTE as cargo cult. A stack of CTEs that join raw events to enrollment before aggregating is the fanout, formatted vertically. The repair is the order: reduce to the fact grain, then attach dimensions that are unique. Read the query from the innermost grain outward.

Delete and insert in two autocommit statements. The studio reads the hole. The repair is one transaction and a publication row committed with the insert. Test by killing the session before commit and confirming the previous day remains.

Blind upsert. ON CONFLICT DO UPDATE replaces a payload because a retried file differed by a space. The repair is do-nothing on the same id only when a conflict check has proved the payload matches, or reject the batch in the worker before SQL. The database constraint is the backstop, not the place that picks a winner.

Session time zone as the definition of Tuesday. The query casts timestamptz to date and the connection is set to a local zone. Events near midnight change day when the job moves from a laptop to a server. The repair is AT TIME ZONE 'UTC' in the SQL, matching the spec.

Troubleshooting and Design Review

Symptom: The distinct-learner count is stable and a sum of completion flags is high.

Likely cause: a join multiplies rows and one query hides it with distinct while another sums.

Investigation: run the query without the aggregate and look for repeated learner ids. Compare count(*) and count(distinct learner_id). Check how many enrollment rows exist per learner and course.

Corrective action: constrain or pre-aggregate the many side. Republish the official query. Do not change historical facts to match the inflated sum.

Symptom: A left join still drops learners.

Likely cause: a WHERE clause on a right-table column, or an inner join later in the FROM list.

Investigation: read the predicates. Move the right-table filter into the ON clause if unmatched rows must survive. Check join order.

Corrective action: rewrite and add a test row with no enrollment. The test row must appear in a left-join reconciliation and must not appear in the active-enrollment rate. Those are different queries. Keep both.

Symptom: A quarantine report that used to list unmatched learners is suddenly empty.

Likely cause: NOT IN and a new null key in the subquery, or a filter that excluded the sample.

Investigation: SELECT count(*) FROM enrollment WHERE learner_id IS NULL. Rewrite as NOT EXISTS and compare.

Corrective action: replace the predicate. Add NOT NULL to the key if the contract says the key is required. Find the loader that inserted the null. That loader is a contract bypass.

Symptom: Tuesday's count dips for ten minutes every morning.

Likely cause: the restatement commits the delete before the insert, or readers ignore mart_publication and scan during the load.

Investigation: read the transaction boundaries. Ask the studio which table it selects.

Corrective action: one transaction, and a view that joins publication. The dip is a serving bug if the job is already correct and the view is not.

Symptom: EXPLAIN shows a row estimate in the millions for active enrollment, and the table is supposed to be unique per learner and course.

Likely cause: statistics are stale, or the uniqueness is not real and the estimate is honest.

Investigation: SELECT learner_id, course_id, count(*) FROM enrollment WHERE status = 'active' GROUP BY 1, 2 HAVING count(*) > 1. Then look at the unique index. Lesson 8 covers ANALYZE and plans. You do not need it to interpret a duplicate key you can select.

Corrective action: if duplicates exist, stop the metric, quarantine, and add the constraint once the table is clean. If they do not, update statistics and confirm the plan changes before you touch the SQL.

A design review of a metric query reads the grain of each CTE, the join keys, the null predicate, the aggregate function, the time zone, and the publication predicate. It asks which sample learner is excluded and why. If the author cannot place lrn_1055 and lrn_1060, the query is not ready.

Interview Questions and Answers

Q1. What does a SQL join actually produce, and how does that break a completion count?

A join emits one output row for each pair of input rows that satisfies the predicate. It is not a lookup of a single match. If you join lesson completions to enrollments only on learner_id, a learner with two enrollment rows becomes two completion rows, and count(*) counts the pairs. I prevent that by joining on learner and course, by reducing enrollment to a unique active key first, or by using EXISTS when I only need presence. SELECT DISTINCT on the outside is a smell: it hides the multiplication until the select list changes.

Q2. Why does WHERE status <> 'refunded' drop learners whose status is null?

Comparisons with null yield unknown, not true. WHERE keeps only true rows, so null status fails <>, fails =, and fails most other comparisons. The learner disappears. I write the predicate the spec requires, usually status = 'active', and I put nulls in a quarantine query with IS NULL. I do not describe <> as "everyone else." In a left join, the same predicate in WHERE on the nullable side discards unmatched rows and silently turns the join into an inner join.

Q3. When do you use WHERE, and when do you use HAVING?

WHERE filters rows before grouping. HAVING filters groups after aggregates exist. Event type, attempt number, and the time window belong in WHERE. "Groups with more than one raw event" belongs in HAVING count(*) > 1, as a conflict check, not as a quiet deletion. I do not put event_type = 'completed' in HAVING. If the engine rejects WHERE count(*) > 1, that is a reminder of the order, not a reason to compute the aggregate in a subquery just to filter early. A conflict check should abort the load, not filter the metric.

Q4. What is the difference between count(*), count(course_id), and count(distinct learner_id)?

count(*) counts rows in the group, including rows whose columns are null. count(course_id) counts rows where course_id is not null, so a left join's unmatched rows disappear from the count. count(distinct learner_id) counts unique learner ids and can mask a join fanout if the question was really "how many fact rows." For a mart that is already one row per learner, lesson, and attempt, count(*) inside a lesson is a completion count at that grain. For the Monday question "how many learners," I count distinct learners or, better, count rows of a set that is already one row per learner. I say which question I am answering in the query name.

Q5. Why aggregate events before joining them to enrollment?

Raw progress is many rows per learner. Enrollment may be many rows per learner. Joining them first multiplies those mistakes together. I reduce events to the attempt grain in a CTE or derived table, reduce active enrollment to one row per learner and course, and then join on learner and course. Each CTE's grain is something I can state. A CTE does not guarantee materialization or speed. I use it so the grain is visible. If a reviewer cannot find the step where the grain becomes the fact, the query is not done.

Q6. Why do you prefer NOT EXISTS to NOT IN for unmatched completions?

If the subquery returns a null, NOT IN evaluates to unknown for the outer rows and the whole anti-join comes back empty. That can happen the day a bad load inserts a null learner id. NOT EXISTS asks whether a matching row is present and does not have that trap. It also does not multiply outer rows when several inner rows match, which is the property I want for "has no active enrollment." I still constrain the key NOT NULL. The constraint and the predicate are both there because one of them will be bypassed first.

Q7. How do you define a calendar day for an event stored as timestamptz?

I convert to the zone named in the metric spec inside the query. For this mart, UTC: cast (event_time AT TIME ZONE 'UTC') to date. I do not cast timestamptz to date directly, because that cast uses the session time zone and will move midnight events when the job runs under a different setting. The same conversion is in the publication's event_day. A restatement window is a half-open interval on timestamptz, [start, end), not a string comparison and not a filter on ingestion time.

Q8. How do you replace one event-time day without showing readers a hole?

In a single transaction I delete the completion rows for that definition version and window, insert the recomputed facts, and insert the publication row, then commit. Official readers join to mart_publication and see the previous commit until this one lands. Two autocommit statements are a hole. A view that reads lesson_completion alone is a hole even if the job is transactional. I test by rolling back and confirming a second session still sees the old publication. I do not TRUNCATE the whole mart to refresh a day.

Q9. What is wrong with INSERT ... ON CONFLICT DO UPDATE for raw progress events?

It treats a primary-key collision as "take the new payload," which is latest-wins. Lesson 1's rule is that the same event_id with the same payload is one event, and the same id with a different payload is a conflict that rejects the batch. A blind update also lets a bad backfill overwrite a good event inside the database after the Python worker tried to be strict. I insert new ids, leave conflicts as a failed statement or a quarantine query that aborts the transaction, and keep do-nothing upserts only for exact retries I have already proved identical. The worker's checksum is the first line. The primary key is the backstop.

Q10. A query is slow and someone proposes a cloud warehouse in the same sentence. What do you check first in SQL?

Whether the query is joining at the wrong grain and producing a huge intermediate set. A bad join can be slow on any engine and wrong on every engine. I look at count(*) versus count(distinct ...) and at duplicate keys. I may look at EXPLAIN to see an absurd row estimate, and I stop before tuning indexes if the set is not the set the spec asked for. Lesson 8 is the plan lesson. A new engine that runs the wrong join faster is still the wrong metric. The director question from lesson 2 and this question are the same question at two layers: shape, then mechanism.

Q11. How does a CTE differ from a temporary table you create yourself?

A CTE is a named subquery in one statement. The planner may inline it. It disappears when the statement ends, and it does not automatically get an index. A temporary table is a real table in the session, which you can index and inspect, and which you must create and drop. I use a CTE to make grain readable in a metric query. I use a temporary table when I am debugging a large step and need to see its rows, or when a measured plan requires a materialization the CTE will not give me. I do not tell interviewers that a CTE "always optimizes" or "always materializes." In PostgreSQL it depends, and the readability reason stands either way.

Q12. Which learners in a sample with a null enrollment status and a missing enrollment should appear in an "active enrollment" completion rate?

Only learners with a completion at the stated attempt and an enrollment row for that course whose status is the explicit value active. The null-status learner is quarantined, not treated as active and not treated as refunded. The learner with no enrollment row is quarantined by a NOT EXISTS report and excluded from the rate. If the business later decides previews count, that is a new definition version and a different predicate, not a silent change to treat null as active. I want the candidate to place both learners without running the query. If they cannot, they have not yet felt three-valued logic.

Hands-On Exercises

Exercise: Defend a completion rate in SQL

Problem statement. Using the sample schema, produce the official attempt-1 completion set for les_07 and the quarantine set. Then show a restatement transaction. Use PostgreSQL 16. Do not use window functions.

Tasks.

1. Create the tables, constraints, and sample rows. Add one extra completed event that collides on the grain and show the statement failing.
2. Write the fanout query and record its row count. Explain the count in one paragraph.
3. Write the official query with a CTE or with EXISTS, status = 'active', and course_id. List the learner ids.
4. Write the quarantine query with NOT EXISTS. Include the null-status learner and the missing-enrollment learner.
5. Show a NOT IN query that becomes empty if you insert a null learner_id, and then delete that null so the table is clean again.
6. Publish the day in one transaction that includes mart_publication. Roll the transaction back in a drill and state what a second session sees.
7. Create a view for the Monday read that cannot see unpublished days.
8. Add a comment above each query with its grain. Remove any SELECT DISTINCT you added to make a count "look right."

Expected deliverables. The SQL file, the result lists, the failed duplicate insert, and the view definition.

Evaluation rubric.

You pass when a reviewer can point to the exact predicate that excludes lrn_1055 and to the transaction that prevents a half-published day.

Lesson Summary

SQL will multiply a completion by every matching enrollment, drop a null as if it were a decision, and empty an anti-join if one key is unknown. Those are not edge cases. They are the meaning of joins and of three-valued logic, and they are how a green parser still feeds a wrong Monday number.

The habits that protect the grain are to aggregate or constrain before you join, to say active when you mean active, to use NOT EXISTS when you mean absence, to count the thing the spec named, and to publish a day in one transaction the studio can see only through mart_publication. DISTINCT and a blind upsert are how those habits get undone in a review that is in a hurry.

Window functions, which you will want the moment the question becomes "the latest enrollment status per learner," are the next lesson. They do not repair a join that was wrong before the window was applied. Get the set right first.

Further Reading

• PostgreSQL 16: SELECT — WHERE, GROUP BY, HAVING, FILTER, and WITH.
• PostgreSQL 16: table expressions and joins — join types and what a missing condition becomes.
• PostgreSQL 16: comparison functions and null — three-valued logic, the reason <> is not a complement.
• PostgreSQL 16: INSERT and ON CONFLICT — use it as a backstop with your eyes open, not as a silent merge.
• PostgreSQL 16: transactions and date/time functions — atomic restatement and AT TIME ZONE.
• Primers: /learn/sql and /learn/postgresql. This lesson assumed you would rather apply the language than relearn the keywords in isolation.
• Continue this course: /learn/data-engineering/advanced-sql-and-window-functions, then /learn/data-engineering/relational-database-internals.