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

New Business Only: Deal Cycle Time Trend by Month

“Restricted to genuine new-customer acquisition deals (excluding renewals), how long does it take to close an opportunity, and is that cycle time trending up or down over the last three years?”

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

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
Patrick O'Neill writes to Daniel Kim
The manager, at the start of week 1
Note to Daniel Kim — B2B pipeline health

Daniel,

Samuel and I need to give the B2B wholesale merchants (and our own leadership) a clear-eyed view of why their sales teams are struggling to close business. This feeds a joint recommendation Samuel and I are making next week on where to invest — better pipeline coaching/tooling for merchants vs. changes to how we structure stages, quotes and terms in the product. So I want the picture to be accurate more than exhaustive — pick your battles across the nights you have.

Here's what I actually want to know, in plain terms:
1. Where do deals die? Across the wholesale merchants, at what point in the journey from raw lead to signed deal do we lose the most opportunities? I want to know which stage is the graveyard, not just an overall conversion number.
2. How long does it take to close, and is that getting better or worse? From a lead first showing up to a deal actually closing (won or lost), what's the typical cycle time, and has it been stretching out or shrinking over recent months?
3. What separates a won deal from a lost one? Things like deal size, how many people/touches were involved before close, which rep or team owned it, or how old the opportunity got before it closed — anything that reliably shows up more on the winning side versus the losing side.
4. Are some merchants or reps clearly struggling more than others? I'd like a sense of whether this is a widespread pattern across our B2B merchants or concentrated in a handful of accounts/teams, since that changes whether this is a product fix or a coaching fix.
5. (If there's room) Does deal size or payment terms correlate with win rate or speed? Wholesalers care a lot about credit terms — I'm curious if longer terms or larger contract values are dragging out or killing deals.
Priority order is 1, 2, 3 first — those directly shape the recommendation. 4 and 5 are valuable but can slip if you run out of slots this week.

Keep each report to one clean question — I'd rather have four sharp answers than one crowded one. Flag anything that looks like a data quality issue rather than a real trend.

Thanks — looking forward to the Friday memo.

Patrick
A note from the Head of Merchant Success, Samuel Whitcombe
Subject: A few questions from the Merchant Success side

Hi Daniel,

Good to see this getting attention this week. A few things I'd love your analysis to help answer, from the merchant-health angle:
1. For our B2B wholesaler merchants specifically, are deals actually stalling at a particular stage, or is it more that fewer leads come in to begin with? I want to know where the real friction sits before our QBRs.
2. Among the accounts that do close, is there anything about them (size, how engaged they were beforehand, how long the courtship took) that separates the wins from the losses? I'm trying to understand whether our biggest merchants' sales teams are losing winnable deals or just working less promising ones.
3. Is there a relationship between how long a deal has been open and its odds of closing at all — i.e. do stale pipelines quietly become risk, or do they usually still land eventually?
Happy to compare notes before you finalize anything for Patrick.

Best, Samuel
Daniel Kim plans the night
The analyst · week 1, night 2, request 3 of 3
Night 1's big finding is a data-model gotcha, not yet a business trend: every wholesale tenant's 'New business' pipeline shows zero opportunities (open or closed) at Prospecting/Qualification/Proposal/Negotiation — closed deals only ever show as the terminal Closed Won/Closed Lost stage. That means current stage_id can't answer 'where do deals die' for lost opportunities; it only tells us the final outcome. It also means Report 3 (won/lost) and Report 2 (cycle time) from night 1 silently pooled two very different populations together: 'New business' (true lead-to-signed-deal acquisition, which is what Patrick and Samuel are actually asking about) and 'Renewals' (existing-customer renew/churn, where 'Renewed' carries a 1.0 default win probability and is a fundamentally easier motion). Tonight I'm spending all three slots fixing this at the root: (1) reconstruct the real graveyard stage from change_history for New-business-only lost opportunities, (2) redo the won/lost characteristics comparison scoped to New business only, (3) redo the cycle-time trend scoped to New business only. This keeps priorities 1-2-3 intact but makes them actually answer the question Patrick asked, rather than a renewals-diluted version of it. I'm holding Samuel's stale-pipeline-risk question and Patrick's #4/#5 for night 3, once I see whether change_history actually has usable coverage for the graveyard question (flagged as a risk in request 1) and whether the split reports change the picture.
New Business Only: Deal Cycle Time Trend by Month
Question
Restricted to genuine new-customer acquisition deals (excluding renewals), how long does it take to close an opportunity, and is that cycle time trending up or down over the last three years?
One row per
calendar month (based on closed_at month), New business pipeline only
Columns
- month (YYYY-MM)
- opportunities_closed (count)
- closed_won (count)
- closed_lost (count)
- win_rate_pct
- avg_cycle_days (closed_at - created_at)
- median_cycle_days (closed_at - created_at)

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 tenant_scope AS (
  SELECT id AS tenant_id
  FROM tenants
  WHERE industry ILIKE '%industrial%'
     OR industry ILIKE '%office equipment%'
     OR industry ILIKE '%wholesal%'
),
new_business_pipelines AS (
  SELECT tenant_id, id AS pipeline_id
  FROM pipelines
  WHERE name = 'New business'
),
scoped_opportunities AS (
  SELECT
    o.id,
    o.is_won,
    o.created_at,
    o.closed_at
  FROM opportunities o
  JOIN tenant_scope ts ON ts.tenant_id = o.tenant_id
  JOIN new_business_pipelines nbp
    ON nbp.tenant_id = o.tenant_id
   AND nbp.pipeline_id = o.pipeline_id
  WHERE o.is_closed = true
    AND o.closed_at IS NOT NULL
    AND o.closed_at >= DATE '2023-01-01'
    AND o.closed_at < DATE '2026-01-01'
),
opportunities_with_month AS (
  SELECT
    so.id,
    so.is_won,
    cd.month_label,
    EXTRACT(EPOCH FROM (so.closed_at - so.created_at)) / 86400.0 AS cycle_days
  FROM scoped_opportunities so
  JOIN calendar_days cd ON cd.calendar_date = CAST(so.closed_at AS DATE)
),
monthly_stats AS (
  SELECT
    month_label,
    COUNT(*) AS opportunities_closed,
    COUNT(*) FILTER (WHERE is_won) AS closed_won,
    COUNT(*) FILTER (WHERE NOT is_won) AS closed_lost,
    AVG(cycle_days) AS avg_cycle_days_raw,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cycle_days) AS median_cycle_days_raw
  FROM opportunities_with_month
  GROUP BY month_label
)
SELECT
  month_label AS month,
  opportunities_closed,
  closed_won,
  closed_lost,
  ROUND(100.0 * closed_won / NULLIF(opportunities_closed, 0), 1) AS win_rate_pct,
  ROUND(avg_cycle_days_raw::numeric, 1) AS avg_cycle_days,
  ROUND(median_cycle_days_raw::numeric, 1) AS median_cycle_days
FROM monthly_stats
ORDER BY month ASC

“Query compiles and matches the requested grain and columns.”

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

“Submitted. The report groups new-business-pipeline opportunities (tenant industry filter + pipeline name = 'New business') by closed_at month, with counts, win rate, and average/median cycle time in days, rounded to 1 decimal, ordered chronologically from 2023-01 through 2025-12.”

The report comes back overnight

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

month   | opportunities_closed | closed_won | closed_lost | win_rate_pct | avg_cycle_days | median_cycle_days
--------+----------------------+------------+-------------+--------------+----------------+------------------
2023-01 |                    3 |          3 |           0 |        100.0 |           14.0 |              16.0
2023-02 |                    4 |          4 |           0 |        100.0 |           11.4 |               9.6
2023-03 |                    5 |          3 |           2 |         60.0 |           15.7 |              17.0
2023-04 |                    2 |          2 |           0 |        100.0 |            3.0 |               3.0
2023-05 |                    2 |          1 |           1 |         50.0 |           23.3 |              23.3
2023-06 |                    3 |          2 |           1 |         66.7 |           19.1 |              26.3
2023-07 |                    1 |          1 |           0 |        100.0 |           30.4 |              30.4
2023-08 |                    2 |          2 |           0 |        100.0 |           14.8 |              14.8
2023-09 |                    2 |          1 |           1 |         50.0 |           21.6 |              21.6
2023-10 |                    2 |          2 |           0 |        100.0 |           19.7 |              19.7
2023-11 |                    7 |          5 |           2 |         71.4 |           19.7 |              18.1
2023-12 |                    3 |          2 |           1 |         66.7 |           14.9 |              12.7
2024-01 |                    8 |          8 |           0 |        100.0 |           13.0 |              12.6
2024-02 |                    4 |          4 |           0 |        100.0 |            8.6 |               8.5
2024-03 |                    3 |          3 |           0 |        100.0 |           18.8 |              14.0
2024-04 |                    3 |          3 |           0 |        100.0 |           11.0 |              13.0
2024-05 |                    8 |          6 |           2 |         75.0 |           22.7 |              20.4
2024-07 |                    5 |          2 |           3 |         40.0 |           33.7 |              31.0
2024-08 |                    3 |          3 |           0 |        100.0 |           12.6 |              14.5
2024-09 |                    2 |          2 |           0 |        100.0 |           21.6 |              21.6
2024-10 |                    7 |          6 |           1 |         85.7 |           17.2 |              15.0
2024-11 |                    7 |          3 |           4 |         42.9 |           23.1 |              17.0
2024-12 |                    4 |          1 |           3 |         25.0 |           29.6 |              30.9
2025-01 |                    3 |          2 |           1 |         66.7 |           17.2 |              15.3
2025-02 |                    2 |          1 |           1 |         50.0 |           34.1 |              34.1
2025-03 |                    4 |          3 |           1 |         75.0 |           14.9 |              14.6
2025-04 |                    6 |          5 |           1 |         83.3 |           18.4 |              18.6
2025-05 |                    3 |          3 |           0 |        100.0 |           13.7 |              11.8
2025-06 |                    2 |          2 |           0 |        100.0 |            6.5 |               6.5
2025-07 |                    5 |          5 |           0 |        100.0 |           12.5 |               9.3
2025-08 |                    2 |          2 |           0 |        100.0 |            5.0 |               5.0
2025-09 |                    5 |          4 |           1 |         80.0 |           13.7 |              14.7
2025-10 |                    5 |          4 |           1 |         80.0 |           23.6 |              14.0
2025-11 |                    7 |          5 |           2 |         71.4 |           20.5 |              21.0
2025-12 |                    2 |          1 |           1 |         50.0 |           30.9 |              30.9
(35 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
- q180B2B Wholesale: Age Profile of Currently Open New-Business Pipeline
It builds on:
- q173B2B Wholesale Deal Cycle Time Trend by Month

It becomes benchmark query q177

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

spellingcast (2×)
numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed
numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6)
made explicita selected t.col gets AS col, its standard name made explicit (7×)
placeholderpercentile cont
placeholderseconds between
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 tenant_scope AS (  SELECT id AS tenant_id  FROM tenants  WHERE industry ILIKE '%industrial%'     OR industry ILIKE '%office equipment%'     OR industry ILIKE '%wholesal%'),new_business_pipelines AS (  SELECT tenant_id, id AS pipeline_id  FROM pipelines  WHERE name = 'New business'),scoped_opportunities AS (  SELECT    o.id,    o.is_won,    o.created_at,    o.closed_at    o.id AS id,    o.is_won AS is_won,    o.created_at AS created_at,    o.closed_at AS closed_at  FROM opportunities o  JOIN tenant_scope ts ON ts.tenant_id = o.tenant_id  JOIN new_business_pipelines nbp    ON nbp.tenant_id = o.tenant_id   AND nbp.pipeline_id = o.pipeline_id  WHERE o.is_closed = true    AND o.closed_at IS NOT NULL    AND o.closed_at >= DATE '2023-01-01'    AND o.closed_at < DATE '2026-01-01'),opportunities_with_month AS (  SELECT    so.id,    so.is_won,    cd.month_label,    EXTRACT(EPOCH FROM (so.closed_at - so.created_at)) / 86400.0 AS cycle_days    so.id AS id,    so.is_won AS is_won,    cd.month_label AS month_label,    {{seconds_between(so.created_at, so.closed_at)}} / 86400.0 AS cycle_days  FROM scoped_opportunities so  JOIN calendar_days cd ON cd.calendar_date = CAST(so.closed_at AS DATE)),monthly_stats AS (  SELECT    month_label,    COUNT(*) AS opportunities_closed,    COUNT(*) FILTER (WHERE is_won) AS closed_won,    COUNT(*) FILTER (WHERE NOT is_won) AS closed_lost,    AVG(cycle_days) AS avg_cycle_days_raw,    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cycle_days) AS median_cycle_days_raw    {{percentile_cont(0.5, cycle_days)}} AS median_cycle_days_raw  FROM opportunities_with_month  GROUP BY month_label)SELECT  month_label AS month,  opportunities_closed,  closed_won,  closed_lost,  ROUND(100.0 * closed_won / NULLIF(opportunities_closed, 0), 1) AS win_rate_pct,  ROUND(avg_cycle_days_raw::numeric, 1) AS avg_cycle_days,  ROUND(median_cycle_days_raw::numeric, 1) AS median_cycle_days  100.0 * closed_won / NULLIF(opportunities_closed, 0) AS win_rate_pct,  avg_cycle_days_raw AS avg_cycle_days,  median_cycle_days_raw AS median_cycle_daysFROM monthly_statsORDER BY month ASCORDER BY month ASC NULLS LAST

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

WITH tenant_scope AS (
  SELECT id AS tenant_id
  FROM tenants
  WHERE industry ILIKE '%industrial%'
     OR industry ILIKE '%office equipment%'
     OR industry ILIKE '%wholesal%'
),
new_business_pipelines AS (
  SELECT tenant_id, id AS pipeline_id
  FROM pipelines
  WHERE name = 'New business'
),
scoped_opportunities AS (
  SELECT
    o.id AS id,
    o.is_won AS is_won,
    o.created_at AS created_at,
    o.closed_at AS closed_at
  FROM opportunities o
  JOIN tenant_scope ts ON ts.tenant_id = o.tenant_id
  JOIN new_business_pipelines nbp
    ON nbp.tenant_id = o.tenant_id
   AND nbp.pipeline_id = o.pipeline_id
  WHERE o.is_closed = true
    AND o.closed_at IS NOT NULL
    AND o.closed_at >= DATE '2023-01-01'
    AND o.closed_at < DATE '2026-01-01'
),
opportunities_with_month AS (
  SELECT
    so.id AS id,
    so.is_won AS is_won,
    cd.month_label AS month_label,
    CAST(EXTRACT(EPOCH FROM (so.closed_at - so.created_at)) AS NUMERIC(18,6)) / 86400.0 AS cycle_days
  FROM scoped_opportunities so
  JOIN calendar_days cd ON cd.calendar_date = CAST(so.closed_at AS DATE)
),
monthly_stats AS (
  SELECT
    month_label,
    COUNT(*) AS opportunities_closed,
    COUNT(*) FILTER (WHERE is_won) AS closed_won,
    COUNT(*) FILTER (WHERE NOT is_won) AS closed_lost,
    AVG(cycle_days) AS avg_cycle_days_raw,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cycle_days) AS median_cycle_days_raw
  FROM opportunities_with_month
  GROUP BY month_label
)
SELECT
  month_label AS month,
  opportunities_closed,
  closed_won,
  closed_lost,
  100.0 * closed_won / NULLIF(opportunities_closed, 0) AS win_rate_pct,
  avg_cycle_days_raw AS avg_cycle_days,
  median_cycle_days_raw AS median_cycle_days
FROM monthly_stats
ORDER BY month ASC NULLS LAST

WITH tenant_scope AS (
  SELECT id AS tenant_id
  FROM tenants
  WHERE industry ILIKE '%industrial%'
     OR industry ILIKE '%office equipment%'
     OR industry ILIKE '%wholesal%'
),
new_business_pipelines AS (
  SELECT tenant_id, id AS pipeline_id
  FROM pipelines
  WHERE name = 'New business'
),
scoped_opportunities AS (
  SELECT
    o.id AS id,
    o.is_won AS is_won,
    o.created_at AS created_at,
    o.closed_at AS closed_at
  FROM opportunities o
  JOIN tenant_scope ts ON ts.tenant_id = o.tenant_id
  JOIN new_business_pipelines nbp
    ON nbp.tenant_id = o.tenant_id
   AND nbp.pipeline_id = o.pipeline_id
  WHERE o.is_closed = true
    AND o.closed_at IS NOT NULL
    AND o.closed_at >= DATE '2023-01-01'
    AND o.closed_at < DATE '2026-01-01'
),
opportunities_with_month AS (
  SELECT
    so.id AS id,
    so.is_won AS is_won,
    cd.month_label AS month_label,
    CAST(EXTRACT(EPOCH FROM (so.closed_at - so.created_at)) AS NUMERIC(18,6)) / 86400.0 AS cycle_days
  FROM scoped_opportunities so
  JOIN calendar_days cd ON cd.calendar_date = CAST(so.closed_at AS DATE)
),
monthly_stats AS (
  SELECT
    month_label,
    COUNT(*) AS opportunities_closed,
    COUNT(*) FILTER (WHERE is_won) AS closed_won,
    COUNT(*) FILTER (WHERE NOT is_won) AS closed_lost,
    AVG(cycle_days) AS avg_cycle_days_raw,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(cycle_days AS DOUBLE PRECISION)) AS median_cycle_days_raw
  FROM opportunities_with_month
  GROUP BY month_label
)
SELECT
  month_label AS month,
  opportunities_closed,
  closed_won,
  closed_lost,
  100.0 * closed_won / NULLIF(opportunities_closed, 0) AS win_rate_pct,
  avg_cycle_days_raw AS avg_cycle_days,
  median_cycle_days_raw AS median_cycle_days
FROM monthly_stats
ORDER BY month ASC NULLS LAST

WITH tenant_scope AS (
  SELECT id AS tenant_id
  FROM tenants
  WHERE industry ILIKE '%industrial%'
     OR industry ILIKE '%office equipment%'
     OR industry ILIKE '%wholesal%'
),
new_business_pipelines AS (
  SELECT tenant_id, id AS pipeline_id
  FROM pipelines
  WHERE name = 'New business'
),
scoped_opportunities AS (
  SELECT
    o.id AS id,
    o.is_won AS is_won,
    o.created_at AS created_at,
    o.closed_at AS closed_at
  FROM opportunities o
  JOIN tenant_scope ts ON ts.tenant_id = o.tenant_id
  JOIN new_business_pipelines nbp
    ON nbp.tenant_id = o.tenant_id
   AND nbp.pipeline_id = o.pipeline_id
  WHERE o.is_closed = true
    AND o.closed_at IS NOT NULL
    AND o.closed_at >= DATE '2023-01-01'
    AND o.closed_at < DATE '2026-01-01'
),
opportunities_with_month AS (
  SELECT
    so.id AS id,
    so.is_won AS is_won,
    cd.month_label AS month_label,
    CAST(divideDecimal(toDecimal128(dateDiff('microsecond', so.created_at, so.closed_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0 AS cycle_days
  FROM scoped_opportunities so
  JOIN calendar_days cd ON cd.calendar_date = CAST(so.closed_at AS DATE)
),
monthly_stats AS (
  SELECT
    month_label,
    COUNT(*) AS opportunities_closed,
    COUNT(*) FILTER (WHERE is_won) AS closed_won,
    COUNT(*) FILTER (WHERE NOT is_won) AS closed_lost,
    AVG(cycle_days) AS avg_cycle_days_raw,
    quantileExactInclusive(0.5)(toFloat64(cycle_days)) AS median_cycle_days_raw
  FROM opportunities_with_month
  GROUP BY month_label
)
SELECT
  month_label AS month,
  opportunities_closed,
  closed_won,
  closed_lost,
  100.0 * closed_won / NULLIF(opportunities_closed, 0) AS win_rate_pct,
  avg_cycle_days_raw AS avg_cycle_days,
  median_cycle_days_raw AS median_cycle_days
FROM monthly_stats
ORDER BY month ASC NULLS LAST

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

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: New Business Only: Deal Cycle Time Trend by Month
Question: Restricted to genuine new-customer acquisition deals (excluding renewals), how long does it take to close an opportunity, and is that cycle time trending up or down over the last three years?
Grain: one row per calendar month (based on closed_at month), New business pipeline only
Columns:
- month (YYYY-MM)
- opportunities_closed (count)
- closed_won (count)
- closed_lost (count)
- win_rate_pct
- avg_cycle_days (closed_at - created_at)
- median_cycle_days (closed_at - created_at)
Details: Re-runs last night's cycle-time trend but adds pipeline_id = the tenant's 'New business' pipeline (join pipelines on name = 'New business') to the filter, for the same reason as the won/lost report: pooling in Renewals (which likely close faster and win far more often, given 'Renewed' has a 1.0 win_probability) could be masking or distorting the true acquisition-cycle trend. Same tenant scope (industry ILIKE '%industrial%' OR '%office equipment%' OR '%wholesal%'), opportunities where is_closed = true and closed_at is not null, full history 2023-01-01 through 2025-12-31, grouped by month of closed_at (calendar_days.month_label or equivalent). Round avg/median cycle days to 1 decimal. Order by month ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.

Grace calls dry_run

WITH tenant_scope AS (
  SELECT id AS tenant_id
  FROM tenants
  WHERE industry ILIKE '%industrial%'
     OR industry ILIKE '%office equipment%'
     OR industry ILIKE '%wholesal%'
),
new_business_pipelines AS (
  SELECT tenant_id, id AS pipeline_id
  FROM pipelines
  WHERE name = 'New business'
),
scoped_opportunities AS (
  SELECT
    o.id,
    o.is_won,
    o.created_at,
    o.closed_at
  FROM opportunities o
  JOIN tenant_scope ts ON ts.tenant_id = o.tenant_id
  JOIN new_business_pipelines nbp
    ON nbp.tenant_id = o.tenant_id
   AND nbp.pipeline_id = o.pipeline_id
  WHERE o.is_closed = true
    AND o.closed_at IS NOT NULL
    AND o.closed_at >= DATE '2023-01-01'
    AND o.closed_at < DATE '2026-01-01'
),
opportunities_with_month AS (
  SELECT
    so.id,
    so.is_won,
    cd.month_label,
    EXTRACT(EPOCH FROM (so.closed_at - so.created_at)) / 86400.0 AS cycle_days
  FROM scoped_opportunities so
  JOIN calendar_days cd ON cd.calendar_date = CAST(so.closed_at AS DATE)
),
monthly_stats AS (
  SELECT
    month_label,
    COUNT(*) AS opportunities_closed,
    COUNT(*) FILTER (WHERE is_won) AS closed_won,
    COUNT(*) FILTER (WHERE NOT is_won) AS closed_lost,
    AVG(cycle_days) AS avg_cycle_days_raw,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cycle_days) AS median_cycle_days_raw
  FROM opportunities_with_month
  GROUP BY month_label
)
SELECT
  month_label AS month,
  opportunities_closed,
  closed_won,
  closed_lost,
  ROUND(100.0 * closed_won / NULLIF(opportunities_closed, 0), 1) AS win_rate_pct,
  ROUND(avg_cycle_days_raw::numeric, 1) AS avg_cycle_days,
  ROUND(median_cycle_days_raw::numeric, 1) AS median_cycle_days
FROM monthly_stats
ORDER BY month ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, opportunities_closed, closed_won, closed_lost, win_rate_pct, avg_cycle_days, median_cycle_days

GraceQuery compiles and matches the requested grain and columns.

Grace calls submit

WITH tenant_scope AS (
  SELECT id AS tenant_id
  FROM tenants
  WHERE industry ILIKE '%industrial%'
     OR industry ILIKE '%office equipment%'
     OR industry ILIKE '%wholesal%'
),
new_business_pipelines AS (
  SELECT tenant_id, id AS pipeline_id
  FROM pipelines
  WHERE name = 'New business'
),
scoped_opportunities AS (
  SELECT
    o.id,
    o.is_won,
    o.created_at,
    o.closed_at
  FROM opportunities o
  JOIN tenant_scope ts ON ts.tenant_id = o.tenant_id
  JOIN new_business_pipelines nbp
    ON nbp.tenant_id = o.tenant_id
   AND nbp.pipeline_id = o.pipeline_id
  WHERE o.is_closed = true
    AND o.closed_at IS NOT NULL
    AND o.closed_at >= DATE '2023-01-01'
    AND o.closed_at < DATE '2026-01-01'
),
opportunities_with_month AS (
  SELECT
    so.id,
    so.is_won,
    cd.month_label,
    EXTRACT(EPOCH FROM (so.closed_at - so.created_at)) / 86400.0 AS cycle_days
  FROM scoped_opportunities so
  JOIN calendar_days cd ON cd.calendar_date = CAST(so.closed_at AS DATE)
),
monthly_stats AS (
  SELECT
    month_label,
    COUNT(*) AS opportunities_closed,
    COUNT(*) FILTER (WHERE is_won) AS closed_won,
    COUNT(*) FILTER (WHERE NOT is_won) AS closed_lost,
    AVG(cycle_days) AS avg_cycle_days_raw,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cycle_days) AS median_cycle_days_raw
  FROM opportunities_with_month
  GROUP BY month_label
)
SELECT
  month_label AS month,
  opportunities_closed,
  closed_won,
  closed_lost,
  ROUND(100.0 * closed_won / NULLIF(opportunities_closed, 0), 1) AS win_rate_pct,
  ROUND(avg_cycle_days_raw::numeric, 1) AS avg_cycle_days,
  ROUND(median_cycle_days_raw::numeric, 1) AS median_cycle_days
FROM monthly_stats
ORDER BY month ASC

The warehouse answers

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

GraceSubmitted. The report groups new-business-pipeline opportunities (tenant industry filter + pipeline name = 'New business') by closed_at month, with counts, win rate, and average/median cycle time in days, rounded to 1 decimal, ordered chronologically from 2023-01 through 2025-12.

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 (7 KB) · the night's reports (7 KB) · the week's memo (3 KB)

New Business Only: Deal Cycle Time Trend by Month

A business event lands on the Head of B2B Commerce's desk

Patrick O'Neill writes to Daniel Kim

Daniel Kim plans the night

Grace writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q177

Everything, as recorded