DB Olympics

Queries · q383 · Head of B2B Commerce · week 2, night 1

Youssef Meijer: Individual New-Business Opportunities

“Looking at Youssef Meijer's own deals one by one — which accounts, what sizes, how long open — does his low win rate look like a few bad accounts/territory, or a pattern across all his work?”

Manager · asked the questionPatrick O'NeillHead of B2B Commerce. Built B2B sales teams at a software company. Owns the B2B features wholesale merchants run their sales on.
Analyst · planned the reportDaniel KimFunnel thinker: measures each step of a process and where things drop out.
Engineer · wrote the SQLGraceWrites tidy, layered CTEs with descriptive names, one step per CTE, then a final select.

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 B2B Commerce's desk

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

    B2B merchants say their sales teams struggle. How do their pipelines convert from lead to won deal, how long does it take, and what predicts winning or losing?

    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. Patrick O'Neill writes to Daniel Kim

    The manager, at the start of week 2

    Note to Daniel Kim — reactions and next focus

    Daniel, this is a strong memo — a few things surprised me and changed what I want to chase next.

    What surprised me / needs explaining:

    • The "zero open pipeline" finding is bigger than a side note to me — if every opportunity we've ever seen across 3 years is already closed, we have no forward-looking view at all right now for any of these merchants. Before I take that to Samuel, I need to understand whether that's a real business fact (deals genuinely don't sit open long) or whether open deals simply aren't being entered into the system until they're decided.
    • The "lost deals get more attention, not less" finding cuts against my instinct. I want to understand the shape of that attention, not just the average — are reps front-loading effort and then the deal goes quiet for weeks before it's marked lost, or is the activity spread evenly, including right up to the end? That tells us whether the fix is "kill deals faster" or "the last mile of a sale is where we're weak."
    • Youssef Meijer is the one concrete, actionable lead we got this week — I want to go one level deeper before I talk to Polder Packaging.

    Questions for this week:

    1. For the deals that eventually lose, can you show roughly when the activity happens relative to the deal's life — clustered early, steady throughout, or a late flurry right before it's given up on? I want to know if these are "stalled and forgotten until someone finally closes it out" versus "actively fought to the end and still lost."
    2. Is the complete absence of open pipeline a constant feature of the last three years, or has it changed recently — i.e., are merchants currently sitting on live deals that just haven't shown up yet, or does this system genuinely never carry an open deal for more than a few weeks?
    3. Can you pull Youssef Meijer's individual deals — which accounts, what sizes, how old — so I can tell whether this looks like a few bad accounts/territory versus a pattern across all his work?
    4. Of the lost deals, is there any sign of when in the quarter or year they tend to die — e.g., do deals linger longest around certain times (end of quarter pushes, holiday periods), or is the ~35-day drag constant regardless of when the deal started?

    I know the instrumentation gaps (no stage history, no merchant-side payment terms) aren't fixable by you this week — flag both to product as you planned, that's the right move. Let's prioritize Q1 and Q3 above everything else; Q2 and Q4 can slip if slots run short.

    Thanks, Daniel — good detective work this week.

    Patrick

  3. Daniel Kim plans the night

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

    Patrick wants Q1 (shape of activity on lost deals) and Q3 (Youssef Meijer deep-dive) prioritized, with Q2 (is zero open pipeline a constant or recent change) and Q4 (seasonality of when deals die) able to slip. I'm using all 3 slots tonight on Q1, Q3, and Q2 since Q2 is a cheap, decisive check (one grouped query against a 36-month calendar spine) that directly answers whether 'no open pipeline' is a real 3-year pattern or a recent artifact — worth confirming before Patrick takes it to Samuel. Q4 (quarter/seasonal timing of when lost deals die) is the one I'm holding back this week since it's explicitly lower priority and I'd rather see tonight's three answers first — if Q1's activity-timing report shows a seasonal clustering on its own, that might already partially answer Q4 for free. The Youssef Meijer report deliberately keeps scope to his own New-business opportunities only, matching how we found him (via last week's corrected rep-level report), and converts to EUR using the same formula we fixed last week to avoid repeating the earlier NULL bug.

    Youssef Meijer: Individual New-Business Opportunities
    Question
    Looking at Youssef Meijer's own deals one by one — which accounts, what sizes, how long open — does his low win rate look like a few bad accounts/territory, or a pattern across all his work?
    One row per
    opportunity owned by Youssef Meijer
    Columns
    • account_name
    • outcome (won / lost)
    • amount_eur
    • currency_code
    • days_open (closed_at - created_at)
    • created_at
    • closed_at

  4. Grace 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)
    WITH polder_tenant AS (
  SELECT id AS tenant_id
  FROM tenants
  WHERE name = 'Polder Packaging'
),
owner AS (
  SELECT u.id AS user_id
  FROM users u
  JOIN polder_tenant pt ON u.tenant_id = pt.tenant_id
  WHERE u.full_name = 'Youssef Meijer'
),
pipeline AS (
  SELECT p.id AS pipeline_id
  FROM pipelines p
  JOIN polder_tenant pt ON p.tenant_id = pt.tenant_id
  WHERE p.name = 'New business'
),
target_opportunities AS (
  SELECT
    o.id,
    o.account_id,
    o.amount,
    o.currency_code,
    o.is_won,
    o.created_at,
    o.closed_at
  FROM opportunities o
  JOIN owner ON o.owner_user_id = owner.user_id
  JOIN pipeline ON o.pipeline_id = pipeline.pipeline_id
  WHERE o.is_closed = true
),
with_account AS (
  SELECT
    t.*,
    a.name AS account_name
  FROM target_opportunities t
  JOIN accounts a ON t.account_id = a.id
),
with_rate AS (
  SELECT
    wa.*,
    CASE
      WHEN wa.currency_code = 'EUR' THEN 1.0
      ELSE (
        SELECT er.rate
        FROM exchange_rates er
        WHERE er.base_code = 'EUR'
          AND er.quote_code = wa.currency_code
          AND er.as_of <= wa.closed_at::date
        ORDER BY er.as_of DESC
        LIMIT 1
      )
    END AS eur_rate
  FROM with_account wa
)
SELECT
  account_name,
  CASE WHEN is_won THEN 'won' ELSE 'lost' END AS outcome,
  ROUND(amount / eur_rate, 2) AS amount_eur,
  currency_code,
  (closed_at::date - created_at::date) AS days_open,
  created_at,
  closed_at
FROM with_rate
ORDER BY closed_at ASC, account_name ASC

    “Query compiles cleanly. 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 one row per closed opportunity owned by Youssef Meijer (Polder Packaging, New business pipeline), ordered chronologically by closed_at with account_name as tie-break, so the analyst can read it as a case-file sequence and spot whether losses cluster by account/size/duration.”

  5. The report comes back overnight

    As Daniel Kim saw it, from the small dataset the agents worked on

    account_name                             | outcome | amount_eur | currency_code | days_open | created_at                 | closed_at                 
-----------------------------------------+---------+------------+---------------+-----------+----------------------------+---------------------------
van den Broek Logistiek BV               | won     |     5300.0 | EUR           |        18 | 2023-12-24 19:05:29.039224 | 2024-01-11 17:18:22.797757
Jansen Metaal BV                         | won     |     5500.0 | EUR           |         0 | 2024-04-22 12:16:44.95064  | 2024-04-22 12:16:44.95064 
Bos & van der Meer Bouw BV               | lost    |     5400.0 | EUR           |        36 | 2024-04-06 14:53:29.325756 | 2024-05-12 14:53:29.325756
Krüger Elektro GmbH                      | won     |     2300.0 | EUR           |        18 | 2024-06-29 12:15:02.517082 | 2024-07-17 12:15:02.517082
Mulder Metaal BV                         | won     |     1000.0 | EUR           |        13 | 2024-11-07 14:49:34.195554 | 2024-11-20 14:49:34.195554
Claes & Willems Installatie BV           | lost    |     9300.0 | EUR           |        38 | 2025-03-22 12:56:52.584993 | 2025-04-29 12:56:52.584993
van den Berg & van der Meer Logistiek BV | lost    |    26000.0 | EUR           |        22 | 2025-08-26 14:31:00.093123 | 2025-09-17 14:31:00.093123
(7 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 q383

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

    • spellingcast (3×)
    • spellingfetch first
    • determinismtie-breaker: opportunity id
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
    • made explicita selected t.col gets AS col, its standard name made explicit (7×)
    • placeholderdate diff days
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (4×)
    • placeholderfetch first
    The benchmark text, against the submitted SQL
    WITH polder_tenant AS (  SELECT id AS tenant_id  FROM tenants  WHERE name = 'Polder Packaging'),owner AS (  SELECT u.id AS user_id  FROM users u  JOIN polder_tenant pt ON u.tenant_id = pt.tenant_id  WHERE u.full_name = 'Youssef Meijer'),pipeline AS (  SELECT p.id AS pipeline_id  FROM pipelines p  JOIN polder_tenant pt ON p.tenant_id = pt.tenant_id  WHERE p.name = 'New business'),target_opportunities AS (  SELECT    o.id,    o.account_id,    o.amount,    o.currency_code,    o.is_won,    o.created_at,    o.closed_at    o.id AS id,    o.account_id AS account_id,    o.amount AS amount,    o.currency_code AS currency_code,    o.is_won AS is_won,    o.created_at AS created_at,    o.closed_at AS closed_at  FROM opportunities o  JOIN owner ON o.owner_user_id = owner.user_id  JOIN pipeline ON o.pipeline_id = pipeline.pipeline_id  WHERE o.is_closed = true),with_account AS (  SELECT    t.*,    a.name AS account_name  FROM target_opportunities t  JOIN accounts a ON t.account_id = a.id),with_rate AS (  SELECT    wa.*,    CASE      WHEN wa.currency_code = 'EUR' THEN 1.0      ELSE (        SELECT er.rate        FROM exchange_rates er        WHERE er.base_code = 'EUR'          AND er.quote_code = wa.currency_code          AND er.as_of <= wa.closed_at::date        ORDER BY er.as_of DESC        LIMIT 1          AND er.as_of <= CAST(wa.closed_at AS date)        ORDER BY er.as_of DESC NULLS FIRST        {{fetch_first(1)}}      )    END AS eur_rate  FROM with_account wa)SELECT  account_name,  CASE WHEN is_won THEN 'won' ELSE 'lost' END AS outcome,  ROUND(amount / eur_rate, 2) AS amount_eur,  CAST(amount / eur_rate AS NUMERIC(18,6)) AS amount_eur,  currency_code,  (closed_at::date - created_at::date) AS days_open,  {{date_diff_days(created_at, closed_at)}} AS days_open,  created_at,  closed_atFROM with_rateORDER BY closed_at ASC, account_name ASCORDER BY closed_at ASC NULLS LAST, account_name ASC NULLS LAST, id ASC NULLS LAST

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

    WITH polder_tenant AS (
  SELECT id AS tenant_id
  FROM tenants
  WHERE name = 'Polder Packaging'
),
owner AS (
  SELECT u.id AS user_id
  FROM users u
  JOIN polder_tenant pt ON u.tenant_id = pt.tenant_id
  WHERE u.full_name = 'Youssef Meijer'
),
pipeline AS (
  SELECT p.id AS pipeline_id
  FROM pipelines p
  JOIN polder_tenant pt ON p.tenant_id = pt.tenant_id
  WHERE p.name = 'New business'
),
target_opportunities AS (
  SELECT
    o.id AS id,
    o.account_id AS account_id,
    o.amount AS amount,
    o.currency_code AS currency_code,
    o.is_won AS is_won,
    o.created_at AS created_at,
    o.closed_at AS closed_at
  FROM opportunities o
  JOIN owner ON o.owner_user_id = owner.user_id
  JOIN pipeline ON o.pipeline_id = pipeline.pipeline_id
  WHERE o.is_closed = true
),
with_account AS (
  SELECT
    t.*,
    a.name AS account_name
  FROM target_opportunities t
  JOIN accounts a ON t.account_id = a.id
),
with_rate AS (
  SELECT
    wa.*,
    CASE
      WHEN wa.currency_code = 'EUR' THEN 1.0
      ELSE (
        SELECT er.rate
        FROM exchange_rates er
        WHERE er.base_code = 'EUR'
          AND er.quote_code = wa.currency_code
          AND er.as_of <= CAST(wa.closed_at AS date)
        ORDER BY er.as_of DESC NULLS FIRST
        FETCH FIRST 1 ROWS ONLY
      )
    END AS eur_rate
  FROM with_account wa
)
SELECT
  account_name,
  CASE WHEN is_won THEN 'won' ELSE 'lost' END AS outcome,
  CAST(amount / eur_rate AS NUMERIC(18,6)) AS amount_eur,
  currency_code,
  DATE_DIFF('day', created_at, closed_at) AS days_open,
  created_at,
  closed_at
FROM with_rate
ORDER BY closed_at ASC NULLS LAST, account_name ASC NULLS LAST, id ASC NULLS LAST
    1 ms10 ms100 ms1 s10 sHyper7.3 msDuckDB21.3 msGizmoSQL24.8 msclickhouse_brew109 msFirebolt2.05 sDataFusionerrorClickHouseerror
    • DataFusionThis feature is not implemented: Physical plan does not support logical expression ScalarSubquery(<subquery>)
    • ClickHouseCode: 48. DB::Exception: Cannot decorrelate query, because 'Limit' step is not supported. (NOT_IMPLEMENTED)

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

    Grace's whole session, 7 turns
    • The request, as Grace received it
      Report request from Daniel Kim (analyst for the Head of B2B Commerce):

Title: Youssef Meijer: Individual New-Business Opportunities
Question: Looking at Youssef Meijer's own deals one by one — which accounts, what sizes, how long open — does his low win rate look like a few bad accounts/territory, or a pattern across all his work?
Grain: one row per opportunity owned by Youssef Meijer
Columns:
- account_name
- outcome (won / lost)
- amount_eur
- currency_code
- days_open (closed_at - created_at)
- created_at
- closed_at
Details: Filter to opportunities where owner_user_id matches the user named 'Youssef Meijer' at Polder Packaging (join users on full_name = 'Youssef Meijer' and tenant_id = Polder Packaging's tenant_id, to avoid any name collision at another tenant), pipeline_id = Polder Packaging's 'New business' pipeline, is_closed = true (we've confirmed all his opportunities are closed; this report should return all of them, won and lost, expected around 7 rows per last week's rep-level report). Join accounts for account_name via opportunities.account_id. Convert amount to EUR: if currency_code = 'EUR', amount_eur = amount directly; otherwise amount_eur = amount / rate from exchange_rates (base_code='EUR', quote_code=currency_code, as_of nearest date on or before closed_at). Order by closed_at ascending, tie-break by account_name ascending, so the report reads as a chronological case file.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Grace calls dry_run
      WITH polder_tenant AS (
  SELECT id AS tenant_id
  FROM tenants
  WHERE name = 'Polder Packaging'
),
owner AS (
  SELECT u.id AS user_id
  FROM users u
  JOIN polder_tenant pt ON u.tenant_id = pt.tenant_id
  WHERE u.full_name = 'Youssef Meijer'
),
pipeline AS (
  SELECT p.id AS pipeline_id
  FROM pipelines p
  JOIN polder_tenant pt ON p.tenant_id = pt.tenant_id
  WHERE p.name = 'New business'
),
target_opportunities AS (
  SELECT
    o.id,
    o.account_id,
    o.amount,
    o.currency_code,
    o.is_won,
    o.created_at,
    o.closed_at
  FROM opportunities o
  JOIN owner ON o.owner_user_id = owner.user_id
  JOIN pipeline ON o.pipeline_id = pipeline.pipeline_id
  WHERE o.is_closed = true
),
with_account AS (
  SELECT
    t.*,
    a.name AS account_name
  FROM target_opportunities t
  JOIN accounts a ON t.account_id = a.id
),
with_rate AS (
  SELECT
    wa.*,
    CASE
      WHEN wa.currency_code = 'EUR' THEN 1.0
      ELSE (
        SELECT er.rate
        FROM exchange_rates er
        WHERE er.base_code = 'EUR'
          AND er.quote_code = wa.currency_code
          AND er.as_of <= wa.closed_at::date
        ORDER BY er.as_of DESC
        LIMIT 1
      )
    END AS eur_rate
  FROM with_account wa
)
SELECT
  account_name,
  CASE WHEN is_won THEN 'won' ELSE 'lost' END AS outcome,
  ROUND(amount / eur_rate, 2) AS amount_eur,
  currency_code,
  (closed_at::date - created_at::date) AS days_open,
  created_at,
  closed_at
FROM with_rate
ORDER BY closed_at ASC, account_name ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: account_name, outcome, amount_eur, currency_code, days_open, created_at, closed_at
    • GraceQuery compiles cleanly. Submitting.
    • Grace calls submit
      WITH polder_tenant AS (
  SELECT id AS tenant_id
  FROM tenants
  WHERE name = 'Polder Packaging'
),
owner AS (
  SELECT u.id AS user_id
  FROM users u
  JOIN polder_tenant pt ON u.tenant_id = pt.tenant_id
  WHERE u.full_name = 'Youssef Meijer'
),
pipeline AS (
  SELECT p.id AS pipeline_id
  FROM pipelines p
  JOIN polder_tenant pt ON p.tenant_id = pt.tenant_id
  WHERE p.name = 'New business'
),
target_opportunities AS (
  SELECT
    o.id,
    o.account_id,
    o.amount,
    o.currency_code,
    o.is_won,
    o.created_at,
    o.closed_at
  FROM opportunities o
  JOIN owner ON o.owner_user_id = owner.user_id
  JOIN pipeline ON o.pipeline_id = pipeline.pipeline_id
  WHERE o.is_closed = true
),
with_account AS (
  SELECT
    t.*,
    a.name AS account_name
  FROM target_opportunities t
  JOIN accounts a ON t.account_id = a.id
),
with_rate AS (
  SELECT
    wa.*,
    CASE
      WHEN wa.currency_code = 'EUR' THEN 1.0
      ELSE (
        SELECT er.rate
        FROM exchange_rates er
        WHERE er.base_code = 'EUR'
          AND er.quote_code = wa.currency_code
          AND er.as_of <= wa.closed_at::date
        ORDER BY er.as_of DESC
        LIMIT 1
      )
    END AS eur_rate
  FROM with_account wa
)
SELECT
  account_name,
  CASE WHEN is_won THEN 'won' ELSE 'lost' END AS outcome,
  ROUND(amount / eur_rate, 2) AS amount_eur,
  currency_code,
  (closed_at::date - created_at::date) AS days_open,
  created_at,
  closed_at
FROM with_rate
ORDER BY closed_at ASC, account_name ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • GraceSubmitted. The report returns one row per closed opportunity owned by Youssef Meijer (Polder Packaging, New business pipeline), ordered chronologically by closed_at with account_name as tie-break, so the analyst can read it as a case-file sequence and spot whether losses cluster by account/size/duration.

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

    Grace's transcript (264 KB) · Daniel Kim's transcript (789 KB) · Patrick O'Neill's transcript (263 KB) · the night's plan (6 KB) · the night's reports (9 KB) · the week's memo (3 KB)