DB Olympics

Queries · q343 · VP Platform Engineering · week 2, night 3

Black Friday 2025 Sales vs Surrounding Fridays and Black Friday 2024

“Despite the checkout outage, how did total Black Friday 2025 sales compare to the Fridays immediately before/after it and to Black Friday 2024 - was the day's performance meaningfully dented?”

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 SQLInesThinks in window functions: ranks, running totals, LAG/LEAD for comparisons, percent-of-total.

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 3, request 3 of 3

    Report 2 from last night changes the scale of this story substantially: at the 14:00 UTC hour check on Nov 28 2025, BOTH 'checkout' and 'storefront' components were 100% unhealthy (111/111 checks failing, latency pegged at a 10,000ms timeout ceiling) - a full, unambiguous outage, not the mild 2.44% weekly blip I reported last week (that number was just this severe hour diluted across a whole week of otherwise-healthy checks). And critically, Nov 28 2025 is Black Friday - so if checkout itself was down for part of the busiest shopping day of the year, that's a much bigger finding than '10 merchants' cart-reminder emails failed,' and it would affect every merchant trying to check out then, not just the 10 using carts.remind. I didn't request a 15:00 bucket so I can't see the second (15:40) failure's health-check status yet, and my hourly buckets are too coarse to know exactly when the outage started/ended. Report 3 (ticket subject search) came back empty - no evidence in ticket subjects that shoppers flagged cart/reminder problems before Nov 24, so I can't support the 'support was an early warning sign' theory from this angle; I'm treating that as a genuine (if limited - subjects only, not message bodies) negative result rather than spending more budget on it, since the checkout-outage/Black-Friday angle is now clearly the more important thread with one night left. Tonight's 3 slots: (1) pin down the exact start/end of the checkout outage at finer time resolution; (2) measure the actual order-volume impact hour-by-hour on Nov 28 itself; (3) put Nov 28 in context against surrounding Fridays and Black Friday 2024 so Diego has a real 'how much did this cost us' number for leadership.

    Black Friday 2025 Sales vs Surrounding Fridays and Black Friday 2024
    Question
    Despite the checkout outage, how did total Black Friday 2025 sales compare to the Fridays immediately before/after it and to Black Friday 2024 - was the day's performance meaningfully dented?
    One row per
    named reference day
    Columns
    • date
    • label
    • total_orders
    • total_revenue_eur

  4. Ines 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
  v.calendar_date AS date,
  v.label AS label,
  count(o.id) AS total_orders,
  round(sum(o.grand_total / COALESCE(er.rate, 1))) AS total_revenue_eur
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 v(calendar_date, label)
LEFT JOIN orders o
  ON date(o.placed_at) = v.calendar_date
LEFT JOIN exchange_rates er
  ON er.base_code = 'EUR'
  AND er.quote_code = o.currency_code
  AND er.as_of = v.calendar_date
  AND o.currency_code <> 'EUR'
GROUP BY v.calendar_date, v.label
ORDER BY v.calendar_date ASC

    “Query compiles and returns 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. The report pulls orders placed on each of the four reference UTC days, counts them, and sums grand_total converted to EUR using the matching day's exchange_rates row (EUR-denominated orders treated as rate 1 since an EUR→EUR identity row isn't guaranteed to exist in the table). Rows are ordered by date ascending as requested.”

  5. The report comes back overnight

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

    date       | label             | total_orders | total_revenue_eur
-----------+-------------------+--------------+------------------
2024-11-29 | Black Friday 2024 |          227 |           54186.0
2025-11-21 | Prior Friday      |           82 |           22302.0
2025-11-28 | Black Friday 2025 |          201 |           40166.0
2025-12-05 | Following Friday  |          119 |           15088.0
(4 rows)

  6. What came of it

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

    Later queries that build on this report:

    It builds on:

  7. It becomes benchmark query q343

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

    • spellingdate cast
    • 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  v.calendar_date AS date,  v.label AS label,  count(o.id) AS total_orders,  round(sum(o.grand_total / COALESCE(er.rate, 1))) AS total_revenue_eur  sum(CAST(o.grand_total / COALESCE(er.rate, 1) AS NUMERIC(18,6))) AS total_revenue_eurFROM (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 v(calendar_date, label)LEFT JOIN orders o  ON date(o.placed_at) = v.calendar_date  ON CAST(o.placed_at AS DATE) = v.calendar_dateLEFT JOIN exchange_rates er  ON er.base_code = 'EUR'  AND er.quote_code = o.currency_code  AND er.as_of = v.calendar_date  AND o.currency_code <> 'EUR'GROUP BY v.calendar_date, v.labelORDER BY v.calendar_date ASCORDER BY v.calendar_date ASC NULLS LAST

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

    SELECT
  v.calendar_date AS date,
  v.label AS label,
  count(o.id) AS total_orders,
  sum(CAST(o.grand_total / COALESCE(er.rate, 1) AS NUMERIC(18,6))) AS total_revenue_eur
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 v(calendar_date, label)
LEFT JOIN orders o
  ON CAST(o.placed_at AS DATE) = v.calendar_date
LEFT JOIN exchange_rates er
  ON er.base_code = 'EUR'
  AND er.quote_code = o.currency_code
  AND er.as_of = v.calendar_date
  AND o.currency_code <> 'EUR'
GROUP BY v.calendar_date, v.label
ORDER BY v.calendar_date ASC NULLS LAST
    10 ms100 ms1 s10 sHyper16.8 msDuckDB3.30 sGizmoSQL3.71 sclickhouse_brew81.9 msFirebolterrorDataFusion74.6 msClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 5, Column 26: Operations between decimals with different precision and scale is not supported at the moment. Explicitly cast one of the decimals to other decimal precision and scale." } ], "query": { "query_id": "b17b93fc-1664-4d22-901b-0a67118403b5", "query_labe
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing 'FUNCTION divide(__table3.grand_total :: 3, coalesce(__table4.rate, 1_UInt8) :: 7) -> divide(__table3.grand_total, coalesce(__table4.rate, 1_UInt8)) Nullable(Decimal(18, 4)) : 5'. (ARGUMENT_OUT_OF_BOUND)

    Scale factor 1000, c8a.24xlarge, after the last load. q343 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

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

Title: Black Friday 2025 Sales vs Surrounding Fridays and Black Friday 2024
Question: Despite the checkout outage, how did total Black Friday 2025 sales compare to the Fridays immediately before/after it and to Black Friday 2024 - was the day's performance meaningfully dented?
Grain: one row per named reference day
Columns:
- date
- label
- total_orders
- total_revenue_eur
Details: Table orders, filtered to placed_at on each of these four specific dates (full day, UTC): 2025-11-21 (label 'Prior Friday'), 2025-11-28 (label 'Black Friday 2025'), 2025-12-05 (label 'Following Friday'), 2024-11-29 (label 'Black Friday 2024'). total_revenue_eur = sum of grand_total converted to EUR via exchange_rates (base_code='EUR', quote_code=orders.currency_code, as_of=that date), rounded to whole number. Platform-wide, all tenants. Order by date ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Ines calls dry_run
      SELECT
  v.calendar_date AS date,
  v.label AS label,
  count(o.id) AS total_orders,
  round(sum(o.grand_total / COALESCE(er.rate, 1))) AS total_revenue_eur
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 v(calendar_date, label)
LEFT JOIN orders o
  ON date(o.placed_at) = v.calendar_date
LEFT JOIN exchange_rates er
  ON er.base_code = 'EUR'
  AND er.quote_code = o.currency_code
  AND er.as_of = v.calendar_date
  AND o.currency_code <> 'EUR'
GROUP BY v.calendar_date, v.label
ORDER BY v.calendar_date ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: date, label, total_orders, total_revenue_eur
    • InesQuery compiles and returns the expected columns. Submitting.
    • Ines calls submit
      SELECT
  v.calendar_date AS date,
  v.label AS label,
  count(o.id) AS total_orders,
  round(sum(o.grand_total / COALESCE(er.rate, 1))) AS total_revenue_eur
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 v(calendar_date, label)
LEFT JOIN orders o
  ON date(o.placed_at) = v.calendar_date
LEFT JOIN exchange_rates er
  ON er.base_code = 'EUR'
  AND er.quote_code = o.currency_code
  AND er.as_of = v.calendar_date
  AND o.currency_code <> 'EUR'
GROUP BY v.calendar_date, v.label
ORDER BY v.calendar_date ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • InesSubmitted. The report pulls orders placed on each of the four reference UTC days, counts them, and sums `grand_total` converted to EUR using the matching day's `exchange_rates` row (EUR-denominated orders treated as rate 1 since an EUR→EUR identity row isn't guaranteed to exist in the table). Rows are ordered by date ascending as requested.

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

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