Queries · q174 · Head of B2B Commerce · week 1, night 1

B2B Wholesale Opportunities: Won vs Lost Characteristics

“Among closed B2B wholesale opportunities, what reliably differs between the ones we won and the ones we lost — deal size, touches/activities before close, days open, or how concentrated they are among accounts and reps?”

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

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 1, request 3 of 3
Night 1: going straight at Patrick's top three priorities (graveyard stage, cycle-time trend, won-vs-lost drivers), which also cover most of Samuel's three questions (stage friction, what separates wins/losses, implicitly sets up stale-pipeline-risk follow-up for night 2). All three reports scope to B2B wholesale tenants via an industry-text heuristic (industrial supplies / office equipment / wholesale) since there's no explicit 'B2B wholesaler' flag on tenants — I've asked the report to surface the merchant list so I can sanity-check the filter caught the right handful of tenants before going further. Kept each report to one grain: (tenant x stage) for the funnel, (month) for cycle time, and (won/lost) for the comparison, per the one-question-one-report rule. Held back Samuel's 'stale pipeline = risk' question and Patrick's #4 (merchant/rep dispersion) and #5 (terms/size correlation) for later nights once I see where the real friction shows up — no point spending slots on dispersion or terms analysis if the stage report shows everyone dying at the same place for the same reason.
B2B Wholesale Opportunities: Won vs Lost Characteristics
Question
Among closed B2B wholesale opportunities, what reliably differs between the ones we won and the ones we lost — deal size, touches/activities before close, days open, or how concentrated they are among accounts and reps?
One row per
closed outcome (won or lost)
Columns
- outcome (won / lost)
- opportunities (count)
- average amount in EUR
- median amount in EUR
- average activities per opportunity before close
- average days open (closed_at - created_at)
- distinct accounts involved
- distinct owning reps (owner_user_id) involved

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 b2b_tenants AS (
  SELECT id FROM tenants
  WHERE industry ILIKE '%industrial%' OR industry ILIKE '%office equipment%' OR industry ILIKE '%wholesal%'
),
closed_opps AS (
  SELECT o.id, o.tenant_id, o.account_id, o.owner_user_id, o.is_won, o.closed_at, o.created_at,
    o.amount,
    o.currency_code,
    CASE WHEN o.currency_code = 'EUR' THEN o.amount
         ELSE o.amount / (
           SELECT er.rate FROM exchange_rates er
           WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code
             AND er.as_of <= o.closed_at::date
           ORDER BY er.as_of DESC LIMIT 1
         )
    END AS amount_eur
  FROM opportunities o
  JOIN b2b_tenants t ON t.id = o.tenant_id
  WHERE o.is_closed = true AND o.closed_at IS NOT NULL
),
activity_counts AS (
  SELECT co.id AS opportunity_id, COUNT(a.id) AS activity_count
  FROM closed_opps co
  LEFT JOIN activities a ON a.opportunity_id = co.id AND a.occurred_at <= co.closed_at
  GROUP BY co.id
)
SELECT
  CASE WHEN co.is_won THEN 'won' ELSE 'lost' END AS outcome,
  COUNT(*) AS opportunities,
  ROUND(AVG(co.amount_eur), 2) AS avg_amount_eur,
  ROUND(CAST(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY co.amount_eur) AS numeric), 2) AS median_amount_eur,
  ROUND(AVG(ac.activity_count), 2) AS avg_activities_before_close,
  ROUND(CAST(AVG(EXTRACT(EPOCH FROM (co.closed_at - co.created_at)) / 86400.0) AS numeric), 1) AS avg_days_open,
  COUNT(DISTINCT co.account_id) AS distinct_accounts,
  COUNT(DISTINCT co.owner_user_id) AS distinct_reps
FROM closed_opps co
JOIN activity_counts ac ON ac.opportunity_id = co.id
GROUP BY co.is_won
ORDER BY co.is_won DESC

“Query compiles and returns exactly the requested columns and grain. Submitting.”

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

“Submitted. Summary of the approach: - Scoped to B2B wholesale tenants by industry pattern match, then to opportunities with is_closed = true, split into won/lost by is_won. - Converted each opportunity's amount to EUR using the exchange_rates rate (base EUR → opportunity currency) as of closed_at's date, falling back to the nearest prior available date to handle weekends/gaps. - Computed per-opportunity activity counts (activities linked by opportunity_id with occurred_at <= closed_at), then averaged within each outcome group. - Days open = closed_at - created_at averaged in days. - Distinct accounts/reps counted via COUNT(DISTINCT account_id) / COUNT(DISTINCT owner_user_id) per group. - One row per outcome (won, lost), ordered won first.”

The report comes back overnight

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

outcome | opportunities | avg_amount_eur | median_amount_eur | avg_activities_before_close | avg_days_open | distinct_accounts | distinct_reps
--------+---------------+----------------+-------------------+-----------------------------+---------------+-------------------+--------------
won     |           339 |        5736.76 |           4500.00 |                         3.0 |          18.9 |               155 |            10
lost    |            67 |        4946.74 |           4200.00 |                        3.48 |          33.1 |                67 |             9
(2 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:
- q176New Business Only: Won vs Lost Opportunity Characteristics

It becomes benchmark query q174

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

spellingcast
spellingfetch first
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)))
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 (9×)
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 (2×)
placeholderfetch first

The benchmark text, against the submitted SQL

WITH b2b_tenants AS (  SELECT id FROM tenants  WHERE industry ILIKE '%industrial%' OR industry ILIKE '%office equipment%' OR industry ILIKE '%wholesal%'),closed_opps AS (  SELECT o.id, o.tenant_id, o.account_id, o.owner_user_id, o.is_won, o.closed_at, o.created_at,    o.amount,    o.currency_code,  SELECT o.id AS id, o.tenant_id AS tenant_id, o.account_id AS account_id, o.owner_user_id AS owner_user_id, o.is_won AS is_won, o.closed_at AS closed_at, o.created_at AS created_at,    o.amount AS amount,    o.currency_code AS currency_code,    CASE WHEN o.currency_code = 'EUR' THEN o.amount         ELSE o.amount / (           SELECT er.rate FROM exchange_rates er           WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code             AND er.as_of <= o.closed_at::date           ORDER BY er.as_of DESC LIMIT 1             AND er.as_of <= CAST(o.closed_at AS date)           ORDER BY er.as_of DESC NULLS FIRST {{fetch_first(1)}}         )    END AS amount_eur  FROM opportunities o  JOIN b2b_tenants t ON t.id = o.tenant_id  WHERE o.is_closed = true AND o.closed_at IS NOT NULL),activity_counts AS (  SELECT co.id AS opportunity_id, COUNT(a.id) AS activity_count  FROM closed_opps co  LEFT JOIN activities a ON a.opportunity_id = co.id AND a.occurred_at <= co.closed_at  GROUP BY co.id)SELECT  CASE WHEN co.is_won THEN 'won' ELSE 'lost' END AS outcome,  COUNT(*) AS opportunities,  ROUND(AVG(co.amount_eur), 2) AS avg_amount_eur,  ROUND(CAST(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY co.amount_eur) AS numeric), 2) AS median_amount_eur,  ROUND(AVG(ac.activity_count), 2) AS avg_activities_before_close,  ROUND(CAST(AVG(EXTRACT(EPOCH FROM (co.closed_at - co.created_at)) / 86400.0) AS numeric), 1) AS avg_days_open,  AVG(co.amount_eur) AS avg_amount_eur,  {{percentile_cont(0.5, co.amount_eur)}} AS median_amount_eur,  AVG(CAST(ac.activity_count AS NUMERIC(18,6))) AS avg_activities_before_close,  AVG({{seconds_between(co.created_at, co.closed_at)}} / 86400.0) AS avg_days_open,  COUNT(DISTINCT co.account_id) AS distinct_accounts,  COUNT(DISTINCT co.owner_user_id) AS distinct_repsFROM closed_opps coJOIN activity_counts ac ON ac.opportunity_id = co.idGROUP BY co.is_wonORDER BY co.is_won DESCORDER BY co.is_won DESC NULLS FIRST

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

WITH b2b_tenants AS (
  SELECT id FROM tenants
  WHERE industry ILIKE '%industrial%' OR industry ILIKE '%office equipment%' OR industry ILIKE '%wholesal%'
),
closed_opps AS (
  SELECT o.id AS id, o.tenant_id AS tenant_id, o.account_id AS account_id, o.owner_user_id AS owner_user_id, o.is_won AS is_won, o.closed_at AS closed_at, o.created_at AS created_at,
    o.amount AS amount,
    o.currency_code AS currency_code,
    CASE WHEN o.currency_code = 'EUR' THEN o.amount
         ELSE o.amount / (
           SELECT er.rate FROM exchange_rates er
           WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code
             AND er.as_of <= CAST(o.closed_at AS date)
           ORDER BY er.as_of DESC NULLS FIRST FETCH FIRST 1 ROWS ONLY
         )
    END AS amount_eur
  FROM opportunities o
  JOIN b2b_tenants t ON t.id = o.tenant_id
  WHERE o.is_closed = true AND o.closed_at IS NOT NULL
),
activity_counts AS (
  SELECT co.id AS opportunity_id, COUNT(a.id) AS activity_count
  FROM closed_opps co
  LEFT JOIN activities a ON a.opportunity_id = co.id AND a.occurred_at <= co.closed_at
  GROUP BY co.id
)
SELECT
  CASE WHEN co.is_won THEN 'won' ELSE 'lost' END AS outcome,
  COUNT(*) AS opportunities,
  AVG(co.amount_eur) AS avg_amount_eur,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY co.amount_eur) AS median_amount_eur,
  AVG(CAST(ac.activity_count AS NUMERIC(18,6))) AS avg_activities_before_close,
  AVG(CAST(EXTRACT(EPOCH FROM (co.closed_at - co.created_at)) AS NUMERIC(18,6)) / 86400.0) AS avg_days_open,
  COUNT(DISTINCT co.account_id) AS distinct_accounts,
  COUNT(DISTINCT co.owner_user_id) AS distinct_reps
FROM closed_opps co
JOIN activity_counts ac ON ac.opportunity_id = co.id
GROUP BY co.is_won
ORDER BY co.is_won DESC NULLS FIRST

WITH b2b_tenants AS (
  SELECT id FROM tenants
  WHERE industry ILIKE '%industrial%' OR industry ILIKE '%office equipment%' OR industry ILIKE '%wholesal%'
),
closed_opps AS (
  SELECT o.id AS id, o.tenant_id AS tenant_id, o.account_id AS account_id, o.owner_user_id AS owner_user_id, o.is_won AS is_won, o.closed_at AS closed_at, o.created_at AS created_at,
    o.amount AS amount,
    o.currency_code AS currency_code,
    CASE WHEN o.currency_code = 'EUR' THEN o.amount
         ELSE o.amount / (
           SELECT er.rate FROM exchange_rates er
           WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code
             AND er.as_of <= CAST(o.closed_at AS date)
           ORDER BY er.as_of DESC NULLS FIRST LIMIT 1
         )
    END AS amount_eur
  FROM opportunities o
  JOIN b2b_tenants t ON t.id = o.tenant_id
  WHERE o.is_closed = true AND o.closed_at IS NOT NULL
),
activity_counts AS (
  SELECT co.id AS opportunity_id, COUNT(a.id) AS activity_count
  FROM closed_opps co
  LEFT JOIN activities a ON a.opportunity_id = co.id AND a.occurred_at <= co.closed_at
  GROUP BY co.id
)
SELECT
  CASE WHEN co.is_won THEN 'won' ELSE 'lost' END AS outcome,
  COUNT(*) AS opportunities,
  AVG(co.amount_eur) AS avg_amount_eur,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY co.amount_eur) AS median_amount_eur,
  AVG(CAST(ac.activity_count AS NUMERIC(18,6))) AS avg_activities_before_close,
  AVG(CAST(EXTRACT(EPOCH FROM (co.closed_at - co.created_at)) AS NUMERIC(18,6)) / 86400.0) AS avg_days_open,
  COUNT(DISTINCT co.account_id) AS distinct_accounts,
  COUNT(DISTINCT co.owner_user_id) AS distinct_reps
FROM closed_opps co
JOIN activity_counts ac ON ac.opportunity_id = co.id
GROUP BY co.is_won
ORDER BY co.is_won DESC NULLS FIRST

WITH b2b_tenants AS (
  SELECT id FROM tenants
  WHERE industry ILIKE '%industrial%' OR industry ILIKE '%office equipment%' OR industry ILIKE '%wholesal%'
),
closed_opps AS (
  SELECT o.id AS id, o.tenant_id AS tenant_id, o.account_id AS account_id, o.owner_user_id AS owner_user_id, o.is_won AS is_won, o.closed_at AS closed_at, o.created_at AS created_at,
    o.amount AS amount,
    o.currency_code AS currency_code,
    CASE WHEN o.currency_code = 'EUR' THEN o.amount
         ELSE o.amount / (
           SELECT er.rate FROM exchange_rates er
           WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code
             AND er.as_of <= CAST(o.closed_at AS date)
           ORDER BY er.as_of DESC NULLS FIRST FETCH FIRST 1 ROWS ONLY
         )
    END AS amount_eur
  FROM opportunities o
  JOIN b2b_tenants t ON t.id = o.tenant_id
  WHERE o.is_closed = true AND o.closed_at IS NOT NULL
),
activity_counts AS (
  SELECT co.id AS opportunity_id, COUNT(a.id) AS activity_count
  FROM closed_opps co
  LEFT JOIN activities a ON a.opportunity_id = co.id AND a.occurred_at <= co.closed_at
  GROUP BY co.id
)
SELECT
  CASE WHEN co.is_won THEN 'won' ELSE 'lost' END AS outcome,
  COUNT(*) AS opportunities,
  AVG(co.amount_eur) AS avg_amount_eur,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(co.amount_eur AS DOUBLE PRECISION)) AS median_amount_eur,
  AVG(CAST(ac.activity_count AS NUMERIC(18,6))) AS avg_activities_before_close,
  AVG(CAST(EXTRACT(EPOCH FROM (co.closed_at - co.created_at)) AS NUMERIC(18,6)) / 86400.0) AS avg_days_open,
  COUNT(DISTINCT co.account_id) AS distinct_accounts,
  COUNT(DISTINCT co.owner_user_id) AS distinct_reps
FROM closed_opps co
JOIN activity_counts ac ON ac.opportunity_id = co.id
GROUP BY co.is_won
ORDER BY co.is_won DESC NULLS FIRST

WITH b2b_tenants AS (
  SELECT id FROM tenants
  WHERE industry ILIKE '%industrial%' OR industry ILIKE '%office equipment%' OR industry ILIKE '%wholesal%'
),
closed_opps AS (
  SELECT o.id AS id, o.tenant_id AS tenant_id, o.account_id AS account_id, o.owner_user_id AS owner_user_id, o.is_won AS is_won, o.closed_at AS closed_at, o.created_at AS created_at,
    o.amount AS amount,
    o.currency_code AS currency_code,
    CASE WHEN o.currency_code = 'EUR' THEN o.amount
         ELSE o.amount / (
           SELECT er.rate FROM exchange_rates er
           WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code
             AND er.as_of <= CAST(o.closed_at AS date)
           ORDER BY er.as_of DESC NULLS FIRST FETCH FIRST 1 ROWS ONLY
         )
    END AS amount_eur
  FROM opportunities o
  JOIN b2b_tenants t ON t.id = o.tenant_id
  WHERE o.is_closed = true AND o.closed_at IS NOT NULL
),
activity_counts AS (
  SELECT co.id AS opportunity_id, COUNT(a.id) AS activity_count
  FROM closed_opps co
  LEFT JOIN activities a ON a.opportunity_id = co.id AND a.occurred_at <= co.closed_at
  GROUP BY co.id
)
SELECT
  CASE WHEN co.is_won THEN 'won' ELSE 'lost' END AS outcome,
  COUNT(*) AS opportunities,
  AVG(co.amount_eur) AS avg_amount_eur,
  quantileExactInclusive(0.5)(toFloat64(co.amount_eur)) AS median_amount_eur,
  AVG(CAST(ac.activity_count AS NUMERIC(18,6))) AS avg_activities_before_close,
  AVG(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', co.created_at, co.closed_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0) AS avg_days_open,
  COUNT(DISTINCT co.account_id) AS distinct_accounts,
  COUNT(DISTINCT co.owner_user_id) AS distinct_reps
FROM closed_opps co
JOIN activity_counts ac ON ac.opportunity_id = co.id
GROUP BY co.is_won
ORDER BY co.is_won DESC NULLS FIRST

Firebolt{ "errors": [ { "description": "Line 10, Column 24: Operations between decimals with different precision and scale is not supported at the moment. Explicitly cast one of the decimals to other decimal precision and scale." } ], "query": { "query_id": "10713583-3042-450f-a5ea-c2d5f405fd06", "query_lab
DataFusionThis feature is not implemented: Physical plan does not support logical expression ScalarSubquery(<subquery>)
ClickHouseCode: 48. DB::Exception: Cannot decorrelate query, because 'Limit' step is not supported. (NOT_IMPLEMENTED)

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

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: B2B Wholesale Opportunities: Won vs Lost Characteristics
Question: Among closed B2B wholesale opportunities, what reliably differs between the ones we won and the ones we lost — deal size, touches/activities before close, days open, or how concentrated they are among accounts and reps?
Grain: one row per closed outcome (won or lost)
Columns:
- outcome (won / lost)
- opportunities (count)
- average amount in EUR
- median amount in EUR
- average activities per opportunity before close
- average days open (closed_at - created_at)
- distinct accounts involved
- distinct owning reps (owner_user_id) involved
Details: Scope to the same B2B wholesale tenants as the other two reports (industry ILIKE '%industrial%' OR '%office equipment%' OR '%wholesal%'). Use opportunities where is_closed = true, split into is_won = true (won) vs is_won = false (lost), across all history through 2025-12-31. Convert opportunity amount to EUR using exchange_rates (base_code='EUR', quote_code = opportunity currency_code, rate as_of the opportunity's closed_at date, or nearest prior business day if a weekend) before averaging across tenants/currencies, per house standards. 'Activities before close' = count of rows in activities where activities.opportunity_id = opportunities.id and activities.occurred_at <= opportunities.closed_at, averaged per opportunity within each outcome group. Distinct accounts/reps are counts of distinct account_id / owner_user_id among opportunities in that outcome group. Order rows with 'won' first, then 'lost'.

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

Ines calls dry_run

WITH b2b_tenants AS (
  SELECT id FROM tenants
  WHERE industry ILIKE '%industrial%' OR industry ILIKE '%office equipment%' OR industry ILIKE '%wholesal%'
),
closed_opps AS (
  SELECT o.id, o.tenant_id, o.account_id, o.owner_user_id, o.is_won, o.closed_at, o.created_at,
    o.amount,
    o.currency_code,
    CASE WHEN o.currency_code = 'EUR' THEN o.amount
         ELSE o.amount / (
           SELECT er.rate FROM exchange_rates er
           WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code
             AND er.as_of <= o.closed_at::date
           ORDER BY er.as_of DESC LIMIT 1
         )
    END AS amount_eur
  FROM opportunities o
  JOIN b2b_tenants t ON t.id = o.tenant_id
  WHERE o.is_closed = true AND o.closed_at IS NOT NULL
),
activity_counts AS (
  SELECT co.id AS opportunity_id, COUNT(a.id) AS activity_count
  FROM closed_opps co
  LEFT JOIN activities a ON a.opportunity_id = co.id AND a.occurred_at <= co.closed_at
  GROUP BY co.id
)
SELECT
  CASE WHEN co.is_won THEN 'won' ELSE 'lost' END AS outcome,
  COUNT(*) AS opportunities,
  ROUND(AVG(co.amount_eur), 2) AS avg_amount_eur,
  ROUND(CAST(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY co.amount_eur) AS numeric), 2) AS median_amount_eur,
  ROUND(AVG(ac.activity_count), 2) AS avg_activities_before_close,
  ROUND(CAST(AVG(EXTRACT(EPOCH FROM (co.closed_at - co.created_at)) / 86400.0) AS numeric), 1) AS avg_days_open,
  COUNT(DISTINCT co.account_id) AS distinct_accounts,
  COUNT(DISTINCT co.owner_user_id) AS distinct_reps
FROM closed_opps co
JOIN activity_counts ac ON ac.opportunity_id = co.id
GROUP BY co.is_won
ORDER BY co.is_won DESC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: outcome, opportunities, avg_amount_eur, median_amount_eur, avg_activities_before_close, avg_days_open, distinct_accounts, distinct_reps

InesQuery compiles and returns exactly the requested columns and grain. Submitting.

Ines calls submit

WITH b2b_tenants AS (
  SELECT id FROM tenants
  WHERE industry ILIKE '%industrial%' OR industry ILIKE '%office equipment%' OR industry ILIKE '%wholesal%'
),
closed_opps AS (
  SELECT o.id, o.tenant_id, o.account_id, o.owner_user_id, o.is_won, o.closed_at, o.created_at,
    o.amount,
    o.currency_code,
    CASE WHEN o.currency_code = 'EUR' THEN o.amount
         ELSE o.amount / (
           SELECT er.rate FROM exchange_rates er
           WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code
             AND er.as_of <= o.closed_at::date
           ORDER BY er.as_of DESC LIMIT 1
         )
    END AS amount_eur
  FROM opportunities o
  JOIN b2b_tenants t ON t.id = o.tenant_id
  WHERE o.is_closed = true AND o.closed_at IS NOT NULL
),
activity_counts AS (
  SELECT co.id AS opportunity_id, COUNT(a.id) AS activity_count
  FROM closed_opps co
  LEFT JOIN activities a ON a.opportunity_id = co.id AND a.occurred_at <= co.closed_at
  GROUP BY co.id
)
SELECT
  CASE WHEN co.is_won THEN 'won' ELSE 'lost' END AS outcome,
  COUNT(*) AS opportunities,
  ROUND(AVG(co.amount_eur), 2) AS avg_amount_eur,
  ROUND(CAST(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY co.amount_eur) AS numeric), 2) AS median_amount_eur,
  ROUND(AVG(ac.activity_count), 2) AS avg_activities_before_close,
  ROUND(CAST(AVG(EXTRACT(EPOCH FROM (co.closed_at - co.created_at)) / 86400.0) AS numeric), 1) AS avg_days_open,
  COUNT(DISTINCT co.account_id) AS distinct_accounts,
  COUNT(DISTINCT co.owner_user_id) AS distinct_reps
FROM closed_opps co
JOIN activity_counts ac ON ac.opportunity_id = co.id
GROUP BY co.is_won
ORDER BY co.is_won DESC

The warehouse answers

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

InesSubmitted. Summary of the approach: - Scoped to B2B wholesale tenants by industry pattern match, then to opportunities with `is_closed = true`, split into won/lost by `is_won`. - Converted each opportunity's amount to EUR using the `exchange_rates` rate (base EUR → opportunity currency) as of `closed_at`'s date, falling back to the nearest prior available date to handle weekends/gaps. - Computed per-opportunity activity counts (activities linked by `opportunity_id` with `occurred_at <= closed_at`), then averaged within each outcome group. - Days open = `closed_at - created_at` averaged in days. - Distinct accounts/reps counted via `COUNT(DISTINCT account_id)` / `COUNT(DISTINCT owner_user_id)` per group. - One row per outcome (won, lost), ordered won first.

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

B2B Wholesale Opportunities: Won vs Lost Characteristics

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

Patrick O'Neill writes to Daniel Kim

Daniel Kim plans the night

Ines writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q174

Everything, as recorded