DB Olympics

Queries · q344 · VP Platform Engineering · week 2, night 4

Orders in the 14:00-16:00 UTC Window: Nov 28 vs Baseline Days

“How many orders (and how much revenue) were actually lost in the 14:00-16:00 UTC outage window on Black Friday, compared to the same window on comparable days?”

Manager · asked the questionDiego MoralesVP Platform Engineering. Former reliability lead at a payments company. Owns uptime and the integrations merchants depend on.
Analyst · planned the reportLucas MoreauPragmatic: answers the question asked with the simplest reliable report.
Engineer · wrote the SQLViktorOld-school warehouse engineer: explicit JOIN chains straight to the base tables, derived tables, GROUP BY with CASE pivots, few CTEs.

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

  1. A business event lands on the VP Platform Engineering's desk

    Week 1; shared with Samuel Whitcombe, Head of Merchant Success

    Leadership wants a review of platform reliability in 2025: failed and slow background jobs, health check failures, rate limiting, webhook delivery problems, and which merchants felt them.

    One of the 35 events we wrote for the role play; it shaped the manager's first note. How events arise · All the events

  2. Diego Morales writes to Lucas Moreau

    The manager, at the start of week 2

    Subject: Re: 2025 Platform Reliability Review — good start, a few things I need tightened up

    Lucas,

    Thanks — this is useful and more honest than I expected on the limits of what we can see. A few things stood out and need more work before Friday's memo goes to leadership.

    First, the retention gap is itself a finding, not just a caveat. We're flying with 90 days of job history and 60 days of health-check history — that means we genuinely don't know how 2025 looked before October. I want that stated plainly and separately in the memo as a reliability/observability gap in its own right, not buried as a footnote under the carts.remind story.

    Second, the ERP/WMS/Accounting webhook stuck-retry pattern is, to me, the most important and actionable thing in here — more than the one-week carts.remind incident. I want that properly validated and sized, not eyeballed, before we say anything about it to leadership.

    Questions for this week:

    1. Can you redo the webhook comparison correctly and tell me: how many merchants are affected by this stuck-retry pattern, and what's the revenue or order volume behind them? I want to know if this is a handful of our biggest accounts or a long tail.
    2. Has the ~15% stuck-retry rate on those sync webhooks been happening all year, or is it new/recent? Even without full job history, is there any way to tell whether this is a longstanding bug we've lived with or something that regressed recently?
    3. On carts.remind — you said it looks tied to a feature flag or config rather than load. Can we get more confidence on that? I want to know if this is a one-off we already fixed, or a latent risk that could recur with other job types.
    4. Setting aside strict proof of causation, can you give me your best read on whether the ticket-volume climb for the 10 affected tenants in the weeks before the failures showed up in our logs is something merchants were telling us about directly (i.e., check if their tickets mention sync/reminders/missing carts) — even a rough read would tell us if our support channel was effectively an early warning system we ignored.

    On rate limiting: agreed, drop it for this cycle — don't spend a slot trying to force an answer out of config data alone. Just flag it as a 2026 instrumentation ask, as you already have.

    Priority for your remaining slots: #1 and #2 above. The webhook/sync issue is the one I can take into a fix-it conversation with engineering next week if the numbers hold up — I'd rather have that nailed down than more polish on the carts.remind narrative.

    Diego

  3. Lucas Moreau plans the night

    The analyst · week 2, night 4, request 1 of 3

    Report 1 confirms health checks for checkout/storefront run only every 2 hours (checks exist at 12:00, 14:00, 16:00 but nothing in between) - so even though monitoring did catch a 100%-unhealthy reading at 14:00, our detection granularity for this path is coarse (up to 2 hours to notice), which is itself worth flagging for Diego's #3. Report 2 is the smoking gun on business impact: hour 14 had just 1 order and hour 15 has NO row at all (zero orders) on Black Friday, versus 7-11 orders in every neighboring hour that day - a near-total order freeze for roughly 2 hours on the year's biggest shopping day. Report 3 shows Black Friday 2025 total revenue (€40,166) came in 26% below Black Friday 2024 (€54,186) despite the business having grown since then, which is directionally consistent with real lost sales, though other factors could contribute and I haven't isolated the outage's exact share of that gap. This is my last night before the memo, so I'm using the 3 slots to: (1) precisely size the lost-order/revenue window by comparing the same 14:00-16:00 UTC slot across Nov 28 against two baseline Fridays and Black Friday 2024, so I can give Diego an actual number rather than an impression; (2) check whether checkout/storefront ever showed another severe (>=50% unhealthy) outage anywhere else in the Nov-Dec window we have, to answer whether this was a true one-off or a recurring latent risk; (3) check payment attempts during the same window, since a checkout-path outage should show up there too and would confirm this was a full checkout failure, not just a reminder-email glitch.

    Orders in the 14:00-16:00 UTC Window: Nov 28 vs Baseline Days
    Question
    How many orders (and how much revenue) were actually lost in the 14:00-16:00 UTC outage window on Black Friday, compared to the same window on comparable days?
    One row per
    reference date
    Columns
    • date
    • label
    • orders_14_to_16_utc
    • revenue_eur_14_to_16_utc
    • total_orders_full_day
    • pct_of_day_in_window

  4. Viktor writes the SQL

    The engineer · 1 dry run on the empty schema, then the submission

    Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)
    SELECT
  d.ref_date AS date,
  d.label AS label,
  COALESCE(w.orders_cnt, 0) AS orders_14_to_16_utc,
  ROUND(COALESCE(w.revenue_eur, 0), 0) AS revenue_eur_14_to_16_utc,
  COALESCE(f.total_orders, 0) AS total_orders_full_day,
  ROUND(COALESCE(w.orders_cnt, 0) * 100.0 / NULLIF(f.total_orders, 0), 1) AS pct_of_day_in_window
FROM (VALUES
  (DATE '2025-11-21', 'Prior Friday'),
  (DATE '2025-11-28', 'Black Friday 2025'),
  (DATE '2025-12-05', 'Following Friday'),
  (DATE '2024-11-29', 'Black Friday 2024')
) AS d(ref_date, label)
LEFT JOIN (
  SELECT
    CAST(o.placed_at AS date) AS d,
    COUNT(*) AS orders_cnt,
    SUM(o.grand_total / er.rate) AS revenue_eur
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS date)
  WHERE CAST(o.placed_at AS date) IN (DATE '2025-11-21', DATE '2025-11-28', DATE '2025-12-05', DATE '2024-11-29')
    AND CAST(o.placed_at AS time) >= TIME '14:00:00'
    AND CAST(o.placed_at AS time) < TIME '16:00:00'
  GROUP BY CAST(o.placed_at AS date)
) AS w
  ON w.d = d.ref_date
LEFT JOIN (
  SELECT
    CAST(o.placed_at AS date) AS d,
    COUNT(*) AS total_orders
  FROM orders o
  WHERE CAST(o.placed_at AS date) IN (DATE '2025-11-21', DATE '2025-11-28', DATE '2025-12-05', DATE '2024-11-29')
  GROUP BY CAST(o.placed_at AS date)
) AS f
  ON f.d = d.ref_date
ORDER BY d.ref_date ASC

    “Query compiles cleanly with the expected columns. Submitting.”

    Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

    “Submitted. Summary of what the report does: - Grain: one row per reference date (the 4 dates requested). - orders_14_to_16_utc / revenue_eur_14_to_16_utc: counts and EUR-converted revenue (via exchange_rates, EUR→order currency, rate as of that calendar date) for orders placed strictly within 14:00–16:00 UTC. - total_orders_full_day: all orders placed that UTC day, for context. - pct_of_day_in_window: share of the day's orders that fell in the 2-hour window, rounded to 1 decimal. - Dates with zero orders in a bucket show as 0 via COALESCE, and percentage safely returns null if a day had zero total orders (NULLIF). Note included in the submission: the query aggregates across all merchants since no tenant filter was specified — flagged in case the VP wants it scoped to specific tenants.”

  5. The report comes back overnight

    As Lucas Moreau saw it, from the small dataset the agents worked on

    date       | label             | orders_14_to_16_utc | revenue_eur_14_to_16_utc | total_orders_full_day | pct_of_day_in_window
-----------+-------------------+---------------------+--------------------------+-----------------------+---------------------
2024-11-29 | Black Friday 2024 |                  22 |                   4012.0 |                   227 |                  9.7
2025-11-21 | Prior Friday      |                   6 |                    699.0 |                    82 |                  7.3
2025-11-28 | Black Friday 2025 |                   1 |                    103.0 |                   201 |                  0.5
2025-12-05 | Following Friday  |                   6 |                   1574.0 |                   119 |                  5.0
(4 rows)

  6. What came of it

    The memo and the queries that followed; links found by title, so inferred

    It builds on:

  7. It becomes benchmark query q344

    Changed only where the benchmark needs the same result on every system

    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results
    The benchmark text, against the submitted SQL
    SELECT  d.ref_date AS date,  d.label AS label,  COALESCE(w.orders_cnt, 0) AS orders_14_to_16_utc,  ROUND(COALESCE(w.revenue_eur, 0), 0) AS revenue_eur_14_to_16_utc,  COALESCE(w.revenue_eur, 0) AS revenue_eur_14_to_16_utc,  COALESCE(f.total_orders, 0) AS total_orders_full_day,  ROUND(COALESCE(w.orders_cnt, 0) * 100.0 / NULLIF(f.total_orders, 0), 1) AS pct_of_day_in_window  COALESCE(w.orders_cnt, 0) * 100.0 / NULLIF(f.total_orders, 0) AS pct_of_day_in_windowFROM (VALUES  (DATE '2025-11-21', 'Prior Friday'),  (DATE '2025-11-28', 'Black Friday 2025'),  (DATE '2025-12-05', 'Following Friday'),  (DATE '2024-11-29', 'Black Friday 2024')) AS d(ref_date, label)LEFT JOIN (  SELECT    CAST(o.placed_at AS date) AS d,    COUNT(*) AS orders_cnt,    SUM(o.grand_total / er.rate) AS revenue_eur    SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS revenue_eur  FROM orders o  JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = o.currency_code   AND er.as_of = CAST(o.placed_at AS date)  WHERE CAST(o.placed_at AS date) IN (DATE '2025-11-21', DATE '2025-11-28', DATE '2025-12-05', DATE '2024-11-29')    AND CAST(o.placed_at AS time) >= TIME '14:00:00'    AND CAST(o.placed_at AS time) < TIME '16:00:00'  GROUP BY CAST(o.placed_at AS date)) AS w  ON w.d = d.ref_dateLEFT JOIN (  SELECT    CAST(o.placed_at AS date) AS d,    COUNT(*) AS total_orders  FROM orders o  WHERE CAST(o.placed_at AS date) IN (DATE '2025-11-21', DATE '2025-11-28', DATE '2025-12-05', DATE '2024-11-29')  GROUP BY CAST(o.placed_at AS date)) AS f  ON f.d = d.ref_dateORDER BY d.ref_date ASCORDER BY d.ref_date ASC NULLS LAST

    As each system received it; highlighted, the parts spelled for that system

    SELECT
  d.ref_date AS date,
  d.label AS label,
  COALESCE(w.orders_cnt, 0) AS orders_14_to_16_utc,
  COALESCE(w.revenue_eur, 0) AS revenue_eur_14_to_16_utc,
  COALESCE(f.total_orders, 0) AS total_orders_full_day,
  COALESCE(w.orders_cnt, 0) * 100.0 / NULLIF(f.total_orders, 0) AS pct_of_day_in_window
FROM (VALUES
  (DATE '2025-11-21', 'Prior Friday'),
  (DATE '2025-11-28', 'Black Friday 2025'),
  (DATE '2025-12-05', 'Following Friday'),
  (DATE '2024-11-29', 'Black Friday 2024')
) AS d(ref_date, label)
LEFT JOIN (
  SELECT
    CAST(o.placed_at AS date) AS d,
    COUNT(*) AS orders_cnt,
    SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS revenue_eur
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS date)
  WHERE CAST(o.placed_at AS date) IN (DATE '2025-11-21', DATE '2025-11-28', DATE '2025-12-05', DATE '2024-11-29')
    AND CAST(o.placed_at AS time) >= TIME '14:00:00'
    AND CAST(o.placed_at AS time) < TIME '16:00:00'
  GROUP BY CAST(o.placed_at AS date)
) AS w
  ON w.d = d.ref_date
LEFT JOIN (
  SELECT
    CAST(o.placed_at AS date) AS d,
    COUNT(*) AS total_orders
  FROM orders o
  WHERE CAST(o.placed_at AS date) IN (DATE '2025-11-21', DATE '2025-11-28', DATE '2025-12-05', DATE '2024-11-29')
  GROUP BY CAST(o.placed_at AS date)
) AS f
  ON f.d = d.ref_date
ORDER BY d.ref_date ASC NULLS LAST
    10 ms100 msHyper14.2 msDuckDB38.9 msGizmoSQL35.4 msclickhouse_brew92.8 msFirebolterrorDataFusionwrong resultClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 25, Column 29: syntax error, unexpected TIME\n AND CAST(o.placed_at AS time) >= TIME '14:00:00'\n ^", "location": { "failing_line": 25, "start_offset": 29 } } ], "query": { "query_id": "8158bbe3-3c7e-4bea-9375-45e85fe86240", "query_label": null, "request_id": "67
    • DataFusionrow 1: col 3: "15439" vs "0"; col 4: "3097223.518652" vs "0.000000"; col 6: "6.4665426886476343653665728454630746548720" vs "0.0"
    • ClickHouseCode: 62. DB::Exception: Syntax error: failed at position 974 ('14:00:00') (line 25, col 43): '14:00:00'

    Scale factor 1000, c8a.24xlarge, after the last load. q344 on the start page, with every configuration.

  8. Everything, as recorded

    The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

    Viktor's whole session, 7 turns
    • The request, as Viktor received it
      Report request from Lucas Moreau (analyst for the VP Platform Engineering):

Title: Orders in the 14:00-16:00 UTC Window: Nov 28 vs Baseline Days
Question: How many orders (and how much revenue) were actually lost in the 14:00-16:00 UTC outage window on Black Friday, compared to the same window on comparable days?
Grain: one row per reference date
Columns:
- date
- label
- orders_14_to_16_utc
- revenue_eur_14_to_16_utc
- total_orders_full_day
- pct_of_day_in_window
Details: Table orders, placed_at. For each of 2025-11-21 ('Prior Friday'), 2025-11-28 ('Black Friday 2025'), 2025-12-05 ('Following Friday'), 2024-11-29 ('Black Friday 2024'): orders_14_to_16_utc = count of orders with placed_at >= date 14:00:00 and < date 16:00:00 (UTC); revenue_eur_14_to_16_utc = sum of grand_total for those orders converted to EUR via exchange_rates (base_code='EUR', quote_code=orders.currency_code, as_of=that date), rounded to whole number. total_orders_full_day = count of all orders placed that full UTC day (for context). pct_of_day_in_window = orders_14_to_16_utc / total_orders_full_day * 100, rounded 1dp. Order by date ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Viktor calls dry_run
      SELECT
  d.ref_date AS date,
  d.label AS label,
  COALESCE(w.orders_cnt, 0) AS orders_14_to_16_utc,
  ROUND(COALESCE(w.revenue_eur, 0), 0) AS revenue_eur_14_to_16_utc,
  COALESCE(f.total_orders, 0) AS total_orders_full_day,
  ROUND(COALESCE(w.orders_cnt, 0) * 100.0 / NULLIF(f.total_orders, 0), 1) AS pct_of_day_in_window
FROM (VALUES
  (DATE '2025-11-21', 'Prior Friday'),
  (DATE '2025-11-28', 'Black Friday 2025'),
  (DATE '2025-12-05', 'Following Friday'),
  (DATE '2024-11-29', 'Black Friday 2024')
) AS d(ref_date, label)
LEFT JOIN (
  SELECT
    CAST(o.placed_at AS date) AS d,
    COUNT(*) AS orders_cnt,
    SUM(o.grand_total / er.rate) AS revenue_eur
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS date)
  WHERE CAST(o.placed_at AS date) IN (DATE '2025-11-21', DATE '2025-11-28', DATE '2025-12-05', DATE '2024-11-29')
    AND CAST(o.placed_at AS time) >= TIME '14:00:00'
    AND CAST(o.placed_at AS time) < TIME '16:00:00'
  GROUP BY CAST(o.placed_at AS date)
) AS w
  ON w.d = d.ref_date
LEFT JOIN (
  SELECT
    CAST(o.placed_at AS date) AS d,
    COUNT(*) AS total_orders
  FROM orders o
  WHERE CAST(o.placed_at AS date) IN (DATE '2025-11-21', DATE '2025-11-28', DATE '2025-12-05', DATE '2024-11-29')
  GROUP BY CAST(o.placed_at AS date)
) AS f
  ON f.d = d.ref_date
ORDER BY d.ref_date ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: date, label, orders_14_to_16_utc, revenue_eur_14_to_16_utc, total_orders_full_day, pct_of_day_in_window
    • ViktorQuery compiles cleanly with the expected columns. Submitting.
    • Viktor calls submit
      SELECT
  d.ref_date AS date,
  d.label AS label,
  COALESCE(w.orders_cnt, 0) AS orders_14_to_16_utc,
  ROUND(COALESCE(w.revenue_eur, 0), 0) AS revenue_eur_14_to_16_utc,
  COALESCE(f.total_orders, 0) AS total_orders_full_day,
  ROUND(COALESCE(w.orders_cnt, 0) * 100.0 / NULLIF(f.total_orders, 0), 1) AS pct_of_day_in_window
FROM (VALUES
  (DATE '2025-11-21', 'Prior Friday'),
  (DATE '2025-11-28', 'Black Friday 2025'),
  (DATE '2025-12-05', 'Following Friday'),
  (DATE '2024-11-29', 'Black Friday 2024')
) AS d(ref_date, label)
LEFT JOIN (
  SELECT
    CAST(o.placed_at AS date) AS d,
    COUNT(*) AS orders_cnt,
    SUM(o.grand_total / er.rate) AS revenue_eur
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS date)
  WHERE CAST(o.placed_at AS date) IN (DATE '2025-11-21', DATE '2025-11-28', DATE '2025-12-05', DATE '2024-11-29')
    AND CAST(o.placed_at AS time) >= TIME '14:00:00'
    AND CAST(o.placed_at AS time) < TIME '16:00:00'
  GROUP BY CAST(o.placed_at AS date)
) AS w
  ON w.d = d.ref_date
LEFT JOIN (
  SELECT
    CAST(o.placed_at AS date) AS d,
    COUNT(*) AS total_orders
  FROM orders o
  WHERE CAST(o.placed_at AS date) IN (DATE '2025-11-21', DATE '2025-11-28', DATE '2025-12-05', DATE '2024-11-29')
  GROUP BY CAST(o.placed_at AS date)
) AS f
  ON f.d = d.ref_date
ORDER BY d.ref_date ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • ViktorSubmitted. Summary of what the report does: - **Grain:** one row per reference date (the 4 dates requested). - **orders_14_to_16_utc / revenue_eur_14_to_16_utc:** counts and EUR-converted revenue (via `exchange_rates`, EUR→order currency, rate as of that calendar date) for orders placed strictly within 14:00–16:00 UTC. - **total_orders_full_day:** all orders placed that UTC day, for context. - **pct_of_day_in_window:** share of the day's orders that fell in the 2-hour window, rounded to 1 decimal. - Dates with zero orders in a bucket show as 0 via `COALESCE`, and percentage safely returns null if a day had zero total orders (`NULLIF`). Note included in the submission: the query aggregates across all merchants since no tenant filter was specified — flagged in case the VP wants it scoped to specific tenants.

    Not shown here: 240 KB of context the agent was given, its instructions and the schema documentation; it is in the raw transcript.

    Viktor's transcript (264 KB) · Lucas Moreau's transcript (813 KB) · Diego Morales's transcript (264 KB) · the night's plan (4 KB) · the night's reports (5 KB) · the week's memo (4 KB)