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

Did B2B Wholesale New-Business Pipeline Ever Carry an Open Balance at Month-End?

“Is the current complete absence of open new-business pipeline a constant feature of the last three years, or did merchants used to carry live open deals and that's changed recently?”

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

   Show the whole note

3. Daniel Kim plans the night

   The analyst · week 2, night 1, request 3 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.

   Read the whole plan
   Did B2B Wholesale New-Business Pipeline Ever Carry an Open Balance at Month-End?

   Question

   Is the current complete absence of open new-business pipeline a constant feature of the last three years, or did merchants used to carry live open deals and that's changed recently?

   One row per

   calendar month-end snapshot, 2023-01 through 2025-12

   Columns

   • month_end_date
   • opportunities_open_at_month_end
   • avg_age_days_of_open_opportunities_at_month_end
   • oldest_age_days_of_open_opportunity_at_month_end

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 month_ends as (
       select
           year_num,
           month_num,
           max(calendar_date) as month_end_date
       from calendar_days
       where year_num between 2023 and 2025
       group by year_num, month_num
   ),
   scoped_tenants as (
       select id as tenant_id, name
       from tenants
       where name in (
           'Ballycorran Industrial Supply',
           'Castell Fasteners',
           'Hanse Industriebedarf',
           'Midland Safety Wear',
           'Polder Packaging'
       )
   ),
   new_business_pipelines as (
       select p.id as pipeline_id, p.tenant_id
       from pipelines p
       join scoped_tenants st on st.tenant_id = p.tenant_id
       where p.name = 'New business'
   ),
   scoped_opportunities as (
       select o.id, o.tenant_id, o.created_at, o.closed_at
       from opportunities o
       join new_business_pipelines nbp
           on nbp.pipeline_id = o.pipeline_id
           and nbp.tenant_id = o.tenant_id
   ),
   monthly_open_opportunities as (
       select
           me.month_end_date,
           o.id as opportunity_id,
           (me.month_end_date - o.created_at::date) as age_days
       from month_ends me
       cross join scoped_opportunities o
       where o.created_at::date <= me.month_end_date
           and (o.closed_at is null or o.closed_at::date > me.month_end_date)
   )
   select
       me.month_end_date,
       count(moo.opportunity_id) as opportunities_open_at_month_end,
       round(avg(moo.age_days)::numeric, 1) as avg_age_days_of_open_opportunities_at_month_end,
       max(moo.age_days) as oldest_age_days_of_open_opportunity_at_month_end
   from month_ends me
   left join monthly_open_opportunities moo
       on moo.month_end_date = me.month_end_date
   group by me.month_end_date
   order by me.month_end_date asc

   “The query compiles and returns exactly the requested grain and columns (36 monthly rows, 2023-01 through 2025-12).”

   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 open "New business" pipeline opportunities across the five named B2B wholesale tenants at each month-end from 2023-01 through 2025-12, with average and oldest age in days, so Daniel can see whether the current zero-pipeline state is new or has always been the case.”

5. The report comes back overnight

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

   month_end_date | opportunities_open_at_month_end | avg_age_days_of_open_opportunities_at_mo | oldest_age_days_of_open_opportunity_at_m
   ---------------+---------------------------------+------------------------------------------+-----------------------------------------
   2023-01-31     |                               2 |                                      4.5 |                                        5
   2023-02-28     |                               2 |                                     11.0 |                                       13
   2023-03-31     |                               0 |                                     NULL |                                     NULL
   2023-04-30     |                               1 |                                     24.0 |                                       24
   2023-05-31     |                               2 |                                     15.5 |                                       18
   2023-06-30     |                               1 |                                     12.0 |                                       12
   2023-07-31     |                               1 |                                     13.0 |                                       13
   2023-08-31     |                               2 |                                      7.5 |                                       13
   2023-09-30     |                               2 |                                      2.0 |                                        3
   2023-10-31     |                               4 |                                     15.5 |                                       27
   2023-11-30     |                               1 |                                      3.0 |                                        3
   2023-12-31     |                               5 |                                      4.6 |                                       15
   2024-01-31     |                               1 |                                      8.0 |                                        8
   2024-02-29     |                               3 |                                      8.3 |                                       12
   2024-03-31     |                               1 |                                     19.0 |                                       19
   2024-04-30     |                               7 |                                     10.6 |                                       27
   2024-05-31     |                               1 |                                     43.0 |                                       43
   2024-06-30     |                               4 |                                     26.3 |                                       73
   2024-07-31     |                               2 |                                      0.5 |                                        1
   2024-08-31     |                               1 |                                      1.0 |                                        1
   2024-09-30     |                               4 |                                      6.8 |                                       17
   2024-10-31     |                               5 |                                     21.4 |                                       32
   2024-11-30     |                               3 |                                     22.0 |                                       36
   2024-12-31     |                               2 |                                      8.5 |                                       13
   2025-01-31     |                               1 |                                     44.0 |                                       44
   2025-02-28     |                               2 |                                     18.0 |                                       23
   2025-03-31     |                               3 |                                      6.3 |                                        9
   2025-04-30     |                               2 |                                      1.5 |                                        3
   2025-05-31     |                               1 |                                      1.0 |                                        1
   2025-06-30     |                               2 |                                      4.5 |                                        9
   2025-07-31     |                               0 |                                     NULL |                                     NULL
   2025-08-31     |                               4 |                                      7.0 |                                       17
   2025-09-30     |                               3 |                                     19.0 |                                       47
   2025-10-31     |                               5 |                                     13.4 |                                       25
   2025-11-30     |                               1 |                                     31.0 |                                       31
   2025-12-31     |                               0 |                                     NULL |                                     NULL
   (36 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:

   • q385B2B Wholesale New-Business Opportunities: Created vs Closed by Month

   It builds on:

   • q172B2B Wholesale Pipeline: Opportunities by Merchant and Stage
   • q180B2B Wholesale: Age Profile of Currently Open New-Business Pipeline

7. It becomes benchmark query q384

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

   • editA difference of two dates is a number of days in PostgreSQL and needs an interval qualifier in the standard: the date_diff_days placeholder, as for the other date differences. (docs/RUNNER.md, Dialects)
   • spellingcast (4×)
   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed; AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
   • made explicita selected t.col gets AS col, its standard name made explicit (7×)
   • 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

   with month_ends as (    select        year_num,        month_num,        max(calendar_date) as month_end_date    from calendar_days    where year_num between 2023 and 2025    group by year_num, month_num),scoped_tenants as (    select id as tenant_id, name    from tenants    where name in (        'Ballycorran Industrial Supply',        'Castell Fasteners',        'Hanse Industriebedarf',        'Midland Safety Wear',        'Polder Packaging'    )),new_business_pipelines as (    select p.id as pipeline_id, p.tenant_id    select p.id as pipeline_id, p.tenant_id AS tenant_id    from pipelines p    join scoped_tenants st on st.tenant_id = p.tenant_id    where p.name = 'New business'),scoped_opportunities as (    select o.id, o.tenant_id, o.created_at, o.closed_at    select o.id AS id, o.tenant_id AS tenant_id, o.created_at AS created_at, o.closed_at AS closed_at    from opportunities o    join new_business_pipelines nbp        on nbp.pipeline_id = o.pipeline_id        and nbp.tenant_id = o.tenant_id),monthly_open_opportunities as (    select        me.month_end_date,        me.month_end_date AS month_end_date,        o.id as opportunity_id,        (me.month_end_date - o.created_at::date) as age_days        {{date_diff_days(CAST(o.created_at AS date), me.month_end_date)}} as age_days    from month_ends me    cross join scoped_opportunities o    where o.created_at::date <= me.month_end_date        and (o.closed_at is null or o.closed_at::date > me.month_end_date)    where CAST(o.created_at AS date) <= me.month_end_date        and (o.closed_at is null or CAST(o.closed_at AS date) > me.month_end_date))select    me.month_end_date,    me.month_end_date AS month_end_date,    count(moo.opportunity_id) as opportunities_open_at_month_end,    round(avg(moo.age_days)::numeric, 1) as avg_age_days_of_open_opportunities_at_month_end,    avg(cast(moo.age_days as numeric(18,6))) as avg_age_days_of_open_opportunities_at_month_end,    max(moo.age_days) as oldest_age_days_of_open_opportunity_at_month_endfrom month_ends meleft join monthly_open_opportunities moo    on moo.month_end_date = me.month_end_dategroup by me.month_end_dateorder by me.month_end_date ascorder by me.month_end_date asc NULLS LAST

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

   DuckDB · GizmoSQLHyper · CedarDB · pg_duckdb · PostgreSQL · DataFusion · Firebolt · clickhouse_brewClickHouse · chDB

   with month_ends as (
       select
           year_num,
           month_num,
           max(calendar_date) as month_end_date
       from calendar_days
       where year_num between 2023 and 2025
       group by year_num, month_num
   ),
   scoped_tenants as (
       select id as tenant_id, name
       from tenants
       where name in (
           'Ballycorran Industrial Supply',
           'Castell Fasteners',
           'Hanse Industriebedarf',
           'Midland Safety Wear',
           'Polder Packaging'
       )
   ),
   new_business_pipelines as (
       select p.id as pipeline_id, p.tenant_id AS tenant_id
       from pipelines p
       join scoped_tenants st on st.tenant_id = p.tenant_id
       where p.name = 'New business'
   ),
   scoped_opportunities as (
       select o.id AS id, o.tenant_id AS tenant_id, o.created_at AS created_at, o.closed_at AS closed_at
       from opportunities o
       join new_business_pipelines nbp
           on nbp.pipeline_id = o.pipeline_id
           and nbp.tenant_id = o.tenant_id
   ),
   monthly_open_opportunities as (
       select
           me.month_end_date AS month_end_date,
           o.id as opportunity_id,
           DATE_DIFF('day', CAST(o.created_at AS date), me.month_end_date) as age_days
       from month_ends me
       cross join scoped_opportunities o
       where CAST(o.created_at AS date) <= me.month_end_date
           and (o.closed_at is null or CAST(o.closed_at AS date) > me.month_end_date)
   )
   select
       me.month_end_date AS month_end_date,
       count(moo.opportunity_id) as opportunities_open_at_month_end,
       avg(cast(moo.age_days as numeric(18,6))) as avg_age_days_of_open_opportunities_at_month_end,
       max(moo.age_days) as oldest_age_days_of_open_opportunity_at_month_end
   from month_ends me
   left join monthly_open_opportunities moo
       on moo.month_end_date = me.month_end_date
   group by me.month_end_date
   order by me.month_end_date asc NULLS LAST

   Scale factor 1000, c8a.24xlarge, after the last load. q384 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: Did B2B Wholesale New-Business Pipeline Ever Carry an Open Balance at Month-End?
     Question: Is the current complete absence of open new-business pipeline a constant feature of the last three years, or did merchants used to carry live open deals and that's changed recently?
     Grain: one row per calendar month-end snapshot, 2023-01 through 2025-12
     Columns:
     - month_end_date
     - opportunities_open_at_month_end
     - avg_age_days_of_open_opportunities_at_month_end
     - oldest_age_days_of_open_opportunity_at_month_end
     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. For each calendar month from 2023-01 through 2025-12, take the last calendar day of that month as the snapshot date (derive from calendar_days, e.g. max(calendar_date) grouped by year_num, month_num). Count opportunities where created_at <= end of that snapshot date AND (closed_at IS NULL OR closed_at > end of that snapshot date) — i.e. opportunities that were genuinely open and straddling that month boundary. For those, compute avg and max age in days (snapshot_date - created_at::date). If a month has zero qualifying opportunities, still include the row with 0 / NULL / NULL. Order by month_end_date ascending.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Grace calls dry_run

     with month_ends as (
         select
             year_num,
             month_num,
             max(calendar_date) as month_end_date
         from calendar_days
         where year_num between 2023 and 2025
         group by year_num, month_num
     ),
     scoped_tenants as (
         select id as tenant_id, name
         from tenants
         where name in (
             'Ballycorran Industrial Supply',
             'Castell Fasteners',
             'Hanse Industriebedarf',
             'Midland Safety Wear',
             'Polder Packaging'
         )
     ),
     new_business_pipelines as (
         select p.id as pipeline_id, p.tenant_id
         from pipelines p
         join scoped_tenants st on st.tenant_id = p.tenant_id
         where p.name = 'New business'
     ),
     scoped_opportunities as (
         select o.id, o.tenant_id, o.created_at, o.closed_at
         from opportunities o
         join new_business_pipelines nbp
             on nbp.pipeline_id = o.pipeline_id
             and nbp.tenant_id = o.tenant_id
     ),
     monthly_open_opportunities as (
         select
             me.month_end_date,
             o.id as opportunity_id,
             (me.month_end_date - o.created_at::date) as age_days
         from month_ends me
         cross join scoped_opportunities o
         where o.created_at::date <= me.month_end_date
             and (o.closed_at is null or o.closed_at::date > me.month_end_date)
     )
     select
         me.month_end_date,
         count(moo.opportunity_id) as opportunities_open_at_month_end,
         round(avg(moo.age_days)::numeric, 1) as avg_age_days_of_open_opportunities_at_month_end,
         max(moo.age_days) as oldest_age_days_of_open_opportunity_at_month_end
     from month_ends me
     left join monthly_open_opportunities moo
         on moo.month_end_date = me.month_end_date
     group by me.month_end_date
     order by me.month_end_date asc

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: month_end_date, opportunities_open_at_month_end, avg_age_days_of_open_opportunities_at_month_end, oldest_age_days_of_open_opportunity_at_month_end

   • GraceThe query compiles and returns exactly the requested grain and columns (36 monthly rows, 2023-01 through 2025-12).
   • Grace calls submit

     with month_ends as (
         select
             year_num,
             month_num,
             max(calendar_date) as month_end_date
         from calendar_days
         where year_num between 2023 and 2025
         group by year_num, month_num
     ),
     scoped_tenants as (
         select id as tenant_id, name
         from tenants
         where name in (
             'Ballycorran Industrial Supply',
             'Castell Fasteners',
             'Hanse Industriebedarf',
             'Midland Safety Wear',
             'Polder Packaging'
         )
     ),
     new_business_pipelines as (
         select p.id as pipeline_id, p.tenant_id
         from pipelines p
         join scoped_tenants st on st.tenant_id = p.tenant_id
         where p.name = 'New business'
     ),
     scoped_opportunities as (
         select o.id, o.tenant_id, o.created_at, o.closed_at
         from opportunities o
         join new_business_pipelines nbp
             on nbp.pipeline_id = o.pipeline_id
             and nbp.tenant_id = o.tenant_id
     ),
     monthly_open_opportunities as (
         select
             me.month_end_date,
             o.id as opportunity_id,
             (me.month_end_date - o.created_at::date) as age_days
         from month_ends me
         cross join scoped_opportunities o
         where o.created_at::date <= me.month_end_date
             and (o.closed_at is null or o.closed_at::date > me.month_end_date)
     )
     select
         me.month_end_date,
         count(moo.opportunity_id) as opportunities_open_at_month_end,
         round(avg(moo.age_days)::numeric, 1) as avg_age_days_of_open_opportunities_at_month_end,
         max(moo.age_days) as oldest_age_days_of_open_opportunity_at_month_end
     from month_ends me
     left join monthly_open_opportunities moo
         on moo.month_end_date = me.month_end_date
     group by me.month_end_date
     order by me.month_end_date asc

   • The warehouse answers

     Submitted to tonight's batch. Your work on this request is done.

   • GraceSubmitted. The report counts open "New business" pipeline opportunities across the five named B2B wholesale tenants at each month-end from 2023-01 through 2025-12, with average and oldest age in days, so Daniel can see whether the current zero-pipeline state is new or has always been the case.

   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 (267 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)