DB Olympics

Queries · q268 · Head of Merchant Success · week 2, night 1

Castell Fasteners traffic drop: seasonal pattern or anomaly across B2B wholesale merchants

“Is Castell Fasteners Q4 web traffic decline normal seasonal behavior shared by other B2B wholesale merchants, or a Castell-specific anomaly?”

Manager · asked the questionSamuel WhitcombeHead of Merchant Success. New in the role, owns the relationship with the largest merchants and quarterly business reviews with them in January.
Analyst · planned the reportArjun PatelSegmenter: breaks totals down by merchant size, vertical, country and channel.
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 Head of Merchant Success's desk

    Week 2; shared with Chloé Marchetti, Head of Growth Marketing and Daniel Achterberg, VP Customer Experience

    Churn has become a board topic, for merchants and for their shoppers. A cross-functional task force wants to understand which experiences precede leaving, and which early signals would let us act before it happens.

    One of the 35 events we wrote for the role play; the manager heard of it at the start of week 2 and had to cover it in that week's note. How events arise · All the events

  2. Samuel Whitcombe writes to Arjun Patel

    The manager, at the start of week 2

    Subject: Next week's priorities — QBR follow-ups + the churn task force

    Hi Arjun,

    Really useful memo — thank you. A few things jumped out that I want to understand better, plus a new cross-functional ask that's going to need a slot or two this week.

    What surprised me / needs explaining first:

    • Castell Fasteners is a contradiction — our #4 merchant, highly sticky customer base (88.6% repeat), but the weakest YoY growth of the top tier and a steep traffic drop. I want to know whether that traffic drop is a real warning sign or just how B2B industrial buying seasonally looks, before I raise it with them in their QBR.
    • Good catch on the refund-rate math — please don't let anything with the >100% problem anywhere near a client-facing page until it's fixed.

    My questions for this week:

    1. Is Castell's traffic decline a real risk or seasonal noise? I want a clear enough answer that I either flag it to them directly in the QBR or leave it alone.
    2. How are our top ~15 merchants' shoppers being supported, and does it show up in how those merchants feel about us? This was cut last week — please pick it back up.
    3. For the task force below: which merchants have actually left the platform (or are clearly heading that way), and what did their last few months look like beforehand — their sales trend, their shoppers' buying behavior, how much support friction they had? I'm less interested in a single root cause and more in whatever pattern shows up consistently across the merchants who left, since that's what would let us catch the next one early.

    New context — the churn task force: Churn is now a board topic, both merchant churn and end-shopper churn, and I'm leading this jointly with Chloé Marchetti and Daniel Achterberg. The deliverable the three of us owe the board is a shortlist of early-warning signals — things we can observe weeks or months before a merchant (or a merchant's shoppers) leaves — and a recommendation on which of those signals is reliable enough to act on. It feeds directly into two decisions: whether Merchant Success should get a proactive outreach trigger for at-risk accounts, and whether Growth Marketing and CX get an equivalent retention trigger for end-shoppers. So anything you can tell me this week about what preceded past departures — on the merchant side especially, since that's my piece — is directly useful input, even if it's partial. I'll compare notes with Chloé's and Daniel's analysts before we draft anything for the board.

    Given the slot limits, prioritize in this order: Castell explanation, then the task-force churn pattern, then support/CSAT if a slot remains.

    Thanks, Samuel

    A note from the VP Customer Experience, Daniel Achterberg

    Subject: CX signals for the churn task force

    Arjun,

    A few things I'd want the task force to look at from the shopper/support side:

    1. Does ticket experience precede merchant churn? For merchants who later left, did their shoppers see slower first responses, more reopened tickets, or more escalations in the months before? I'd want to know how far out a warning shows up.
    2. Do returns/refunds friction and churn move together? Where refunds get delayed, disputed, or partially handled, do we see both unhappy shoppers (low ratings/CSAT) and merchants churning shortly after? I'm curious whether refund delay is a leading indicator or just a symptom.
    3. Are complaint themes shifting before a merchant leaves? Is there a recognizable pattern in what shoppers are complaining about (e.g. shipping, product quality, billing) in the weeks before a merchant churns, versus a stable merchant?

    If we can pin down even one or two of these as reliable early signals, it changes what Samuel's team and I should be monitoring week to week rather than finding out after the fact.

    Daniel

    A note from the Head of Growth Marketing, Chloé Marchetti

    Subject: Churn task force — what I'd like us to look at from Growth

    Hi Arjun,

    Glad this is getting cross-functional attention. From the marketing side, here's what I think matters:

    1. Do shoppers who stop engaging with our marketing touches (emails, campaigns, loyalty) churn sooner than the ones who stay engaged? If disengagement shows up weeks before a churn, that's an early signal worth acting on.
    2. Does a merchant's own marketing activity level relate to whether their shoppers stick around? I want to know if merchants who run fewer campaigns, promotions, or loyalty programs are the ones seeing higher shopper churn — that would tell us whether under-investment in marketing tools is part of the story.
    3. Is there a pattern in how promo- or discount-driven shoppers behave right before leaving — e.g. do they go quiet after a coupon or loyalty redemption, or does spend drop off without any further marketing response from us?

    Happy to jump on a call if useful — mainly want to make sure whatever "early warning" signals come out of this include the marketing-touch side, not just support or payment signals.

    Best, Chloé

  3. Arjun Patel plans the night

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

    Testing single-request submission after a schema error with the full 3-report batch.

    Castell Fasteners traffic drop: seasonal pattern or anomaly across B2B wholesale merchants
    Question
    Is Castell Fasteners Q4 web traffic decline normal seasonal behavior shared by other B2B wholesale merchants, or a Castell-specific anomaly?
    One row per
    B2B wholesale merchant (tenant)
    Columns
    • merchant name
    • industry
    • web sessions Oct-Dec 2023
    • web sessions Oct-Dec 2024
    • web sessions Oct-Dec 2025
    • YoY growth pct 2024 to 2025
    • YoY growth pct 2023 to 2024

  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
  t.name AS merchant_name,
  t.industry,
  COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01') AS sessions_q4_2023,
  COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01') AS sessions_q4_2024,
  COUNT(*) FILTER (WHERE ws.started_at >= DATE '2025-10-01' AND ws.started_at < DATE '2026-01-01') AS sessions_q4_2025,
  CASE WHEN COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01') = 0 THEN NULL
       ELSE ROUND(100.0 * (COUNT(*) FILTER (WHERE ws.started_at >= DATE '2025-10-01' AND ws.started_at < DATE '2026-01-01') - COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01')) / COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01'), 1)
  END AS yoy_growth_pct_2024_to_2025,
  CASE WHEN COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01') = 0 THEN NULL
       ELSE ROUND(100.0 * (COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01') - COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01')) / COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01'), 1)
  END AS yoy_growth_pct_2023_to_2024
FROM tenants t
LEFT JOIN web_sessions ws ON ws.tenant_id = t.id
WHERE t.name IN (
  'Castell Fasteners', 'OfficeHub Nordic', 'Deskline Supplies',
  'Ballycorran Industrial Supply', 'Midland Safety Wear', 'Polder Packaging',
  'Kontor24 Burobedarf'
)
GROUP BY t.id, t.name, t.industry
ORDER BY sessions_q4_2025 DESC, t.id ASC
LIMIT 50

    “Query compiles fine. 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 counts raw web_sessions rows with started_at in Oct 1–Dec 31 for each of 2023/2024/2025 per B2B wholesale peer, with YoY growth percentages left blank when the prior-year base is zero, ordered by 2025 Q4 sessions descending (tie-break tenant_id).”

  5. The report comes back overnight

    As Arjun Patel saw it, from the small dataset the agents worked on

    merchant_name                 | industry                  | sessions_q4_2023 | sessions_q4_2024 | sessions_q4_2025 | yoy_growth_pct_2024_to_2025 | yoy_growth_pct_2023_to_2024
------------------------------+---------------------------+------------------+------------------+------------------+-----------------------------+----------------------------
Ballycorran Industrial Supply | Industrial Supplies (B2B) |               62 |               75 |               87 |                        16.0 |                        21.0
OfficeHub Nordic              | Office Supplies (B2B)     |               32 |               37 |               69 |                        86.5 |                        15.6
Castell Fasteners             | Industrial Supplies (B2B) |               31 |               47 |               42 |                       -10.6 |                        51.6
Midland Safety Wear           | Industrial Supplies (B2B) |               26 |               36 |               40 |                        11.1 |                        38.5
Polder Packaging              | Industrial Supplies (B2B) |               17 |               14 |               26 |                        85.7 |                       -17.6
Deskline Supplies             | Office Supplies (B2B)     |               13 |               16 |               14 |                       -12.5 |                        23.1
(6 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 q268

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

    • spellingfetch first
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • made explicita selected t.col gets AS col, its standard name made explicit
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (2×)
    • placeholderfetch first
    The benchmark text, against the submitted SQL
    SELECT  t.name AS merchant_name,  t.industry,  t.industry AS industry,  COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01') AS sessions_q4_2023,  COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01') AS sessions_q4_2024,  COUNT(*) FILTER (WHERE ws.started_at >= DATE '2025-10-01' AND ws.started_at < DATE '2026-01-01') AS sessions_q4_2025,  CASE WHEN COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01') = 0 THEN NULL       ELSE ROUND(100.0 * (COUNT(*) FILTER (WHERE ws.started_at >= DATE '2025-10-01' AND ws.started_at < DATE '2026-01-01') - COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01')) / COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01'), 1)       ELSE 100.0 * (COUNT(*) FILTER (WHERE ws.started_at >= DATE '2025-10-01' AND ws.started_at < DATE '2026-01-01') - COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01')) / COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01')  END AS yoy_growth_pct_2024_to_2025,  CASE WHEN COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01') = 0 THEN NULL       ELSE ROUND(100.0 * (COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01') - COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01')) / COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01'), 1)       ELSE 100.0 * (COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01') - COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01')) / COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01')  END AS yoy_growth_pct_2023_to_2024FROM tenants tLEFT JOIN web_sessions ws ON ws.tenant_id = t.idWHERE t.name IN (  'Castell Fasteners', 'OfficeHub Nordic', 'Deskline Supplies',  'Ballycorran Industrial Supply', 'Midland Safety Wear', 'Polder Packaging',  'Kontor24 Burobedarf')GROUP BY t.id, t.name, t.industryORDER BY sessions_q4_2025 DESC, t.id ASCLIMIT 50ORDER BY sessions_q4_2025 DESC NULLS FIRST, t.id ASC NULLS LAST{{fetch_first(50)}}

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

    SELECT
  t.name AS merchant_name,
  t.industry AS industry,
  COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01') AS sessions_q4_2023,
  COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01') AS sessions_q4_2024,
  COUNT(*) FILTER (WHERE ws.started_at >= DATE '2025-10-01' AND ws.started_at < DATE '2026-01-01') AS sessions_q4_2025,
  CASE WHEN COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01') = 0 THEN NULL
       ELSE 100.0 * (COUNT(*) FILTER (WHERE ws.started_at >= DATE '2025-10-01' AND ws.started_at < DATE '2026-01-01') - COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01')) / COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01')
  END AS yoy_growth_pct_2024_to_2025,
  CASE WHEN COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01') = 0 THEN NULL
       ELSE 100.0 * (COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01') - COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01')) / COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01')
  END AS yoy_growth_pct_2023_to_2024
FROM tenants t
LEFT JOIN web_sessions ws ON ws.tenant_id = t.id
WHERE t.name IN (
  'Castell Fasteners', 'OfficeHub Nordic', 'Deskline Supplies',
  'Ballycorran Industrial Supply', 'Midland Safety Wear', 'Polder Packaging',
  'Kontor24 Burobedarf'
)
GROUP BY t.id, t.name, t.industry
ORDER BY sessions_q4_2025 DESC NULLS FIRST, t.id ASC NULLS LAST
FETCH FIRST 50 ROWS ONLY
    1 ms10 ms100 ms1 sHyper9.9 msDuckDB10.6 msGizmoSQL15.8 msclickhouse_brew94.9 msFirebolt62.5 msDataFusion118 msClickHouse46.4 ms

    Scale factor 1000, c8a.24xlarge, after the last load. q268 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 Arjun Patel (analyst for the Head of Merchant Success):

Title: Castell Fasteners traffic drop: seasonal pattern or anomaly across B2B wholesale merchants
Question: Is Castell Fasteners Q4 web traffic decline normal seasonal behavior shared by other B2B wholesale merchants, or a Castell-specific anomaly?
Grain: one row per B2B wholesale merchant (tenant)
Columns:
- merchant name
- industry
- web sessions Oct-Dec 2023
- web sessions Oct-Dec 2024
- web sessions Oct-Dec 2025
- YoY growth pct 2024 to 2025
- YoY growth pct 2023 to 2024
Details: Tables web_sessions (started_at, tenant_id) and tenants (name, industry). Restrict to the B2B wholesale peer group: Castell Fasteners, OfficeHub Nordic, Deskline Supplies, Ballycorran Industrial Supply, Midland Safety Wear, Polder Packaging, Kontor24 Burobedarf. For each merchant count web_sessions with started_at from Oct 1 through Dec 31 in 2023, 2024 and 2025 separately. YoY growth pct 2024 to 2025 equals the 2025 count minus the 2024 count, divided by the 2024 count, blank if the 2024 count is zero; YoY growth pct 2023 to 2024 defined the same way. Order by web sessions Oct-Dec 2025 descending, tie-break tenant_id ascending. Cap 50 rows.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Ines calls dry_run
      SELECT
  t.name AS merchant_name,
  t.industry,
  COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01') AS sessions_q4_2023,
  COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01') AS sessions_q4_2024,
  COUNT(*) FILTER (WHERE ws.started_at >= DATE '2025-10-01' AND ws.started_at < DATE '2026-01-01') AS sessions_q4_2025,
  CASE WHEN COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01') = 0 THEN NULL
       ELSE ROUND(100.0 * (COUNT(*) FILTER (WHERE ws.started_at >= DATE '2025-10-01' AND ws.started_at < DATE '2026-01-01') - COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01')) / COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01'), 1)
  END AS yoy_growth_pct_2024_to_2025,
  CASE WHEN COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01') = 0 THEN NULL
       ELSE ROUND(100.0 * (COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01') - COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01')) / COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01'), 1)
  END AS yoy_growth_pct_2023_to_2024
FROM tenants t
LEFT JOIN web_sessions ws ON ws.tenant_id = t.id
WHERE t.name IN (
  'Castell Fasteners', 'OfficeHub Nordic', 'Deskline Supplies',
  'Ballycorran Industrial Supply', 'Midland Safety Wear', 'Polder Packaging',
  'Kontor24 Burobedarf'
)
GROUP BY t.id, t.name, t.industry
ORDER BY sessions_q4_2025 DESC, t.id ASC
LIMIT 50
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: merchant_name, industry, sessions_q4_2023, sessions_q4_2024, sessions_q4_2025, yoy_growth_pct_2024_to_2025, yoy_growth_pct_2023_to_2024
    • InesQuery compiles fine. Submitting.
    • Ines calls submit
      SELECT
  t.name AS merchant_name,
  t.industry,
  COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01') AS sessions_q4_2023,
  COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01') AS sessions_q4_2024,
  COUNT(*) FILTER (WHERE ws.started_at >= DATE '2025-10-01' AND ws.started_at < DATE '2026-01-01') AS sessions_q4_2025,
  CASE WHEN COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01') = 0 THEN NULL
       ELSE ROUND(100.0 * (COUNT(*) FILTER (WHERE ws.started_at >= DATE '2025-10-01' AND ws.started_at < DATE '2026-01-01') - COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01')) / COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01'), 1)
  END AS yoy_growth_pct_2024_to_2025,
  CASE WHEN COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01') = 0 THEN NULL
       ELSE ROUND(100.0 * (COUNT(*) FILTER (WHERE ws.started_at >= DATE '2024-10-01' AND ws.started_at < DATE '2025-01-01') - COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01')) / COUNT(*) FILTER (WHERE ws.started_at >= DATE '2023-10-01' AND ws.started_at < DATE '2024-01-01'), 1)
  END AS yoy_growth_pct_2023_to_2024
FROM tenants t
LEFT JOIN web_sessions ws ON ws.tenant_id = t.id
WHERE t.name IN (
  'Castell Fasteners', 'OfficeHub Nordic', 'Deskline Supplies',
  'Ballycorran Industrial Supply', 'Midland Safety Wear', 'Polder Packaging',
  'Kontor24 Burobedarf'
)
GROUP BY t.id, t.name, t.industry
ORDER BY sessions_q4_2025 DESC, t.id ASC
LIMIT 50
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • InesSubmitted. The report counts raw `web_sessions` rows with `started_at` in Oct 1–Dec 31 for each of 2023/2024/2025 per B2B wholesale peer, with YoY growth percentages left blank when the prior-year base is zero, ordered by 2025 Q4 sessions descending (tie-break tenant_id).

    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 (262 KB) · Arjun Patel's transcript (881 KB) · Samuel Whitcombe's transcript (261 KB) · the night's plan (1 KB) · the night's reports (1 KB) · the week's memo (4 KB)