DB Olympics

Queries · q054 · Head of Growth Marketing · week 1, night 2

Which merchants drove the January and July discount-rate spikes

“The platform-wide discount rate jumped from a ~35% baseline to 53-59% in January and July 2025 with no increase in average discount size — was that driven by a handful of merchants running sitewide promotions in those two months?”

Manager · asked the questionChloé MarchettiHead of Growth Marketing. Joined from a direct-to-consumer brand. Wants to sell merchants more of Tidewick's marketing tools.
Analyst · planned the reportSofie NilsenStoryteller: looks for the two or three findings that matter and digs until each is explained.
Engineer · wrote the SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

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

  1. Chloé Marchetti writes to Sofie Nilsen

    The manager, at the start of week 1

    Subject: What I need to get oriented this week

    Hi Sofie,

    Thrilled to be here — here's where my head's at. I know you've only got a handful of report slots a night, so I've ordered these by what I need most. Please push back if any of these are too broad or should split into more than one report.

    1. How are merchants actually acquiring shoppers, and through which channels? I want to understand where traffic and new customers are coming from across our merchant base — which channels show up most, and whether that's shifted recently. This is really just orientation for me: I don't yet know what "normal" looks like here.
    2. Are the coupons and promotions merchants run actually paying off? I'd like a sense of how much discounting is happening, how often codes get used, and whether promoted orders look healthy (not just cheaper). I'm trying to figure out if we should be telling merchants to promote more carefully — this is relevant for your product-pitch angle too.
    3. What does our email/campaign activity look like, and is anyone opening or clicking? I want a baseline read on send volume and engagement so I know whether campaigns are a strength or a weak spot for merchants on the platform.
    4. Is anyone actually using loyalty and referrals, and does it seem to matter? I keep hearing these are "nice to have" features. I want to know whether merchants who use them have any different shopper behavior, so I know whether to invest in pushing adoption.
    5. (If there's room this week) Do we run experiments/A-B tests at all, and does anyone act on the results? Lower priority — just curious whether this is a muscle we have or one I need to build from scratch.

    What I need this for: I'm meeting the leadership team Friday and want a grounded, numbers-based view of growth marketing's current state before I propose anything. No need for polish — I just want to know what's really happening so I don't propose things that are already working, or miss things that are quietly broken.

    Thanks for taking this on — let me know if you want me to cut this list down.

    Chloé

  2. Sofie Nilsen plans the night

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

    Two things from last night need explaining before I trust the headline numbers, plus one gap Chloé explicitly asked about that I haven't covered yet. First, 'direct/none' is 57% of 2025 sessions and growing 26% YoY — before I call that normal I want to see whether it's a tracking artifact concentrated in a few big merchants (consistent with the 'handful of large merchants' pattern) or spread evenly. Second, the discount-rate report showed two anomalous months (Jan 53%, Jul 59%) versus a ~35% baseline the rest of the year, with discount size per order flat — meaning more orders got a code, not bigger discounts. I want to know if that's a few tenants running sitewide promos. Third, Chloé specifically asked whether promoted orders 'look healthy, not just cheaper' — I haven't produced that comparison yet, so it goes in tonight regardless of the anomalies.

    Which merchants drove the January and July discount-rate spikes
    Question
    The platform-wide discount rate jumped from a ~35% baseline to 53-59% in January and July 2025 with no increase in average discount size — was that driven by a handful of merchants running sitewide promotions in those two months?
    One row per
    tenant
    Columns
    • tenant_name - tenants.name
    • industry - tenants.industry
    • orders_jan_jul_2025 - count of that tenant's orders placed_at in Jan or Jul 2025
    • discount_rate_jan_jul_pct - share of those orders with discount_total > 0, *100, rounded to 1 decimal
    • orders_other_months_2025 - count of that tenant's orders placed_at in the other 10 months of 2025
    • discount_rate_other_months_pct - share of those orders with discount_total > 0, *100, rounded to 1 decimal
    • delta_pct_points - discount_rate_jan_jul_pct minus discount_rate_other_months_pct, rounded to 1 decimal

  3. Bea 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
  t0.name AS tenant_name,
  t0.industry AS industry,
  COALESCE(t1.orders_jan_jul, 0) AS orders_jan_jul_2025,
  ROUND(COALESCE(t1.discount_orders, 0) * 100.0 / NULLIF(t1.orders_jan_jul, 0), 1) AS discount_rate_jan_jul_pct,
  COALESCE(t2.orders_other, 0) AS orders_other_months_2025,
  ROUND(COALESCE(t2.discount_orders, 0) * 100.0 / NULLIF(t2.orders_other, 0), 1) AS discount_rate_other_months_pct,
  ROUND(COALESCE(t1.discount_orders, 0) * 100.0 / NULLIF(t1.orders_jan_jul, 0), 1)
    - ROUND(COALESCE(t2.discount_orders, 0) * 100.0 / NULLIF(t2.orders_other, 0), 1) AS delta_pct_points
FROM tenants t0
JOIN (
  SELECT
    o1.tenant_id AS tenant_id,
    COUNT(*) AS orders_jan_jul,
    SUM(CASE WHEN o1.discount_total > 0 THEN 1 ELSE 0 END) AS discount_orders
  FROM orders o1
  WHERE o1.placed_at IS NOT NULL
    AND EXTRACT(YEAR FROM o1.placed_at) = 2025
    AND EXTRACT(MONTH FROM o1.placed_at) IN (1, 7)
  GROUP BY o1.tenant_id
) t1 ON t1.tenant_id = t0.id
LEFT JOIN (
  SELECT
    o2.tenant_id AS tenant_id,
    COUNT(*) AS orders_other,
    SUM(CASE WHEN o2.discount_total > 0 THEN 1 ELSE 0 END) AS discount_orders
  FROM orders o2
  WHERE o2.placed_at IS NOT NULL
    AND EXTRACT(YEAR FROM o2.placed_at) = 2025
    AND EXTRACT(MONTH FROM o2.placed_at) NOT IN (1, 7)
  GROUP BY o2.tenant_id
) t2 ON t2.tenant_id = t0.id
WHERE t1.orders_jan_jul >= 20
ORDER BY delta_pct_points DESC, orders_jan_jul_2025 DESC
LIMIT 15

    “Looks good — 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 returns the top 15 tenants by discount-rate delta between the Jan/Jul 2025 bucket and the other 10 months of 2025, restricted to tenants with ≥20 orders in the Jan/Jul bucket, as requested.”

  4. The report comes back overnight

    As Sofie Nilsen saw it, from the small dataset the agents worked on

    tenant_name            | industry                  | orders_jan_jul_2025 | discount_rate_jan_jul_pct | orders_other_months_2025 | discount_rate_other_months_pct |   delta_pct_points
-----------------------+---------------------------+---------------------+---------------------------+--------------------------+--------------------------------+-------------------
Marrowby & Vane        | Apparel & Footwear        |                 313 |                      72.5 |                     1445 |                            8.2 |               64.3
Cinder Peak Sports Co. | Sports & Outdoor          |                  67 |                      70.1 |                      271 |                           17.3 |               52.8
Ridgeback Gear         | Sports & Outdoor          |                  23 |                      60.9 |                       85 |                           12.9 |               48.0
Nordic Bloom Cosmetics | Beauty                    |                  20 |                      55.0 |                      131 |                           16.0 |               39.0
Sourdough Club         | Food & Coffee             |                  58 |                      43.1 |                      262 |                            6.9 |               36.2
Kayak Corner           | Sports & Outdoor          |                  21 |                      42.9 |                       88 |                            6.8 |               36.1
Linden & Loom          | Home & Living             |                  58 |                      48.3 |                      205 |                           13.7 | 34.599999999999994
Wohnglück              | Home & Living             |                  26 |                      46.2 |                      125 |                           14.4 | 31.800000000000004
Ampfeld Elektronik     | Consumer Electronics      |                  79 |                      50.6 |                      370 |                           20.8 |               29.8
Rue Lafayette Studio   | Apparel & Footwear        |                  37 |                      40.5 |                      150 |                           12.0 |               28.5
Cragsley Outfitters    | Apparel & Footwear        |                 556 |                      34.0 |                     3079 |                            7.7 |               26.3
Brennquell Kaffee      | Food & Coffee             |                 705 |                      82.6 |                     3490 |                           59.3 | 23.299999999999997
Castell Fasteners      | Industrial Supplies (B2B) |                  22 |                      95.5 |                      137 |                           77.4 | 18.099999999999994
Oakhurst Tailoring     | Apparel & Footwear        |                  24 |                      37.5 |                      141 |                           19.9 |               17.6
OfficeHub Nordic       | Office Supplies (B2B)     |                  24 |                      83.3 |                      146 |                           65.8 |               17.5
(15 rows)

  5. 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:

  6. It becomes benchmark query q054

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

    • spellingfetch first
    • determinismtie-breaker: one row per tenant
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (3×)
    • placeholderfetch first
    The benchmark text, against the submitted SQL
    SELECT  t0.name AS tenant_name,  t0.industry AS industry,  COALESCE(t1.orders_jan_jul, 0) AS orders_jan_jul_2025,  ROUND(COALESCE(t1.discount_orders, 0) * 100.0 / NULLIF(t1.orders_jan_jul, 0), 1) AS discount_rate_jan_jul_pct,  COALESCE(t1.discount_orders, 0) * 100.0 / NULLIF(t1.orders_jan_jul, 0) AS discount_rate_jan_jul_pct,  COALESCE(t2.orders_other, 0) AS orders_other_months_2025,  ROUND(COALESCE(t2.discount_orders, 0) * 100.0 / NULLIF(t2.orders_other, 0), 1) AS discount_rate_other_months_pct,  ROUND(COALESCE(t1.discount_orders, 0) * 100.0 / NULLIF(t1.orders_jan_jul, 0), 1)    - ROUND(COALESCE(t2.discount_orders, 0) * 100.0 / NULLIF(t2.orders_other, 0), 1) AS delta_pct_points  COALESCE(t2.discount_orders, 0) * 100.0 / NULLIF(t2.orders_other, 0) AS discount_rate_other_months_pct,  (COALESCE(t1.discount_orders, 0) * 100.0 / NULLIF(t1.orders_jan_jul, 0))    - (COALESCE(t2.discount_orders, 0) * 100.0 / NULLIF(t2.orders_other, 0)) AS delta_pct_pointsFROM tenants t0JOIN (  SELECT    o1.tenant_id AS tenant_id,    COUNT(*) AS orders_jan_jul,    SUM(CASE WHEN o1.discount_total > 0 THEN 1 ELSE 0 END) AS discount_orders  FROM orders o1  WHERE o1.placed_at IS NOT NULL    AND EXTRACT(YEAR FROM o1.placed_at) = 2025    AND EXTRACT(MONTH FROM o1.placed_at) IN (1, 7)  GROUP BY o1.tenant_id) t1 ON t1.tenant_id = t0.idLEFT JOIN (  SELECT    o2.tenant_id AS tenant_id,    COUNT(*) AS orders_other,    SUM(CASE WHEN o2.discount_total > 0 THEN 1 ELSE 0 END) AS discount_orders  FROM orders o2  WHERE o2.placed_at IS NOT NULL    AND EXTRACT(YEAR FROM o2.placed_at) = 2025    AND EXTRACT(MONTH FROM o2.placed_at) NOT IN (1, 7)  GROUP BY o2.tenant_id) t2 ON t2.tenant_id = t0.idWHERE t1.orders_jan_jul >= 20ORDER BY delta_pct_points DESC, orders_jan_jul_2025 DESCLIMIT 15ORDER BY delta_pct_points DESC NULLS FIRST, orders_jan_jul_2025 DESC NULLS FIRST, t0.id NULLS LAST{{fetch_first(15)}}

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

    SELECT
  t0.name AS tenant_name,
  t0.industry AS industry,
  COALESCE(t1.orders_jan_jul, 0) AS orders_jan_jul_2025,
  COALESCE(t1.discount_orders, 0) * 100.0 / NULLIF(t1.orders_jan_jul, 0) AS discount_rate_jan_jul_pct,
  COALESCE(t2.orders_other, 0) AS orders_other_months_2025,
  COALESCE(t2.discount_orders, 0) * 100.0 / NULLIF(t2.orders_other, 0) AS discount_rate_other_months_pct,
  (COALESCE(t1.discount_orders, 0) * 100.0 / NULLIF(t1.orders_jan_jul, 0))
    - (COALESCE(t2.discount_orders, 0) * 100.0 / NULLIF(t2.orders_other, 0)) AS delta_pct_points
FROM tenants t0
JOIN (
  SELECT
    o1.tenant_id AS tenant_id,
    COUNT(*) AS orders_jan_jul,
    SUM(CASE WHEN o1.discount_total > 0 THEN 1 ELSE 0 END) AS discount_orders
  FROM orders o1
  WHERE o1.placed_at IS NOT NULL
    AND EXTRACT(YEAR FROM o1.placed_at) = 2025
    AND EXTRACT(MONTH FROM o1.placed_at) IN (1, 7)
  GROUP BY o1.tenant_id
) t1 ON t1.tenant_id = t0.id
LEFT JOIN (
  SELECT
    o2.tenant_id AS tenant_id,
    COUNT(*) AS orders_other,
    SUM(CASE WHEN o2.discount_total > 0 THEN 1 ELSE 0 END) AS discount_orders
  FROM orders o2
  WHERE o2.placed_at IS NOT NULL
    AND EXTRACT(YEAR FROM o2.placed_at) = 2025
    AND EXTRACT(MONTH FROM o2.placed_at) NOT IN (1, 7)
  GROUP BY o2.tenant_id
) t2 ON t2.tenant_id = t0.id
WHERE t1.orders_jan_jul >= 20
ORDER BY delta_pct_points DESC NULLS FIRST, orders_jan_jul_2025 DESC NULLS FIRST, t0.id NULLS LAST
FETCH FIRST 15 ROWS ONLY
    10 ms100 ms1 sHyper13.6 msDuckDB21.4 msGizmoSQL25.0 msclickhouse_brew369 msFirebolt79.8 msDataFusion116 msClickHouse76.4 ms

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

  7. 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

    Bea's whole session, 7 turns
    • The request, as Bea received it
      Report request from Sofie Nilsen (analyst for the Head of Growth Marketing):

Title: Which merchants drove the January and July discount-rate spikes
Question: The platform-wide discount rate jumped from a ~35% baseline to 53-59% in January and July 2025 with no increase in average discount size — was that driven by a handful of merchants running sitewide promotions in those two months?
Grain: one row per tenant
Columns:
- tenant_name - tenants.name
- industry - tenants.industry
- orders_jan_jul_2025 - count of that tenant's orders placed_at in Jan or Jul 2025
- discount_rate_jan_jul_pct - share of those orders with discount_total > 0, *100, rounded to 1 decimal
- orders_other_months_2025 - count of that tenant's orders placed_at in the other 10 months of 2025
- discount_rate_other_months_pct - share of those orders with discount_total > 0, *100, rounded to 1 decimal
- delta_pct_points - discount_rate_jan_jul_pct minus discount_rate_other_months_pct, rounded to 1 decimal
Details: Source: orders table, all tenants, placed_at in 2025. Split each tenant's orders into two buckets by month(placed_at) in (1,7) vs not, compute discount rate (discount_total > 0) in each bucket. Only include tenants with at least 20 orders in the Jan/Jul bucket (to avoid noise from tiny samples) — note the excluded count in a comment if easy, otherwise just apply the filter. Order by delta_pct_points descending, tie-break by orders_jan_jul_2025 descending, limit to top 15 tenants.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      SELECT
  t0.name AS tenant_name,
  t0.industry AS industry,
  COALESCE(t1.orders_jan_jul, 0) AS orders_jan_jul_2025,
  ROUND(COALESCE(t1.discount_orders, 0) * 100.0 / NULLIF(t1.orders_jan_jul, 0), 1) AS discount_rate_jan_jul_pct,
  COALESCE(t2.orders_other, 0) AS orders_other_months_2025,
  ROUND(COALESCE(t2.discount_orders, 0) * 100.0 / NULLIF(t2.orders_other, 0), 1) AS discount_rate_other_months_pct,
  ROUND(COALESCE(t1.discount_orders, 0) * 100.0 / NULLIF(t1.orders_jan_jul, 0), 1)
    - ROUND(COALESCE(t2.discount_orders, 0) * 100.0 / NULLIF(t2.orders_other, 0), 1) AS delta_pct_points
FROM tenants t0
JOIN (
  SELECT
    o1.tenant_id AS tenant_id,
    COUNT(*) AS orders_jan_jul,
    SUM(CASE WHEN o1.discount_total > 0 THEN 1 ELSE 0 END) AS discount_orders
  FROM orders o1
  WHERE o1.placed_at IS NOT NULL
    AND EXTRACT(YEAR FROM o1.placed_at) = 2025
    AND EXTRACT(MONTH FROM o1.placed_at) IN (1, 7)
  GROUP BY o1.tenant_id
) t1 ON t1.tenant_id = t0.id
LEFT JOIN (
  SELECT
    o2.tenant_id AS tenant_id,
    COUNT(*) AS orders_other,
    SUM(CASE WHEN o2.discount_total > 0 THEN 1 ELSE 0 END) AS discount_orders
  FROM orders o2
  WHERE o2.placed_at IS NOT NULL
    AND EXTRACT(YEAR FROM o2.placed_at) = 2025
    AND EXTRACT(MONTH FROM o2.placed_at) NOT IN (1, 7)
  GROUP BY o2.tenant_id
) t2 ON t2.tenant_id = t0.id
WHERE t1.orders_jan_jul >= 20
ORDER BY delta_pct_points DESC, orders_jan_jul_2025 DESC
LIMIT 15
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tenant_name, industry, orders_jan_jul_2025, discount_rate_jan_jul_pct, orders_other_months_2025, discount_rate_other_months_pct, delta_pct_points
    • BeaLooks good — submitting.
    • Bea calls submit
      SELECT
  t0.name AS tenant_name,
  t0.industry AS industry,
  COALESCE(t1.orders_jan_jul, 0) AS orders_jan_jul_2025,
  ROUND(COALESCE(t1.discount_orders, 0) * 100.0 / NULLIF(t1.orders_jan_jul, 0), 1) AS discount_rate_jan_jul_pct,
  COALESCE(t2.orders_other, 0) AS orders_other_months_2025,
  ROUND(COALESCE(t2.discount_orders, 0) * 100.0 / NULLIF(t2.orders_other, 0), 1) AS discount_rate_other_months_pct,
  ROUND(COALESCE(t1.discount_orders, 0) * 100.0 / NULLIF(t1.orders_jan_jul, 0), 1)
    - ROUND(COALESCE(t2.discount_orders, 0) * 100.0 / NULLIF(t2.orders_other, 0), 1) AS delta_pct_points
FROM tenants t0
JOIN (
  SELECT
    o1.tenant_id AS tenant_id,
    COUNT(*) AS orders_jan_jul,
    SUM(CASE WHEN o1.discount_total > 0 THEN 1 ELSE 0 END) AS discount_orders
  FROM orders o1
  WHERE o1.placed_at IS NOT NULL
    AND EXTRACT(YEAR FROM o1.placed_at) = 2025
    AND EXTRACT(MONTH FROM o1.placed_at) IN (1, 7)
  GROUP BY o1.tenant_id
) t1 ON t1.tenant_id = t0.id
LEFT JOIN (
  SELECT
    o2.tenant_id AS tenant_id,
    COUNT(*) AS orders_other,
    SUM(CASE WHEN o2.discount_total > 0 THEN 1 ELSE 0 END) AS discount_orders
  FROM orders o2
  WHERE o2.placed_at IS NOT NULL
    AND EXTRACT(YEAR FROM o2.placed_at) = 2025
    AND EXTRACT(MONTH FROM o2.placed_at) NOT IN (1, 7)
  GROUP BY o2.tenant_id
) t2 ON t2.tenant_id = t0.id
WHERE t1.orders_jan_jul >= 20
ORDER BY delta_pct_points DESC, orders_jan_jul_2025 DESC
LIMIT 15
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. The report returns the top 15 tenants by discount-rate delta between the Jan/Jul 2025 bucket and the other 10 months of 2025, restricted to tenants with ≥20 orders in the Jan/Jul bucket, 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.

    Bea's transcript (264 KB) · Sofie Nilsen's transcript (793 KB) · Chloé Marchetti's transcript (263 KB) · the night's plan (5 KB) · the night's reports (7 KB) · the week's memo (3 KB)