DB Olympics

Queries · q385 · Head of B2B Commerce · week 2, night 2

B2B Wholesale New-Business Opportunities: Created vs Closed by Month

“Did new-deal creation actually stop or slow down recently, or did deals just happen to all get closed out right as the data ends — i.e., is the 'zero open pipeline' at Dec 2025 a real freeze or a coincidence of timing?”

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

    Night 1 gave three clean answers. (1) Activity timing on lost deals: only 40% of lost opportunities have any logged activity in their first quarter of life, rising to 100% in the second quarter and staying high (96.7%, 76.7%) through the third and fourth — this reads as 'actively worked to the end' for the large majority, not 'stalled and forgotten', though ~23% do go quiet before closing. (2) Youssef Meijer's 7 deals span 7 distinct accounts (no territory/account concentration), but his 3 losses include a €26,000 deal — by dollar value he won only ~26% of his pipeline despite a 57% count-based win rate, suggesting he's fine on small/quick deals but struggles to close large ones. (3) The 'zero open pipeline' finding is NOT a 3-year constant — nearly every month-end from 2023 through Nov 2025 had a handful of genuinely open deals (some 40-70+ days old); only Dec 2025 (and two earlier one-off months) show zero. That raises a new question before I can tell Patrick whether this is a real business fact or a data gap. Tonight's three slots: (1) check whether opportunity creation volume dropped in Oct-Dec 2025 (would signal a data-freshness/ingestion gap) or stayed normal (meaning it's a real, if unusual, pipeline flush); (2) extend the Youssef finding to all reps via a dollar-weighted vs count-weighted win-rate comparison, to see if 'wins small deals, loses the big one' is a broader pattern worth flagging beyond one rep; (3) Patrick's lower-priority Q4 on seasonality of lost-deal duration, since we have a free slot and it's a quick, well-defined query.

    B2B Wholesale New-Business Opportunities: Created vs Closed by Month
    Question
    Did new-deal creation actually stop or slow down recently, or did deals just happen to all get closed out right as the data ends — i.e., is the 'zero open pipeline' at Dec 2025 a real freeze or a coincidence of timing?
    One row per
    calendar month, 2023-01 through 2025-12
    Columns
    • month
    • opportunities_created
    • opportunities_closed

  4. Ines writes the SQL

    The engineer · 2 dry runs on the empty schema, then the submission

    Dry run 1 · STATUS: REJECTED
    WITH target_pipelines AS (
  SELECT p.id AS pipeline_id, p.tenant_id AS tenant_id
  FROM pipelines p
  JOIN tenants t ON t.id = p.tenant_id
  WHERE t.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')
    AND p.name = 'New business'
),
months AS (
  SELECT DISTINCT month_start FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
created AS (
  SELECT date_trunc('month', o.created_at)::date AS month_start, count(*) AS cnt
  FROM opportunities o
  JOIN target_pipelines tp ON tp.pipeline_id = o.pipeline_id AND tp.tenant_id = o.tenant_id
  WHERE o.created_at >= DATE '2023-01-01' AND o.created_at < DATE '2026-01-01'
  GROUP BY 1
),
closed AS (
  SELECT date_trunc('month', o.closed_at)::date AS month_start, count(*) AS cnt
  FROM opportunities o
  JOIN target_pipelines tp ON tp.pipeline_id = o.pipeline_id AND tp.tenant_id = o.tenant_id
  WHERE o.is_closed = true AND o.closed_at >= DATE '2023-01-01' AND o.closed_at < DATE '2026-01-01'
  GROUP BY 1
)
SELECT to_char(m.month_start, 'YYYY-MM') AS month,
       coalesce(c.cnt,0) AS opportunities_created,
       coalesce(cl.cnt,0) AS opportunities_closed
FROM months m
LEFT JOIN created c ON c.month_start = m.month_start
LEFT JOIN closed cl ON cl.month_start = m.month_start
ORDER BY m.month_start
    Dry run 2 · 2 changes from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)
    WITH target_pipelines AS (  SELECT p.id AS pipeline_id, p.tenant_id AS tenant_id  FROM pipelines p  JOIN tenants t ON t.id = p.tenant_id  WHERE t.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')    AND p.name = 'New business'),months AS (  SELECT DISTINCT month_start FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'  SELECT DISTINCT month_start, month_label FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'),created AS (  SELECT date_trunc('month', o.created_at)::date AS month_start, count(*) AS cnt  FROM opportunities o  JOIN target_pipelines tp ON tp.pipeline_id = o.pipeline_id AND tp.tenant_id = o.tenant_id  WHERE o.created_at >= DATE '2023-01-01' AND o.created_at < DATE '2026-01-01'  GROUP BY 1),closed AS (  SELECT date_trunc('month', o.closed_at)::date AS month_start, count(*) AS cnt  FROM opportunities o  JOIN target_pipelines tp ON tp.pipeline_id = o.pipeline_id AND tp.tenant_id = o.tenant_id  WHERE o.is_closed = true AND o.closed_at >= DATE '2023-01-01' AND o.closed_at < DATE '2026-01-01'  GROUP BY 1)SELECT to_char(m.month_start, 'YYYY-MM') AS month,SELECT m.month_label AS month,       coalesce(c.cnt,0) AS opportunities_created,       coalesce(cl.cnt,0) AS opportunities_closedFROM months mLEFT JOIN created c ON c.month_start = m.month_startLEFT JOIN closed cl ON cl.month_start = m.month_startORDER BY m.month_start

    “This compiles correctly with the right columns. Submitting now.”

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

    “Submitted. The report gives one row per month from 2023-01 through 2025-12 (36 rows total) with opportunities_created and opportunities_closed counts, scoped to the five named B2B tenants' 'New business' pipelines — this will let Daniel see directly whether Oct–Dec 2025 creation volume tailed off (pointing to an ingestion gap) or stayed normal while closures simply caught up (a genuine pipeline freeze).”

  5. The report comes back overnight

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

    month   | opportunities_created | opportunities_closed
--------+-----------------------+---------------------
2023-01 |                     4 |                    3
2023-02 |                     4 |                    4
2023-03 |                     3 |                    5
2023-04 |                     3 |                    2
2023-05 |                     3 |                    2
2023-06 |                     2 |                    3
2023-07 |                     1 |                    1
2023-08 |                     3 |                    2
2023-09 |                     2 |                    2
2023-10 |                     4 |                    2
2023-11 |                     4 |                    7
2023-12 |                     7 |                    3
2024-01 |                     4 |                    8
2024-02 |                     6 |                    4
2024-03 |                     1 |                    3
2024-04 |                     9 |                    3
2024-05 |                     2 |                    8
2024-06 |                     3 |                    0
2024-07 |                     3 |                    5
2024-08 |                     2 |                    3
2024-09 |                     5 |                    2
2024-10 |                     8 |                    7
2024-11 |                     5 |                    7
2024-12 |                     3 |                    4
2025-01 |                     2 |                    3
2025-02 |                     3 |                    2
2025-03 |                     5 |                    4
2025-04 |                     5 |                    6
2025-05 |                     2 |                    3
2025-06 |                     3 |                    2
2025-07 |                     3 |                    5
2025-08 |                     6 |                    2
2025-09 |                     4 |                    5
2025-10 |                     7 |                    5
2025-11 |                     3 |                    7
2025-12 |                     1 |                    2
(36 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 q385

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

    • spellingcast (2×)
    • determinismtie-breaker: month_label is not a function of month_start by schema
    • 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×)
    The benchmark text, against the submitted SQL
    WITH target_pipelines AS (  SELECT p.id AS pipeline_id, p.tenant_id AS tenant_id  FROM pipelines p  JOIN tenants t ON t.id = p.tenant_id  WHERE t.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')    AND p.name = 'New business'),months AS (  SELECT DISTINCT month_start, month_label FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'),created AS (  SELECT date_trunc('month', o.created_at)::date AS month_start, count(*) AS cnt  SELECT CAST(date_trunc('month', o.created_at) AS date) AS month_start, count(*) AS cnt  FROM opportunities o  JOIN target_pipelines tp ON tp.pipeline_id = o.pipeline_id AND tp.tenant_id = o.tenant_id  WHERE o.created_at >= DATE '2023-01-01' AND o.created_at < DATE '2026-01-01'  GROUP BY 1),closed AS (  SELECT date_trunc('month', o.closed_at)::date AS month_start, count(*) AS cnt  SELECT CAST(date_trunc('month', o.closed_at) AS date) AS month_start, count(*) AS cnt  FROM opportunities o  JOIN target_pipelines tp ON tp.pipeline_id = o.pipeline_id AND tp.tenant_id = o.tenant_id  WHERE o.is_closed = true AND o.closed_at >= DATE '2023-01-01' AND o.closed_at < DATE '2026-01-01'  GROUP BY 1)SELECT m.month_label AS month,       coalesce(c.cnt,0) AS opportunities_created,       coalesce(cl.cnt,0) AS opportunities_closedFROM months mLEFT JOIN created c ON c.month_start = m.month_startLEFT JOIN closed cl ON cl.month_start = m.month_startORDER BY m.month_startORDER BY m.month_start NULLS LAST, m.month_label NULLS LAST

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

    WITH target_pipelines AS (
  SELECT p.id AS pipeline_id, p.tenant_id AS tenant_id
  FROM pipelines p
  JOIN tenants t ON t.id = p.tenant_id
  WHERE t.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')
    AND p.name = 'New business'
),
months AS (
  SELECT DISTINCT month_start, month_label FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
created AS (
  SELECT CAST(date_trunc('month', o.created_at) AS date) AS month_start, count(*) AS cnt
  FROM opportunities o
  JOIN target_pipelines tp ON tp.pipeline_id = o.pipeline_id AND tp.tenant_id = o.tenant_id
  WHERE o.created_at >= DATE '2023-01-01' AND o.created_at < DATE '2026-01-01'
  GROUP BY 1
),
closed AS (
  SELECT CAST(date_trunc('month', o.closed_at) AS date) AS month_start, count(*) AS cnt
  FROM opportunities o
  JOIN target_pipelines tp ON tp.pipeline_id = o.pipeline_id AND tp.tenant_id = o.tenant_id
  WHERE o.is_closed = true AND o.closed_at >= DATE '2023-01-01' AND o.closed_at < DATE '2026-01-01'
  GROUP BY 1
)
SELECT m.month_label AS month,
       coalesce(c.cnt,0) AS opportunities_created,
       coalesce(cl.cnt,0) AS opportunities_closed
FROM months m
LEFT JOIN created c ON c.month_start = m.month_start
LEFT JOIN closed cl ON cl.month_start = m.month_start
ORDER BY m.month_start NULLS LAST, m.month_label NULLS LAST
    1 ms10 ms100 msHyper6.3 msDuckDB6.5 msGizmoSQL10.2 msclickhouse_brew85.6 msFirebolt56.8 msDataFusion72.1 msClickHouse22.1 ms

    Scale factor 1000, c8a.24xlarge, after the last load. q385 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, 9 turns
    • The request, as Ines received it
      Report request from Daniel Kim (analyst for the Head of B2B Commerce):

Title: B2B Wholesale New-Business Opportunities: Created vs Closed by Month
Question: Did new-deal creation actually stop or slow down recently, or did deals just happen to all get closed out right as the data ends — i.e., is the 'zero open pipeline' at Dec 2025 a real freeze or a coincidence of timing?
Grain: one row per calendar month, 2023-01 through 2025-12
Columns:
- month
- opportunities_created
- opportunities_closed
Details: Scope: tenants.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging'), opportunities where pipeline_id = each tenant's 'New business' pipeline. opportunities_created = count where created_at falls in that month, regardless of status. opportunities_closed = count where closed_at falls in that month (is_closed = true). Full history 2023-01-01 through 2025-12-31. This follows up on last night's finding that every month from 2023 through Nov 2025 had a handful of opportunities genuinely open at month-end (sometimes 40-70+ days old), but Dec 2025 (and two earlier isolated months) showed zero — I want to see whether opportunity creation volume for Oct-Dec 2025 looks normal compared to the rest of 2025, or whether it tailed off, which would point to a data-freshness/ingestion gap for very recent records rather than a real pipeline freeze. Order by month ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Ines calls dry_run
      WITH target_pipelines AS (
  SELECT p.id AS pipeline_id, p.tenant_id AS tenant_id
  FROM pipelines p
  JOIN tenants t ON t.id = p.tenant_id
  WHERE t.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')
    AND p.name = 'New business'
),
months AS (
  SELECT DISTINCT month_start FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
created AS (
  SELECT date_trunc('month', o.created_at)::date AS month_start, count(*) AS cnt
  FROM opportunities o
  JOIN target_pipelines tp ON tp.pipeline_id = o.pipeline_id AND tp.tenant_id = o.tenant_id
  WHERE o.created_at >= DATE '2023-01-01' AND o.created_at < DATE '2026-01-01'
  GROUP BY 1
),
closed AS (
  SELECT date_trunc('month', o.closed_at)::date AS month_start, count(*) AS cnt
  FROM opportunities o
  JOIN target_pipelines tp ON tp.pipeline_id = o.pipeline_id AND tp.tenant_id = o.tenant_id
  WHERE o.is_closed = true AND o.closed_at >= DATE '2023-01-01' AND o.closed_at < DATE '2026-01-01'
  GROUP BY 1
)
SELECT to_char(m.month_start, 'YYYY-MM') AS month,
       coalesce(c.cnt,0) AS opportunities_created,
       coalesce(cl.cnt,0) AS opportunities_closed
FROM months m
LEFT JOIN created c ON c.month_start = m.month_start
LEFT JOIN closed cl ON cl.month_start = m.month_start
ORDER BY m.month_start
    • The warehouse answers
      STATUS: REJECTED
  - does not compile: Catalog Error: Scalar Function with name to_char does not exist!
    • Ines calls dry_run
      WITH target_pipelines AS (
  SELECT p.id AS pipeline_id, p.tenant_id AS tenant_id
  FROM pipelines p
  JOIN tenants t ON t.id = p.tenant_id
  WHERE t.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')
    AND p.name = 'New business'
),
months AS (
  SELECT DISTINCT month_start, month_label FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
created AS (
  SELECT date_trunc('month', o.created_at)::date AS month_start, count(*) AS cnt
  FROM opportunities o
  JOIN target_pipelines tp ON tp.pipeline_id = o.pipeline_id AND tp.tenant_id = o.tenant_id
  WHERE o.created_at >= DATE '2023-01-01' AND o.created_at < DATE '2026-01-01'
  GROUP BY 1
),
closed AS (
  SELECT date_trunc('month', o.closed_at)::date AS month_start, count(*) AS cnt
  FROM opportunities o
  JOIN target_pipelines tp ON tp.pipeline_id = o.pipeline_id AND tp.tenant_id = o.tenant_id
  WHERE o.is_closed = true AND o.closed_at >= DATE '2023-01-01' AND o.closed_at < DATE '2026-01-01'
  GROUP BY 1
)
SELECT m.month_label AS month,
       coalesce(c.cnt,0) AS opportunities_created,
       coalesce(cl.cnt,0) AS opportunities_closed
FROM months m
LEFT JOIN created c ON c.month_start = m.month_start
LEFT JOIN closed cl ON cl.month_start = m.month_start
ORDER BY m.month_start
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, opportunities_created, opportunities_closed
    • InesThis compiles correctly with the right columns. Submitting now.
    • Ines calls submit
      WITH target_pipelines AS (
  SELECT p.id AS pipeline_id, p.tenant_id AS tenant_id
  FROM pipelines p
  JOIN tenants t ON t.id = p.tenant_id
  WHERE t.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')
    AND p.name = 'New business'
),
months AS (
  SELECT DISTINCT month_start, month_label FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
created AS (
  SELECT date_trunc('month', o.created_at)::date AS month_start, count(*) AS cnt
  FROM opportunities o
  JOIN target_pipelines tp ON tp.pipeline_id = o.pipeline_id AND tp.tenant_id = o.tenant_id
  WHERE o.created_at >= DATE '2023-01-01' AND o.created_at < DATE '2026-01-01'
  GROUP BY 1
),
closed AS (
  SELECT date_trunc('month', o.closed_at)::date AS month_start, count(*) AS cnt
  FROM opportunities o
  JOIN target_pipelines tp ON tp.pipeline_id = o.pipeline_id AND tp.tenant_id = o.tenant_id
  WHERE o.is_closed = true AND o.closed_at >= DATE '2023-01-01' AND o.closed_at < DATE '2026-01-01'
  GROUP BY 1
)
SELECT m.month_label AS month,
       coalesce(c.cnt,0) AS opportunities_created,
       coalesce(cl.cnt,0) AS opportunities_closed
FROM months m
LEFT JOIN created c ON c.month_start = m.month_start
LEFT JOIN closed cl ON cl.month_start = m.month_start
ORDER BY m.month_start
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • InesSubmitted. The report gives one row per month from 2023-01 through 2025-12 (36 rows total) with `opportunities_created` and `opportunities_closed` counts, scoped to the five named B2B tenants' 'New business' pipelines — this will let Daniel see directly whether Oct–Dec 2025 creation volume tailed off (pointing to an ingestion gap) or stayed normal while closures simply caught up (a genuine pipeline freeze).

    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 (270 KB) · Daniel Kim's transcript (789 KB) · Patrick O'Neill's transcript (263 KB) · the night's plan (6 KB) · the night's reports (7 KB) · the week's memo (3 KB)