"SQL You Will Actually Use" — Analytical SQL for Data Engineers

Skim Card

One thing to remember: analytical SQL works on sets and ordered windows over history; most wrong answers come from the wrong key, the wrong order or the wrong frame, not from wrong syntax.

• Deduplicate on the business key: event_id found no duplicates; basket_id found 2,455 UK re-sends worth exactly £118,076.98, revenue step 1.
• A window’s ORDER BY must be unique. With retried events kept, the session count changed between runs.
• ORDER BY without a frame means RANGE … CURRENT ROW: tied rows share one running total. Say ROWS when you mean rows.
• A session is a definition: 30 idle minutes without bots gives 6.21%, a UTC day with bots 5.31%; bots alone cost 1.23 points.
• Distinct counts don’t add up across days; HyperLogLog sketches do: merged daily sketches landed 2.6% low.
• MERGE survives retries only with recomputed, unique source rows: increments counted an upload twice; duplicate source rows inserted twice.
• A high-water mark on event time never loaded 19,804 late baskets; on upload time, none were lost.

Use …	When …
QUALIFY row_number() … = 1	keep one row per business key (DuckDB, Snowflake, BigQuery, Databricks, Redshift)
Gaps and islands (date - row_number())	consecutive runs: stock-out days, streaks, failures
GROUPING SETS / CUBE	several subtotal levels in one scan
approx_count_distinct / sketches	distinct counts over big data, or across many ranges of days
MERGE with recomputed rows	upserts that must survive retries

Verified against: DuckDB 2.0.0, Trino 484, Iceberg 1.12 tables via Polaris 1.8 · vendor docs · 28 October 2026

Jump to: Concepts · Hands-On · Landscape · Design Drill

Story Opening

Rohan had a plan for the clickstream. “Five million events. Stream them sorted by visitor into a Java service, keep the last timestamp in a map, emit a session when the gap passes 30 minutes. The duplicates are easier still: a HashSet of event IDs.”

“The event IDs are all unique,” Ingrid said. “The duplicates aren’t. And your loop is a window function: partition by visitor, order by time, look at the previous row. The engine does the sort and the parallelism, in one query finance can read. Find the re-sent baskets and sessionize the clicks both ways.”

The Quick Map

Concepts, with use cases

SQL is the language of this job. A July 2026 job-board analysis by huntr, not independently verified, found SQL in 67.4% and Python in 74.3% of 110,933 data engineering job postings. The SQL that matters is less JOIN syntax than what follows.

Window functions

A window function computes a value for each row from related rows without collapsing them as GROUP BY does. PARTITION BY says which rows belong together, ORDER BY their sequence, and the frame which of them count for this row. Ranking: row_number() gives 1, 2, 3, 4 (ties broken arbitrarily), rank() 1, 2, 3, 3, 5, dense_rank() 1, 2, 3, 3, 4. Offsets: lag() and lead() read the previous or next row. Frames: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is the last seven rows, RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW the last seven days, GROUPS counts distinct ORDER BY values. With ORDER BY and no frame, DuckDB and Trino both default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes every row tied with the current one.

What breaks without them. Self-joins (a.ts < b.ts) or application loops that are slower, harder to read and usually wrong at the edges. Larkspur: sessions, re-sends and moving sums below. Elsewhere: a game studio’s leaderboard needs dense_rank() so tied players share a place, and an account statement is a running balance ordered by posting time and a unique entry ID.

QUALIFY and deduplication

QUALIFY row_number() OVER (PARTITION BY basket_id ORDER BY uploaded_at, event_id) = 1 keeps one row per basket: QUALIFY filters on a window result the way HAVING filters on an aggregate. Two choices make it right: the partition key is the business identity (Part 2’s dedup key), and the ordering decides which copy wins, deterministically.

What breaks without it. Dedup by the message ID removes nothing when a producer re-sends with a new ID. Larkspur: offline tills re-send buffered baskets with a new event_id. Elsewhere: an IoT fleet’s gateways re-transmit readings after a reconnect; the key is (device, reading timestamp), not the MQTT message ID.

Gaps and islands

An island is a run of consecutive values (days out of stock, days with a failed payment); a gap is the space between runs. The standard trick: number the qualifying rows per entity with row_number() and subtract that from the date. Within a consecutive run both rise by one per row, so date - row_number() is constant and names the island. GROUP BY that and each run becomes one row with its first day, last day and length.

What breaks without it. “How long was it empty?” turns into a self-join per day or a loop. Larkspur: consecutive stock-out days per store and SKU, using Part 5’s close-of-day measure. Elsewhere: a SaaS product team measures daily-active streaks; a utility finds meters that sent nothing for three or more days in a row.

Sessionization

A session groups one visitor’s events into visits. The gap rule is three window steps:

The gap, whether bots count and whether a session may cross midnight are business decisions, and each changes the denominator of every rate built on sessions. What breaks without it. Each team writes its own rule into its own query, and nobody can say why the rates differ. Larkspur: product uses a 30-minute gap without bots; marketing one session per visitor per UTC day, bots included. Elsewhere: a video streaming service argues about what a “view” is: 30 seconds watched, or a play button pressed?

PIVOT and UNPIVOT

UNPIVOT turns columns into rows (five funnel-step flags become five rows); PIVOT turns row values into columns (one column per team). Both are shorthand for UNION ALL and conditional aggregation. What breaks without it. Hand-written CASE columns that drift when a step is added. Larkspur: the funnel table below. Elsewhere: a hospital lab stores results long (patient, test, value) and clinicians want one row per patient with a column per test.

GROUPING SETS, ROLLUP and CUBE

GROUP BY GROUPING SETS ((a, b), (a), ()) computes several aggregation levels in one pass. ROLLUP (a, b) is the hierarchy (a, b), (a), (); CUBE (a, b) is every combination. grouping(a) returns 1 on rows where a was rolled up, so labels and guards don’t rely on NULL. What breaks without it. Four queries glued with UNION ALL, each scanning the table, one filtered slightly differently. Larkspur: baskets and sales by currency and channel (step 6). Elsewhere: a parcel carrier reports shipments by region and service with region subtotals and a network total on one page.

Approximate distinct counts

Exact count(DISTINCT x) must remember every value it has seen. HyperLogLog (HLL; Flajolet et al., 2007) estimates it from a few kilobytes of state, with a standard error of about 1.04/√m for m registers. Distinct counts are not additive (daily visitors summed over a quarter count a returning visitor once per day), but HLL sketches merge: keep one per day and any range is a merge away. What breaks without it. Exact distinct over billions of rows needs memory for every value, and a table of daily counts can’t answer “visitors in the last 30 days”. Larkspur: visitors per day, week and quarter from daily sketches. Elsewhere: an adtech platform reports unique reach per campaign over any date range an advertiser picks, from stored daily sketches.

MERGE (upsert)

MERGE INTO target USING source ON … WHEN MATCHED THEN UPDATE … WHEN NOT MATCHED THEN INSERT … applies a batch of changes in one statement. It is safe to re-run only if the source holds recomputed values (the new truth for each key), not increments, and each key appears once in the source. DuckDB, like most engines, raises an error when two source rows match one target row, but inserts both of two unmatched copies (the last statements of merge.sql). What breaks without it. A delete-then-insert that a crash leaves half done, or increments that double on a retry. Larkspur: daily store sales absorbing a late till upload. Elsewhere: a CRM sync that upserts 50 million contacts nightly from a vendor extract.

As-of joins, briefly

An as-of join matches each row to the latest version at or before its timestamp: Part 4’s promotion versions, Part 5’s FX rates. DuckDB has ASOF JOIN; Trino added a NEAREST join in release 481.

Incremental loads and high-water marks

An incremental load remembers the largest value it has loaded (the high-water mark) and next time reads only rows above it, which is correct only if new rows always arrive above the mark. Late data breaks that for event time. The classic trap with updated_at: a transaction that started before the poll commits after it, with a timestamp below the new mark, and is never read; deletes leave nothing to read at all. Remedies: re-read an overlap window below the mark and deduplicate by key, or read the database log with CDC (Part 16). Part 15 goes deeper. Larkspur: offline tills (step 9). Elsewhere: a logistics company polls carriers’ APIs with modified_since and loses scans a depot uploads a day late.

Reading query plans

EXPLAIN shows the plan the engine intends; EXPLAIN ANALYZE runs the query and reports actual rows and time per operator. Read it bottom-up: what was scanned, where rows were reshuffled between workers, which operator took the time, where the row count collapsed. Estimates far from actual rows mean the optimiser was guessing. Larkspur: step 10. Elsewhere: a fintech’s month-end report slows tenfold, and the plan shows one join now shuffling the whole ledger.

Under the Hood

What a window costs

An engine evaluates OVER (PARTITION BY k ORDER BY t) in three moves: route rows so each partition is in one place (in a cluster, a hash shuffle across the network), sort each partition by t, then stream through it. Ranking and lag() look at one neighbour; running sums carry an accumulator; moving frames add the entering row and subtract the leaving one, or use structures such as segment trees for min and max. The cost is mostly the sort and, distributed, the shuffle; step 10 shows both in a Trino plan.

Why ties change answers

A sort may put rows with equal keys in any order, and parallel sorts do. When lag() and sum() each sort a partition, two rows with the same key can come out in one order for the first window and the other for the second; if they differ in the value you compute (“starts a session”), the result changes from run to run (Troubleshooting has the numbers). A unique ORDER BY, or deduplicating first, removes the freedom.

How HyperLogLog estimates

Hash every value; the first bits pick one of m registers, and each register keeps the longest run of leading zeros seen in the rest. A run of k zeros suggests about 2^k distinct values passed through; a harmonic mean across registers tames the luck of any one. Merging two sketches is the register-wise maximum, so daily sketches combine into any range exactly as if built over it.

Hands-On

The code is in the every-record-counts folder of the companion-workspaces repository (tag every-record-counts/part-6). The workbook is a DuckDB file, data/lab/part06/workbook.duckdb; it builds two tables from the raw zone and attaches Part 5’s star read-only as star. Outputs drop DuckDB’s column-type and border lines; times are from a MacBook Pro M3 Pro.

Step 0: build the workbook

uv run python -m scripts.part05_shapes build --local    # Part 5's database, if you don't have it
uv run python -m scripts.part06_sql build --local

clicks      5,609,827 rows    1.9s
sessions    5,161,386 rows    1.2s

clicks keeps every event, with retries and bots as flag columns and the renamed page field coalesced; sessions holds both definitions. The build peaked at 3.7 GiB resident.

Step 1: find the re-sent POS events (revenue step 1)

sql/part06/pos_duplicates.sql first asks which column is a key:

│ country │ events  │ event_ids │ baskets │ extra_copies │
│ GB      │  798289 │    798289 │  795834 │         2455 │
│ IN      │ 1136376 │   1136376 │ 1131230 │         5146 │
│ SG      │  404147 │    404147 │  402294 │         1853 │

Every event_id is unique; 9,454 baskets arrived twice. The second statement numbers each basket’s copies with row_number() and compares each with the previous one through lag(): all 9,454 copies carry identical lines, tenders and timestamp, from 3,253 tills, between 1 and 30 minutes (median 15 minutes 44 seconds) after the original. The third is the legacy board-deck view of UK till sales (UTC dates, every event), with and without the copies:

WITH gb AS (
    SELECT tenders[1].amount_minor AS amount_minor,
           row_number() OVER (PARTITION BY basket_id ORDER BY uploaded_at, event_id) AS copy_no
    FROM raw_pos_events
    WHERE store_id LIKE 'GB-%'
      AND event_ts::DATE BETWEEN DATE '2026-07-01' AND DATE '2026-09-30'
)
SELECT count(*) FILTER (WHERE copy_no > 1)                     AS resent_events,
       major(sum(amount_minor))                                 AS every_event_gbp,
       major(sum(amount_minor) FILTER (WHERE copy_no = 1))      AS one_per_basket_gbp,
       major(sum(amount_minor) FILTER (WHERE copy_no > 1))      AS resent_gbp
FROM gb;

│ resent_events │ every_event_gbp │ one_per_basket_gbp │  resent_gbp   │
│          2455 │     27948482.95 │        27830405.97 │     118076.98 │

£118,076.98: exactly waterfall step 1 in docs/injected-defects.md, taking the board deck from £29,999,232.43 to £29,881,155.45. Part 4’s staging quietly kept one row per basket; this proves it was the right key and what it removed. With Parts 4 and 5, four of the five steps are explained; the hard-deleted orders wait for Part 16. Finding duplicates is not preventing them: Part 21 makes every hop idempotent and proves it with control totals.

Step 2: two session definitions

The product rule in sql/part06/sessions.sql (DuckDB); marketing’s is GROUP BY anonymous_id, event_ts::DATE:

WITH events AS (
    SELECT * FROM clicks WHERE NOT is_retry
),
flagged AS (                -- product: a new session after 30 minutes without an event
    SELECT *,
           coalesce(event_ts - lag(event_ts) OVER w > INTERVAL 30 MINUTE, true) AS starts_session
    FROM events
    WINDOW w AS (PARTITION BY anonymous_id ORDER BY event_ts, event_id)
),
numbered AS (               -- a running count of session starts numbers the sessions
    SELECT *,
           sum(starts_session::INT) OVER (PARTITION BY anonymous_id ORDER BY event_ts, event_id
                                          ROWS UNBOUNDED PRECEDING) AS session_no
    FROM flagged
),
...                         -- then both definitions, one row per session with a flag per funnel step

sql/part06/conversion.sql crosses each rule with each bot policy:

│ definition │   bots   │ sessions │ converted │ conversion_pct │
│ product    │ excluded │  2161516 │    134224 │           6.21 │
│ product    │ included │  2635601 │    134224 │           5.09 │
│ marketing  │ excluded │  2051700 │    134112 │           6.54 │
│ marketing  │ included │  2525785 │    134112 │           5.31 │

Part 0’s numbers sit at opposite corners, and the 0.90-point gap is two decisions pulling in opposite directions: bots take marketing from 6.54% to 5.31% (−1.23 points); the day rule lifts 6.21% to 6.54% (+0.33). Why the day rule finds fewer human sessions, adding up exactly:

│                  step                  │ sessions │
│ product sessions (30 minutes, no bots) │  2161516 │
│ - later visit, back within 90 minutes  │   -67142 │
│ - later visit, same day as a purchase  │   -30902 │
│ - later visit, no purchase that day    │   -13273 │
│ + session crossing UTC midnight        │     1501 │
│ marketing sessions (UTC day, no bots)  │  2051700 │

The day rule merges a buyer’s earlier research visit into the buying one (30,902) and returns within 90 minutes, often a tab left open (67,142): fewer sessions, nearly the same purchases, a higher rate. Each of the 474,085 bot hits is a new visitor and a session that never buys. The 16,644 SDK retries change event counts, not sessions (third statement). Neither query is wrong; which definition Larkspur reports belongs in a contract (Part 20) and one metric definition (Part 31).

Step 3: the funnel, both ways

sql/part06/funnel.sql turns the five step flags into rows (UNPIVOT team_sessions ON page_view, product_view, add_to_cart, checkout_start, purchase INTO NAME step VALUE reached), aggregates per definition and step, and turns the definitions into columns (PIVOT funnel ON definition USING any_value(pct_of_sessions) GROUP BY step). Each team keeps its own bot rule:

│      step      │ marketing │ product │
│ page_view      │     99.84 │   96.92 │
│ product_view   │      41.3 │   50.15 │
│ add_to_cart    │     15.39 │   18.29 │
│ checkout_start │      5.31 │    6.21 │
│ purchase       │      5.31 │    6.21 │

Bots only view pages, so they inflate marketing’s top step and dilute every later one; 3% of product sessions start mid-visit, on a product page after an idle gap. “Reached the step” is a definition too: requiring the steps in time order (the file’s second statement) passes only 87,012 product sessions (4.03%), because short paths skip steps.

Step 4: windows that look right and aren’t

sql/part06/windows.sql runs three checks on Part 5’s star. Ranking: in 9 of 600 stores the third and fourth best sellers tie, so “top 3” by row_number() drops one arbitrarily (1,800 rows) where rank() keeps both (1,809). Default frame: sum() OVER (ORDER BY date) gives every line of 1 July in one store the day’s total, 48,609 pence, on the first line as on the last. Then a 7-day moving sum on one SKU whose sales skip days (middle rows trimmed):

│    date    │ days_since_last_sale │ units  │ last_7_rows │ last_7_days │
│ 2026-08-12 │                    2 │      1 │          15 │          12 │
│ 2026-08-17 │                    2 │      1 │          20 │          12 │
│ 2026-08-20 │                    3 │      4 │          23 │          15 │

│ days_with_sales │ days_where_they_differ │ most_days_in_7_rows │
│              53 │                     41 │                  13 │

A 7-row frame covers up to 13 calendar days; RANGE with INTERVAL 6 DAYS disagrees with it on 41 of 53 days.

Step 5: gaps and islands on the stock shelf

sql/part06/stockouts.sql numbers each store-SKU’s empty days at close and groups by date - row_number():

SELECT store_id, sku, min(date) AS first_day, max(date) AS last_day, count(*) AS run_days
FROM (
    SELECT *, date - CAST(row_number() OVER (PARTITION BY store_id, sku ORDER BY date) AS INTEGER) AS island
    FROM days WHERE empty_at_close
)
GROUP BY store_id, sku, island

│   measure   │ stockout_days │  runs  │ one_day_runs │ runs_14_plus │
│ at close    │       1284530 │ 793952 │       712239 │        19219 │
│ as reported │        999923 │ 537956 │       475449 │        18803 │

Most of the 793,952 runs (712,239) last one day: sold out, refilled overnight. The 19,219 runs of two weeks or more are the supply outages store managers complained about. On the snapshot as reported, 284,607 empty days and 236,790 one-day runs vanish, refilled by a morning delivery before the count (Part 5’s fix, seen as runs). Partitioning by product_key instead of sku finds 794,262 runs, not 793,952 (Gotchas).

Step 6: subtotals in one scan

sql/part06/rollup.sql groups Q3 baskets by CUBE (currency, channel) in one scan and labels subtotal rows with grouping(): 19 rows, from GBP till sales (580,524 baskets, £27,830,443.52) and all GBP channels (632,125, £30,264,520.68) up to every basket in every currency (1,829,698), where sales is NULL.

Step 7: exact or approximate visitors, in DuckDB and Trino

sql/part06/approx.sql compares DuckDB’s approx_count_distinct with the exact count of human visitors, per UTC day in Q3:

│ days  │ mean_abs_error_pct │ max_abs_error_pct │ sum_of_daily_visitors │ quarter_visitors │
│    92 │               2.59 │              9.04 │               2051655 │          1061054 │

DuckDB exposes no mergeable sketch, so the range question goes to Trino (make up-trino). scripts/part06_trino.py load writes clicks to an Iceberg table through Polaris with DuckDB; Trino reads it as lakehouse.part06.clicks. Core plus Trino peaked at 4.3 GiB of container memory, 2.9 GiB of it Trino. sql/part06/trino/approx.sql counts Q3 visitors both ways, then keeps one approx_set() sketch per Q3 day and merges them with cardinality(merge(sketch)):

visitors    exact   approx  error_pct
  humans  1061054  1033590      -2.59
    bots   474085   468930      -1.09

days  sum_of_daily_visitors  merged_daily_sketches  exact_visitors
  92                2051655                1033939         1061054

Trino documents a 2.3% standard error, so −2.59% is about one; DuckDB’s daily errors reached 9%. Summed daily counts overstate quarterly visitors almost twofold; the merged sketches land 2.6% low.

Step 8: MERGE that survives a retry

sql/part06/merge.sql loads daily store sales from uploads up to 14 September, then applies the 15 September upload twice, in two styles: recomputed values for every (store, day) the upload touched, or the upload’s own counts as increments.

MERGE INTO daily_store_sales t
USING touched(DATE '2026-09-15') s
ON t.store_id = s.store_id AND t.business_date = s.business_date
WHEN MATCHED AND (t.baskets, t.sales_minor) IS DISTINCT FROM (s.baskets, s.sales_minor)
    THEN UPDATE SET baskets = s.baskets, sales_minor = s.sales_minor
WHEN NOT MATCHED THEN INSERT BY NAME;

│                     step                     │ store_days │ baskets │ sales_minor  │
│ 0. loaded up to 14 Sep                       │      63645 │ 1957072 │ 281588101237 │
│ 1. recomputed MERGE, run 1                   │      64248 │ 1973458 │ 283841746359 │
│ 2. recomputed MERGE, run 2                   │      64248 │ 1973458 │ 283841746359 │
│ 3. increment MERGE, run 1                    │      64248 │ 1973460 │ 283841750361 │
│ 4. increment MERGE, run 2                    │      64248 │ 1989848 │ 286095399485 │
│ 5. truth: rebuilt from all uploads to 15 Sep │      64248 │ 1973458 │ 283841746359 │

The recomputed MERGE matches the full rebuild and changes nothing on run 2. The increment MERGE is two baskets off after one run (two re-sends whose original came in an earlier upload; the file counts them) and adds the whole upload again on the retry.

Step 9: a high-water mark on the wrong clock

sql/part06/high_water_mark.sql replays every nightly upload: each night’s job loads rows above the maximum it had seen on earlier nights (a window frame ending at 1 PRECEDING), once with the mark on event_ts and once on uploaded_at:

│ baskets │ never_loaded_by_event_ts │ never_loaded_by_uploaded_at │ median_behind_the_mark │
│ 2329358 │                    19804 │                           0 │ 08:50:49.6975          │

19,804 offline baskets landed under the event-time mark, a median 8 hours 51 minutes behind it, and would never be loaded. Upload time only moves forward here, so it loses nothing; Part 2’s rebuild-what-the-upload-touched job is the same idea.

Step 10: read the plans

sql/part06/explain.sql runs DuckDB’s EXPLAIN ANALYZE on the re-send count straight from the JSON files: 128 files, four fields projected, 2,338,812 rows into the window and 9,454 out of the filter; the scan took 3.59 s and the window 1.05 s, times summed over threads (the query took 0.877 s). Trino’s EXPLAIN ANALYZE of a similar query, clickstream retries per country over lakehouse.part06.clicks (sql/part06/trino/explain.sql), shows the distributed shape (trimmed):

Fragment 2 [HASH]
          └─ Window[partitionBy = [event_id], orderBy = [event_ts ASC NULLS LAST]]
             │   CPU: 2.58s (83.91%), Scheduled: 3.07s (75.68%), Blocked: 0.00ns (0.00%), Output: 5609827 rows (267.50MB)
             │   row_number := row_number() RANGE UNBOUNDED_PRECEDING CURRENT_ROW
Fragment 3 [SOURCE]
    Output partitioning: HASH [event_id]
        Input: 5609827 rows (240.75MB), Physical input: 85.96MB, Physical input time: 137.92ms, Splits: 1, ...

Fragment 3 reads three columns (86 MB of Parquet) and hash-partitions rows by event_id: the shuffle. Fragment 2 sorts and windows, 84% of the CPU, and sends 12 pre-aggregated rows on. Trino printed the default RANGE frame for row_number(). sql/part06/trino/dialect.sql shows Trino rejecting QUALIFY (parsed as a table alias: mismatched input 'row_number') and reproducing the 30-minute sessions exactly: 2,161,516 sessions, 6.21%.

Step 11: the tests

tests/test_part06.py checks the headline figures against the small generated world’s manifest, then this post’s full-size figures, then Trino.

uv run pytest -q tests/test_part06.py

..........                                                               [100%]
10 passed in 28.06s

The Landscape

Features as of 28 October 2026, from each vendor’s docs.

Tips & Gotchas

Gotcha — schema inference reads a sample. DuckDB’s read_json looks at 32 files by default; a field that first appears later isn’t a column, and nothing warns you. Use union_by_name = true for drifting JSON and assert expected columns in a test.

Gotcha — partition islands and windows by the natural key, not a Type 2 surrogate. product_key changes with the product’s attributes; 310 stock-out runs were cut in two on 1 August.

Gotcha — CUBE and ROLLUP will happily add rupees to pounds. Step 6 guards money with CASE WHEN grouping(b.currency) = 0 THEN …; baskets can add up across everything.

Tip — before choosing a dedup key, count side by side. Rows, distinct message IDs and distinct business keys in one query (step 1) shows which column means “once”.

Gotcha — Trino’s PIVOT needs GROUP BY inside the parentheses. Without it you get one row (GROUP BY ()); DuckDB’s PIVOT keeps the other columns by default.

Troubleshooting

Every row happened while building this part.

Design Drill

The question: “A digital wallet logs every payment attempt: about 2 billion rows a year, users in 30 countries. The risk team wants every user who had failed payments on three or more consecutive days, refreshed daily, with history back to launch. Write the query and design how it runs.”

Clarify first

• What is a failure: declines only, or errors and timeouts too? Are client retries of one attempt (same idempotency key) one failure?
• Whose day: UTC, or the user’s local date? From the attempt or the profile?
• Consecutive calendar days, or does a successful payment in between break the streak?
• How late can attempts arrive, and who consumes the list: a nightly job, or an alert within minutes?

The shape of a strong answer

• Reduce first: deduplicate attempts by idempotency key, then one row per (user, local day) with at least one failure. Nothing downstream touches raw attempts.
• Size it: if about 5% of attempts fail (confirm), 2 billion rows become ~100 million failures and fewer (user, day) rows still, cheap to window. That is why reducing first wins.
• Gaps and islands on that table: day - row_number() OVER (PARTITION BY user_id ORDER BY day) names each run; GROUP BY user_id, island HAVING count(*) >= 3. MATCH_RECOGNIZE (Trino, Snowflake, BigQuery) says the same as a pattern.
• Store attempts partitioned by date and bucketed by user_id: bucketing lets a window by user skip the shuffle in engines that honour the bucket layout (Spark, for example); clustering alone only improves locality and pruning.
• Run incrementally: a streak table per user (last failed day, length), updated daily by a recomputed, keyed MERGE; late attempts reopen days inside a lateness window. History is one backfill.
• Watch skew: card-testing bots put millions of failures on a few accounts. Route them to fraud first, or one partition does all the work.

Trade-offs to say out loud

• Batch is simple and replayable; streaming (Flink keyed state with timers, Parts 28–29) gives minutes of latency but needs its own lateness policy.
• Local days are what risk means but need a time zone per attempt; UTC is cheap and wrong far from UTC.

Common weak answers

• Self-joining raw attempts on day + 1 and day + 2: three scans of two billion rows, and duplicates multiply.
• lag() over attempts instead of days, so three failures in one afternoon count as three days.

Further Reading

1. DuckDB, “Window Functions” (frames and defaults). https://duckdb.org/docs/2.0/sql/functions/window_functions.html
2. Trino, “HyperLogLog functions”. https://trino.io/docs/current/functions/hyperloglog.html
3. Philippe Flajolet, Éric Fusy, Olivier Gandouet, Frédéric Meunier, “HyperLogLog: the analysis of a near-optimal cardinality estimation algorithm”, AofA 2007. https://dmtcs.episciences.org/3545/pdf
4. Stefan Heule, Marc Nunkesser, Alex Hall, “HyperLogLog in Practice: Algorithmic Engineering of a State of The Art Cardinality Estimation Algorithm”, EDBT 2013. https://research.google/pubs/hyperloglog-in-practice-algorithmic-engineering-of-a-state-of-the-art-cardinality-estimation-algorithm/

Story Closing

The workbook went to Farah with two answers. The largest revenue step is 2,455 UK baskets the tills sent twice, £118,076.98, found by asking which column is really the key. Conversion is 6.21% or 5.31% because two teams made two reasonable decisions, about bots and about what a visit is. Rohan’s Java loop never got written. Of the five promises, this part served uniqueness: count every basket, session and visitor once, by the key that means once.

“SQL gives the same answer twice only if you tell it exactly what you mean,” Ingrid said. “Same goes for tables.”

In Part 7, the tables themselves get the same scrutiny. Rohan opens up Apache Iceberg: the metadata tree under a table, what a commit really swaps, and what a MERGE like step 8’s does to the files.

This is Part 6 of “Every Record Counts: Data Engineering for Backend Engineers” — Season 1: Foundations & the Lakehouse.