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

Lost B2B Wholesale Deals: When Does Activity Happen Relative to the Deal's Life

“For deals that eventually get lost, does sales activity cluster early, stay steady throughout, or come in a late flurry right before the deal is given up on — or does it go quiet before the end (stalled and forgotten)?”

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 1 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
   Lost B2B Wholesale Deals: When Does Activity Happen Relative to the Deal's Life

   Question

   For deals that eventually get lost, does sales activity cluster early, stay steady throughout, or come in a late flurry right before the deal is given up on — or does it go quiet before the end (stalled and forgotten)?

   One row per

   lifecycle quartile (relative position within each lost opportunity's own created_at-to-closed_at window)

   Columns

   • lifecycle_quartile (Q1 0-25%, Q2 25-50%, Q3 50-75%, Q4 75-100% of the deal's own duration)
   • total_activities_in_bucket
   • pct_of_total_activities
   • opportunities_with_at_least_one_activity_in_bucket
   • pct_of_lost_opportunities_with_activity_in_bucket

4. Ines writes the SQL

   The engineer · 1 dry run on the empty schema, then the submission

   Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)

   WITH scoped_tenants AS (
     SELECT 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.id = p.tenant_id
     WHERE p.name = 'New business'
   ),
   lost_opps AS (
     SELECT o.id AS opportunity_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
     WHERE o.is_closed = true AND o.is_won = false
   ),
   total_lost AS (
     SELECT COUNT(*) AS total_lost_opps FROM lost_opps
   ),
   activity_positions AS (
     SELECT
       a.id AS activity_id,
       lo.opportunity_id,
       CASE
         WHEN lo.closed_at IS NULL OR lo.created_at IS NULL THEN NULL
         WHEN lo.closed_at = lo.created_at THEN NULL
         ELSE EXTRACT(EPOCH FROM (a.occurred_at - lo.created_at)) / EXTRACT(EPOCH FROM (lo.closed_at - lo.created_at))
       END AS relative_position
     FROM activities a
     JOIN lost_opps lo ON lo.opportunity_id = a.opportunity_id AND a.tenant_id = lo.tenant_id
   ),
   bucketed AS (
     SELECT
       activity_id,
       opportunity_id,
       CASE
         WHEN relative_position IS NULL OR relative_position < 0 OR relative_position > 1 THEN 'outside window'
         WHEN relative_position < 0.25 THEN 'Q1 (0-25%)'
         WHEN relative_position < 0.5 THEN 'Q2 (25-50%)'
         WHEN relative_position < 0.75 THEN 'Q3 (50-75%)'
         ELSE 'Q4 (75-100%)'
       END AS lifecycle_quartile
     FROM activity_positions
   ),
   totals AS (
     SELECT COUNT(*) AS total_activities_all FROM bucketed
   )
   SELECT
     b.lifecycle_quartile,
     COUNT(*) AS total_activities_in_bucket,
     ROUND(100.0 * COUNT(*) / t.total_activities_all, 2) AS pct_of_total_activities,
     COUNT(DISTINCT b.opportunity_id) AS opportunities_with_at_least_one_activity_in_bucket,
     ROUND(100.0 * COUNT(DISTINCT b.opportunity_id) / tl.total_lost_opps, 2) AS pct_of_lost_opportunities_with_activity_in_bucket
   FROM bucketed b
   CROSS JOIN totals t
   CROSS JOIN total_lost tl
   GROUP BY b.lifecycle_quartile, t.total_activities_all, tl.total_lost_opps
   ORDER BY CASE b.lifecycle_quartile
     WHEN 'Q1 (0-25%)' THEN 1
     WHEN 'Q2 (25-50%)' THEN 2
     WHEN 'Q3 (50-75%)' THEN 3
     WHEN 'Q4 (75-100%)' THEN 4
     ELSE 5
   END

   “Query compiles cleanly with the expected columns. Submitting.”

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

   “Submitted. The query buckets each lost opportunity's activities into lifecycle quartiles (Q1–Q4) based on their relative position within the deal's own created_at→closed_at window, with an "outside window" catch-all for anomalies (post-close timestamps or zero-duration deals), and reports both activity-share and opportunity-coverage percentages per bucket using a fixed denominator for the latter.”

5. The report comes back overnight

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

   lifecycle_quartile | total_activities_in_bucket | pct_of_total_activities | opportunities_with_at_least_one_activity | pct_of_lost_opportunities_with_activity_
   -------------------+----------------------------+-------------------------+------------------------------------------+-----------------------------------------
   Q1 (0-25%)         |                         12 |                   11.88 |                                       12 |                                     40.0
   Q2 (25-50%)        |                         31 |                   30.69 |                                       30 |                                    100.0
   Q3 (50-75%)        |                         35 |                   34.65 |                                       29 |                                    96.67
   Q4 (75-100%)       |                         23 |                   22.77 |                                       23 |                                    76.67
   (4 rows)

6. What came of it

   The memo and the queries that followed; links found by title, so inferred

   It builds on:

   • q176New Business Only: Won vs Lost Opportunity Characteristics

7. It becomes benchmark query q382

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

   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • numericsdocs/NUMERICS.md: quartile boundaries compared exactly (4 * elapsed < lifetime instead of elapsed / lifetime < 0.25), so an engine that rounds the quotient cannot move an activity across a boundary; lifetimes are positive (closed_at > created_at), so the buckets are the same
   • numericsdocs/NUMERICS.md: quartile boundaries compared exactly (see above)
   • numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6) (2×)
   • made explicita selected t.col gets AS col, its standard name made explicit (6×)
   • placeholderseconds between (2×)
   • 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 scoped_tenants AS (  SELECT 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.id = p.tenant_id  WHERE p.name = 'New business'),lost_opps AS (  SELECT o.id AS opportunity_id, o.tenant_id, o.created_at, o.closed_at  SELECT o.id AS opportunity_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  WHERE o.is_closed = true AND o.is_won = false),total_lost AS (  SELECT COUNT(*) AS total_lost_opps FROM lost_opps),activity_positions AS (  SELECT    a.id AS activity_id,    lo.opportunity_id,    lo.opportunity_id AS opportunity_id,    CASE      WHEN lo.closed_at IS NULL OR lo.created_at IS NULL THEN NULL      WHEN lo.closed_at = lo.created_at THEN NULL      ELSE EXTRACT(EPOCH FROM (a.occurred_at - lo.created_at)) / EXTRACT(EPOCH FROM (lo.closed_at - lo.created_at))    END AS relative_position      ELSE {{seconds_between(lo.created_at, lo.closed_at)}}    END AS lifetime_seconds,    {{seconds_between(lo.created_at, a.occurred_at)}} AS elapsed_seconds  FROM activities a  JOIN lost_opps lo ON lo.opportunity_id = a.opportunity_id AND a.tenant_id = lo.tenant_id),bucketed AS (  SELECT    activity_id,    opportunity_id,    CASE      WHEN relative_position IS NULL OR relative_position < 0 OR relative_position > 1 THEN 'outside window'      WHEN relative_position < 0.25 THEN 'Q1 (0-25%)'      WHEN relative_position < 0.5 THEN 'Q2 (25-50%)'      WHEN relative_position < 0.75 THEN 'Q3 (50-75%)'      WHEN lifetime_seconds IS NULL OR elapsed_seconds IS NULL OR elapsed_seconds < 0 OR elapsed_seconds > lifetime_seconds THEN 'outside window'      WHEN 4 * elapsed_seconds < lifetime_seconds THEN 'Q1 (0-25%)'      WHEN 2 * elapsed_seconds < lifetime_seconds THEN 'Q2 (25-50%)'      WHEN 4 * elapsed_seconds < 3 * lifetime_seconds THEN 'Q3 (50-75%)'      ELSE 'Q4 (75-100%)'    END AS lifecycle_quartile  FROM activity_positions),totals AS (  SELECT COUNT(*) AS total_activities_all FROM bucketed)SELECT  b.lifecycle_quartile,  b.lifecycle_quartile AS lifecycle_quartile,  COUNT(*) AS total_activities_in_bucket,  ROUND(100.0 * COUNT(*) / t.total_activities_all, 2) AS pct_of_total_activities,  100.0 * COUNT(*) / t.total_activities_all AS pct_of_total_activities,  COUNT(DISTINCT b.opportunity_id) AS opportunities_with_at_least_one_activity_in_bucket,  ROUND(100.0 * COUNT(DISTINCT b.opportunity_id) / tl.total_lost_opps, 2) AS pct_of_lost_opportunities_with_activity_in_bucket  100.0 * COUNT(DISTINCT b.opportunity_id) / tl.total_lost_opps AS pct_of_lost_opportunities_with_activity_in_bucketFROM bucketed bCROSS JOIN totals tCROSS JOIN total_lost tlGROUP BY b.lifecycle_quartile, t.total_activities_all, tl.total_lost_oppsORDER BY CASE b.lifecycle_quartile  WHEN 'Q1 (0-25%)' THEN 1  WHEN 'Q2 (25-50%)' THEN 2  WHEN 'Q3 (50-75%)' THEN 3  WHEN 'Q4 (75-100%)' THEN 4  ELSE 5ENDEND NULLS LAST

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

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · DataFusion · Firebolt · clickhouse_brewClickHouse · chDB

   WITH scoped_tenants AS (
     SELECT 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.id = p.tenant_id
     WHERE p.name = 'New business'
   ),
   lost_opps AS (
     SELECT o.id AS opportunity_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
     WHERE o.is_closed = true AND o.is_won = false
   ),
   total_lost AS (
     SELECT COUNT(*) AS total_lost_opps FROM lost_opps
   ),
   activity_positions AS (
     SELECT
       a.id AS activity_id,
       lo.opportunity_id AS opportunity_id,
       CASE
         WHEN lo.closed_at IS NULL OR lo.created_at IS NULL THEN NULL
         WHEN lo.closed_at = lo.created_at THEN NULL
         ELSE CAST(EXTRACT(EPOCH FROM (lo.closed_at - lo.created_at)) AS NUMERIC(18,6))
       END AS lifetime_seconds,
       CAST(EXTRACT(EPOCH FROM (a.occurred_at - lo.created_at)) AS NUMERIC(18,6)) AS elapsed_seconds
     FROM activities a
     JOIN lost_opps lo ON lo.opportunity_id = a.opportunity_id AND a.tenant_id = lo.tenant_id
   ),
   bucketed AS (
     SELECT
       activity_id,
       opportunity_id,
       CASE
         WHEN lifetime_seconds IS NULL OR elapsed_seconds IS NULL OR elapsed_seconds < 0 OR elapsed_seconds > lifetime_seconds THEN 'outside window'
         WHEN 4 * elapsed_seconds < lifetime_seconds THEN 'Q1 (0-25%)'
         WHEN 2 * elapsed_seconds < lifetime_seconds THEN 'Q2 (25-50%)'
         WHEN 4 * elapsed_seconds < 3 * lifetime_seconds THEN 'Q3 (50-75%)'
         ELSE 'Q4 (75-100%)'
       END AS lifecycle_quartile
     FROM activity_positions
   ),
   totals AS (
     SELECT COUNT(*) AS total_activities_all FROM bucketed
   )
   SELECT
     b.lifecycle_quartile AS lifecycle_quartile,
     COUNT(*) AS total_activities_in_bucket,
     100.0 * COUNT(*) / t.total_activities_all AS pct_of_total_activities,
     COUNT(DISTINCT b.opportunity_id) AS opportunities_with_at_least_one_activity_in_bucket,
     100.0 * COUNT(DISTINCT b.opportunity_id) / tl.total_lost_opps AS pct_of_lost_opportunities_with_activity_in_bucket
   FROM bucketed b
   CROSS JOIN totals t
   CROSS JOIN total_lost tl
   GROUP BY b.lifecycle_quartile, t.total_activities_all, tl.total_lost_opps
   ORDER BY CASE b.lifecycle_quartile
     WHEN 'Q1 (0-25%)' THEN 1
     WHEN 'Q2 (25-50%)' THEN 2
     WHEN 'Q3 (50-75%)' THEN 3
     WHEN 'Q4 (75-100%)' THEN 4
     ELSE 5
   END NULLS LAST

   • DataFusionrow 1: col 2: "5203" vs "5194"; col 3: "14.4966704744922125323897355883090468362542" vs "14.47159455016578"; col 4: "5203" vs "5194"

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

8. Everything, as recorded

   The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

   Ines's whole session, 7 turns

   • The request, as Ines received it

     Report request from Daniel Kim (analyst for the Head of B2B Commerce):
     
     Title: Lost B2B Wholesale Deals: When Does Activity Happen Relative to the Deal's Life
     Question: For deals that eventually get lost, does sales activity cluster early, stay steady throughout, or come in a late flurry right before the deal is given up on — or does it go quiet before the end (stalled and forgotten)?
     Grain: one row per lifecycle quartile (relative position within each lost opportunity's own created_at-to-closed_at window)
     Columns:
     - lifecycle_quartile (Q1 0-25%, Q2 25-50%, Q3 50-75%, Q4 75-100% of the deal's own duration)
     - total_activities_in_bucket
     - pct_of_total_activities
     - opportunities_with_at_least_one_activity_in_bucket
     - pct_of_lost_opportunities_with_activity_in_bucket
     Details: Scope: tenants.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging'), pipeline_id = each tenant's 'New business' pipeline, opportunities where is_closed = true and is_won = false. For each such opportunity, join activities where activities.opportunity_id = opportunity.id. For each activity compute relative_position = (activity.occurred_at - opportunity.created_at) / NULLIF(opportunity.closed_at - opportunity.created_at, 0), expressed as a fraction of the deal's own lifespan. Bucket into quartiles: Q1 [0, 0.25), Q2 [0.25, 0.5), Q3 [0.5, 0.75), Q4 [0.75, 1.0] inclusive of activities right at closed_at. If any activity falls outside [0,1] (e.g. logged after closed_at, or the opportunity has zero duration), put it in a 5th 'outside window' bucket rather than discarding it, and flag how many rows that affects. total_activities_in_bucket = count of activities in that bucket; pct_of_total_activities = that count / total activities across all lost opportunities in scope; opportunities_with_at_least_one_activity_in_bucket = distinct opportunity count; pct_of_lost_opportunities_with_activity_in_bucket = that count / total lost opportunities in scope (a fixed denominator across rows). Order Q1 through Q4, then 'outside window' last if present.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Ines calls dry_run

     WITH scoped_tenants AS (
       SELECT 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.id = p.tenant_id
       WHERE p.name = 'New business'
     ),
     lost_opps AS (
       SELECT o.id AS opportunity_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
       WHERE o.is_closed = true AND o.is_won = false
     ),
     total_lost AS (
       SELECT COUNT(*) AS total_lost_opps FROM lost_opps
     ),
     activity_positions AS (
       SELECT
         a.id AS activity_id,
         lo.opportunity_id,
         CASE
           WHEN lo.closed_at IS NULL OR lo.created_at IS NULL THEN NULL
           WHEN lo.closed_at = lo.created_at THEN NULL
           ELSE EXTRACT(EPOCH FROM (a.occurred_at - lo.created_at)) / EXTRACT(EPOCH FROM (lo.closed_at - lo.created_at))
         END AS relative_position
       FROM activities a
       JOIN lost_opps lo ON lo.opportunity_id = a.opportunity_id AND a.tenant_id = lo.tenant_id
     ),
     bucketed AS (
       SELECT
         activity_id,
         opportunity_id,
         CASE
           WHEN relative_position IS NULL OR relative_position < 0 OR relative_position > 1 THEN 'outside window'
           WHEN relative_position < 0.25 THEN 'Q1 (0-25%)'
           WHEN relative_position < 0.5 THEN 'Q2 (25-50%)'
           WHEN relative_position < 0.75 THEN 'Q3 (50-75%)'
           ELSE 'Q4 (75-100%)'
         END AS lifecycle_quartile
       FROM activity_positions
     ),
     totals AS (
       SELECT COUNT(*) AS total_activities_all FROM bucketed
     )
     SELECT
       b.lifecycle_quartile,
       COUNT(*) AS total_activities_in_bucket,
       ROUND(100.0 * COUNT(*) / t.total_activities_all, 2) AS pct_of_total_activities,
       COUNT(DISTINCT b.opportunity_id) AS opportunities_with_at_least_one_activity_in_bucket,
       ROUND(100.0 * COUNT(DISTINCT b.opportunity_id) / tl.total_lost_opps, 2) AS pct_of_lost_opportunities_with_activity_in_bucket
     FROM bucketed b
     CROSS JOIN totals t
     CROSS JOIN total_lost tl
     GROUP BY b.lifecycle_quartile, t.total_activities_all, tl.total_lost_opps
     ORDER BY CASE b.lifecycle_quartile
       WHEN 'Q1 (0-25%)' THEN 1
       WHEN 'Q2 (25-50%)' THEN 2
       WHEN 'Q3 (50-75%)' THEN 3
       WHEN 'Q4 (75-100%)' THEN 4
       ELSE 5
     END

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: lifecycle_quartile, total_activities_in_bucket, pct_of_total_activities, opportunities_with_at_least_one_activity_in_bucket, pct_of_lost_opportunities_with_activity_in_bucket

   • InesQuery compiles cleanly with the expected columns. Submitting.
   • Ines calls submit

     WITH scoped_tenants AS (
       SELECT 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.id = p.tenant_id
       WHERE p.name = 'New business'
     ),
     lost_opps AS (
       SELECT o.id AS opportunity_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
       WHERE o.is_closed = true AND o.is_won = false
     ),
     total_lost AS (
       SELECT COUNT(*) AS total_lost_opps FROM lost_opps
     ),
     activity_positions AS (
       SELECT
         a.id AS activity_id,
         lo.opportunity_id,
         CASE
           WHEN lo.closed_at IS NULL OR lo.created_at IS NULL THEN NULL
           WHEN lo.closed_at = lo.created_at THEN NULL
           ELSE EXTRACT(EPOCH FROM (a.occurred_at - lo.created_at)) / EXTRACT(EPOCH FROM (lo.closed_at - lo.created_at))
         END AS relative_position
       FROM activities a
       JOIN lost_opps lo ON lo.opportunity_id = a.opportunity_id AND a.tenant_id = lo.tenant_id
     ),
     bucketed AS (
       SELECT
         activity_id,
         opportunity_id,
         CASE
           WHEN relative_position IS NULL OR relative_position < 0 OR relative_position > 1 THEN 'outside window'
           WHEN relative_position < 0.25 THEN 'Q1 (0-25%)'
           WHEN relative_position < 0.5 THEN 'Q2 (25-50%)'
           WHEN relative_position < 0.75 THEN 'Q3 (50-75%)'
           ELSE 'Q4 (75-100%)'
         END AS lifecycle_quartile
       FROM activity_positions
     ),
     totals AS (
       SELECT COUNT(*) AS total_activities_all FROM bucketed
     )
     SELECT
       b.lifecycle_quartile,
       COUNT(*) AS total_activities_in_bucket,
       ROUND(100.0 * COUNT(*) / t.total_activities_all, 2) AS pct_of_total_activities,
       COUNT(DISTINCT b.opportunity_id) AS opportunities_with_at_least_one_activity_in_bucket,
       ROUND(100.0 * COUNT(DISTINCT b.opportunity_id) / tl.total_lost_opps, 2) AS pct_of_lost_opportunities_with_activity_in_bucket
     FROM bucketed b
     CROSS JOIN totals t
     CROSS JOIN total_lost tl
     GROUP BY b.lifecycle_quartile, t.total_activities_all, tl.total_lost_opps
     ORDER BY CASE b.lifecycle_quartile
       WHEN 'Q1 (0-25%)' THEN 1
       WHEN 'Q2 (25-50%)' THEN 2
       WHEN 'Q3 (50-75%)' THEN 3
       WHEN 'Q4 (75-100%)' THEN 4
       ELSE 5
     END

   • The warehouse answers

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

   • InesSubmitted. The query buckets each lost opportunity's activities into lifecycle quartiles (Q1–Q4) based on their relative position within the deal's own created_at→closed_at window, with an "outside window" catch-all for anomalies (post-close timestamps or zero-duration deals), and reports both activity-share and opportunity-coverage percentages per bucket using a fixed denominator for the latter.

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