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

B2B Wholesale: Dollar-Weighted vs Count-Weighted Win Rate by Rep

“Is Youssef Meijer's pattern of winning small deals but losing his biggest one a one-off, or do several reps actually perform worse on a dollar basis than their deal-count win rate suggests?”

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 SQLFelixJunior engineer who nests subqueries in FROM and WHERE (IN, EXISTS, correlated subqueries) rather than CTEs.

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 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
Daniel Kim plans the night
The analyst · week 2, night 2, request 2 of 3
Night 1 gave three clean answers. (1) Activity timing on lost deals: only 40% of lost opportunities have any logged activity in their first quarter of life, rising to 100% in the second quarter and staying high (96.7%, 76.7%) through the third and fourth — this reads as 'actively worked to the end' for the large majority, not 'stalled and forgotten', though ~23% do go quiet before closing. (2) Youssef Meijer's 7 deals span 7 distinct accounts (no territory/account concentration), but his 3 losses include a €26,000 deal — by dollar value he won only ~26% of his pipeline despite a 57% count-based win rate, suggesting he's fine on small/quick deals but struggles to close large ones. (3) The 'zero open pipeline' finding is NOT a 3-year constant — nearly every month-end from 2023 through Nov 2025 had a handful of genuinely open deals (some 40-70+ days old); only Dec 2025 (and two earlier one-off months) show zero. That raises a new question before I can tell Patrick whether this is a real business fact or a data gap. Tonight's three slots: (1) check whether opportunity creation volume dropped in Oct-Dec 2025 (would signal a data-freshness/ingestion gap) or stayed normal (meaning it's a real, if unusual, pipeline flush); (2) extend the Youssef finding to all reps via a dollar-weighted vs count-weighted win-rate comparison, to see if 'wins small deals, loses the big one' is a broader pattern worth flagging beyond one rep; (3) Patrick's lower-priority Q4 on seasonality of lost-deal duration, since we have a free slot and it's a quick, well-defined query.
B2B Wholesale: Dollar-Weighted vs Count-Weighted Win Rate by Rep
Question
Is Youssef Meijer's pattern of winning small deals but losing his biggest one a one-off, or do several reps actually perform worse on a dollar basis than their deal-count win rate suggests?
One row per
sales rep (opportunities.owner_user_id) with at least 3 closed New-business opportunities
Columns
- rep_name
- merchant
- opportunities_closed
- count_win_rate_pct
- total_amount_won_eur
- total_amount_lost_eur
- dollar_weighted_win_rate_pct
- gap_pct_points (count_win_rate_pct minus dollar_weighted_win_rate_pct)

Felix 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)

SELECT
  u.full_name AS rep_name,
  t.name AS merchant,
  COUNT(*) AS opportunities_closed,
  ROUND(100.0 * SUM(CASE WHEN sub.is_won THEN 1 ELSE 0 END) / COUNT(*), 1) AS count_win_rate_pct,
  ROUND(SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END), 2) AS total_amount_won_eur,
  ROUND(SUM(CASE WHEN NOT sub.is_won THEN sub.amount_eur ELSE 0 END), 2) AS total_amount_lost_eur,
  ROUND(100.0 * SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) / NULLIF(SUM(sub.amount_eur), 0), 1) AS dollar_weighted_win_rate_pct,
  ROUND(
    (100.0 * SUM(CASE WHEN sub.is_won THEN 1 ELSE 0 END) / COUNT(*))
    - (100.0 * SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) / NULLIF(SUM(sub.amount_eur), 0))
  , 1) AS gap_pct_points
FROM (
  SELECT
    o.tenant_id,
    o.owner_user_id,
    o.is_won,
    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
        LIMIT 1
      )
    END AS amount_eur
  FROM opportunities o
  JOIN tenants t2 ON t2.id = o.tenant_id
  JOIN pipelines p ON p.id = o.pipeline_id
  WHERE t2.name IN (
      'Ballycorran Industrial Supply',
      'Castell Fasteners',
      'Hanse Industriebedarf',
      'Midland Safety Wear',
      'Polder Packaging'
    )
    AND p.name = 'New business'
    AND o.is_closed = true
) sub
JOIN users u ON u.id = sub.owner_user_id
JOIN tenants t ON t.id = sub.tenant_id
GROUP BY sub.owner_user_id, u.full_name, t.name
HAVING COUNT(*) >= 3
ORDER BY gap_pct_points DESC, rep_name ASC

“Query compiles and returns exactly the requested 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 computes, per rep (owner_user_id) within the five named B2B tenants' "New business" pipelines, closed opportunities, count-based win rate, EUR-converted won/lost totals, dollar-weighted win rate, and the gap between the two — ordered so reps whose dollar performance is worse than their deal-count win rate suggests (like Youssef Meijer) appear first.”

The report comes back overnight

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

rep_name            | merchant                      | opportunities_closed | count_win_rate_pct | total_amount_won_eur | total_amount_lost_eur | dollar_weighted_win_rate_pct | gap_pct_points
--------------------+-------------------------------+----------------------+--------------------+----------------------+-----------------------+------------------------------+---------------
Youssef Meijer      | Polder Packaging              |                    7 |               57.1 |              14100.0 |               40700.0 |                         25.7 |           31.4
Megan Taylor        | Castell Fasteners             |                   15 |               86.7 |              56581.5 |              10477.75 |                         84.4 |            2.3
Greta Schmitt       | Hanse Industriebedarf         |                   12 |               83.3 |              45800.0 |               10400.0 |                         81.5 |            1.8
Logan Roberts       | Ballycorran Industrial Supply |                   40 |               80.0 |             121300.0 |               33400.0 |                         78.4 |            1.6
Sarah Hartmann      | Hanse Industriebedarf         |                    5 |              100.0 |              37500.0 |                   0.0 |                        100.0 |            0.0
Joshua Jackson      | Castell Fasteners             |                   21 |               85.7 |            106670.12 |              17665.22 |                         85.8 |           -0.1
Lotte van den Broek | Midland Safety Wear           |                   18 |               83.3 |              87700.0 |               16700.0 |                         84.0 |           -0.7
Niamh Harrison      | Ballycorran Industrial Supply |                   36 |               86.1 |             136300.0 |               17800.0 |                         88.4 |           -2.3
Thijs Dijkstra      | Polder Packaging              |                    9 |               88.9 |              44800.0 |                2800.0 |                         94.1 |           -5.2
Jesse Novotný       | Midland Safety Wear           |                   22 |               86.4 |             100200.0 |                9100.0 |                         91.7 |           -5.3
(10 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:
- q388B2B Wholesale: Dollar-Weighted vs Count-Weighted Win Rate by Merchant
It builds on:
- q181B2B Wholesale: Win Rate and Cycle Time by Sales Rep (New Business) — Corrected
- q383Youssef Meijer: Individual New-Business Opportunities

It becomes benchmark query q386

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

spellingfetch first
determinismtie-breaker: (owner_user_id, t.name) is the grouping key
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
made explicita selected t.col gets AS col, its standard name made explicit (3×)
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (5×)
placeholderfetch first

The benchmark text, against the submitted SQL

SELECT  u.full_name AS rep_name,  t.name AS merchant,  COUNT(*) AS opportunities_closed,  ROUND(100.0 * SUM(CASE WHEN sub.is_won THEN 1 ELSE 0 END) / COUNT(*), 1) AS count_win_rate_pct,  ROUND(SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END), 2) AS total_amount_won_eur,  ROUND(SUM(CASE WHEN NOT sub.is_won THEN sub.amount_eur ELSE 0 END), 2) AS total_amount_lost_eur,  ROUND(100.0 * SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) / NULLIF(SUM(sub.amount_eur), 0), 1) AS dollar_weighted_win_rate_pct,  ROUND(    (100.0 * SUM(CASE WHEN sub.is_won THEN 1 ELSE 0 END) / COUNT(*))    - (100.0 * SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) / NULLIF(SUM(sub.amount_eur), 0))  , 1) AS gap_pct_points  100.0 * SUM(CASE WHEN sub.is_won THEN 1 ELSE 0 END) / COUNT(*) AS count_win_rate_pct,  SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) AS total_amount_won_eur,  SUM(CASE WHEN NOT sub.is_won THEN sub.amount_eur ELSE 0 END) AS total_amount_lost_eur,  100.0 * SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) / NULLIF(SUM(sub.amount_eur), 0) AS dollar_weighted_win_rate_pct,  (100.0 * SUM(CASE WHEN sub.is_won THEN 1 ELSE 0 END) / COUNT(*))  - (100.0 * SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) / NULLIF(SUM(sub.amount_eur), 0)) AS gap_pct_pointsFROM (  SELECT    o.tenant_id,    o.owner_user_id,    o.is_won,    o.tenant_id AS tenant_id,    o.owner_user_id AS owner_user_id,    o.is_won AS is_won,    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        LIMIT 1        ORDER BY er.as_of DESC NULLS FIRST        {{fetch_first(1)}}      )    END AS amount_eur  FROM opportunities o  JOIN tenants t2 ON t2.id = o.tenant_id  JOIN pipelines p ON p.id = o.pipeline_id  WHERE t2.name IN (      'Ballycorran Industrial Supply',      'Castell Fasteners',      'Hanse Industriebedarf',      'Midland Safety Wear',      'Polder Packaging'    )    AND p.name = 'New business'    AND o.is_closed = true) subJOIN users u ON u.id = sub.owner_user_idJOIN tenants t ON t.id = sub.tenant_idGROUP BY sub.owner_user_id, u.full_name, t.nameHAVING COUNT(*) >= 3ORDER BY gap_pct_points DESC, rep_name ASCORDER BY gap_pct_points DESC NULLS FIRST, rep_name ASC NULLS LAST, sub.owner_user_id NULLS LAST, t.name NULLS LAST

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

SELECT
  u.full_name AS rep_name,
  t.name AS merchant,
  COUNT(*) AS opportunities_closed,
  100.0 * SUM(CASE WHEN sub.is_won THEN 1 ELSE 0 END) / COUNT(*) AS count_win_rate_pct,
  SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) AS total_amount_won_eur,
  SUM(CASE WHEN NOT sub.is_won THEN sub.amount_eur ELSE 0 END) AS total_amount_lost_eur,
  100.0 * SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) / NULLIF(SUM(sub.amount_eur), 0) AS dollar_weighted_win_rate_pct,
  (100.0 * SUM(CASE WHEN sub.is_won THEN 1 ELSE 0 END) / COUNT(*))
  - (100.0 * SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) / NULLIF(SUM(sub.amount_eur), 0)) AS gap_pct_points
FROM (
  SELECT
    o.tenant_id AS tenant_id,
    o.owner_user_id AS owner_user_id,
    o.is_won AS is_won,
    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 tenants t2 ON t2.id = o.tenant_id
  JOIN pipelines p ON p.id = o.pipeline_id
  WHERE t2.name IN (
      'Ballycorran Industrial Supply',
      'Castell Fasteners',
      'Hanse Industriebedarf',
      'Midland Safety Wear',
      'Polder Packaging'
    )
    AND p.name = 'New business'
    AND o.is_closed = true
) sub
JOIN users u ON u.id = sub.owner_user_id
JOIN tenants t ON t.id = sub.tenant_id
GROUP BY sub.owner_user_id, u.full_name, t.name
HAVING COUNT(*) >= 3
ORDER BY gap_pct_points DESC NULLS FIRST, rep_name ASC NULLS LAST, sub.owner_user_id NULLS LAST, t.name NULLS LAST

SELECT
  u.full_name AS rep_name,
  t.name AS merchant,
  COUNT(*) AS opportunities_closed,
  100.0 * SUM(CASE WHEN sub.is_won THEN 1 ELSE 0 END) / COUNT(*) AS count_win_rate_pct,
  SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) AS total_amount_won_eur,
  SUM(CASE WHEN NOT sub.is_won THEN sub.amount_eur ELSE 0 END) AS total_amount_lost_eur,
  100.0 * SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) / NULLIF(SUM(sub.amount_eur), 0) AS dollar_weighted_win_rate_pct,
  (100.0 * SUM(CASE WHEN sub.is_won THEN 1 ELSE 0 END) / COUNT(*))
  - (100.0 * SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) / NULLIF(SUM(sub.amount_eur), 0)) AS gap_pct_points
FROM (
  SELECT
    o.tenant_id AS tenant_id,
    o.owner_user_id AS owner_user_id,
    o.is_won AS is_won,
    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 tenants t2 ON t2.id = o.tenant_id
  JOIN pipelines p ON p.id = o.pipeline_id
  WHERE t2.name IN (
      'Ballycorran Industrial Supply',
      'Castell Fasteners',
      'Hanse Industriebedarf',
      'Midland Safety Wear',
      'Polder Packaging'
    )
    AND p.name = 'New business'
    AND o.is_closed = true
) sub
JOIN users u ON u.id = sub.owner_user_id
JOIN tenants t ON t.id = sub.tenant_id
GROUP BY sub.owner_user_id, u.full_name, t.name
HAVING COUNT(*) >= 3
ORDER BY gap_pct_points DESC NULLS FIRST, rep_name ASC NULLS LAST, sub.owner_user_id NULLS LAST, t.name NULLS LAST

Firebolt{ "errors": [ { "description": "Line 18, Column 21: 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": "3c70f79a-2597-4781-b260-b5dbcb9e2da9", "query_lab
DataFusionThis feature is not implemented: Physical plan does not support logical expression ScalarSubquery(<subquery>)
ClickHouseCode: 48. DB::Exception: Current query is not supported yet, because can't find correlated column '__table4.closed_at' in current header: __table4.tenant_id, __table4.owner_user_id, __table4.is_won, __table4.currency_code, __table4.amount. (NOT_IMPLEMENTED)

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

Felix's whole session, 7 turns

The request, as Felix received it

Report request from Daniel Kim (analyst for the Head of B2B Commerce):

Title: B2B Wholesale: Dollar-Weighted vs Count-Weighted Win Rate by Rep
Question: Is Youssef Meijer's pattern of winning small deals but losing his biggest one a one-off, or do several reps actually perform worse on a dollar basis than their deal-count win rate suggests?
Grain: one row per sales rep (opportunities.owner_user_id) with at least 3 closed New-business opportunities
Columns:
- rep_name
- merchant
- opportunities_closed
- count_win_rate_pct
- total_amount_won_eur
- total_amount_lost_eur
- dollar_weighted_win_rate_pct
- gap_pct_points (count_win_rate_pct minus dollar_weighted_win_rate_pct)
Details: Prompted by the Youssef Meijer case file: he won 4 of 7 deals by count (57.1%) but his 3 losses included a €26,000 deal, so by value he won only €14,100 of €54,800 total pipeline (~25.7%) — a huge gap between count-based and dollar-based win rate. This report checks whether that pattern is common across reps. Scope: tenants.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging'), pipeline_id = each tenant's 'New business' pipeline, is_closed = true, grouped by owner_user_id (join users for full_name, tenants for merchant name), excluding reps with fewer than 3 closed opportunities. Convert amount to EUR: if currency_code = 'EUR', amount_eur = amount; otherwise amount_eur = amount / rate from exchange_rates (base_code='EUR', quote_code=currency_code, as_of nearest date on or before closed_at). count_win_rate_pct = closed_won / opportunities_closed * 100. dollar_weighted_win_rate_pct = total_amount_won_eur / (total_amount_won_eur + total_amount_lost_eur) * 100. gap_pct_points = count_win_rate_pct - dollar_weighted_win_rate_pct (positive means the rep's count-based win rate overstates how they're doing in euros, i.e. they're losing their biggest deals). Order by gap_pct_points descending so the reps most like Youssef Meijer float to the top.

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

Felix calls dry_run

SELECT
  u.full_name AS rep_name,
  t.name AS merchant,
  COUNT(*) AS opportunities_closed,
  ROUND(100.0 * SUM(CASE WHEN sub.is_won THEN 1 ELSE 0 END) / COUNT(*), 1) AS count_win_rate_pct,
  ROUND(SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END), 2) AS total_amount_won_eur,
  ROUND(SUM(CASE WHEN NOT sub.is_won THEN sub.amount_eur ELSE 0 END), 2) AS total_amount_lost_eur,
  ROUND(100.0 * SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) / NULLIF(SUM(sub.amount_eur), 0), 1) AS dollar_weighted_win_rate_pct,
  ROUND(
    (100.0 * SUM(CASE WHEN sub.is_won THEN 1 ELSE 0 END) / COUNT(*))
    - (100.0 * SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) / NULLIF(SUM(sub.amount_eur), 0))
  , 1) AS gap_pct_points
FROM (
  SELECT
    o.tenant_id,
    o.owner_user_id,
    o.is_won,
    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
        LIMIT 1
      )
    END AS amount_eur
  FROM opportunities o
  JOIN tenants t2 ON t2.id = o.tenant_id
  JOIN pipelines p ON p.id = o.pipeline_id
  WHERE t2.name IN (
      'Ballycorran Industrial Supply',
      'Castell Fasteners',
      'Hanse Industriebedarf',
      'Midland Safety Wear',
      'Polder Packaging'
    )
    AND p.name = 'New business'
    AND o.is_closed = true
) sub
JOIN users u ON u.id = sub.owner_user_id
JOIN tenants t ON t.id = sub.tenant_id
GROUP BY sub.owner_user_id, u.full_name, t.name
HAVING COUNT(*) >= 3
ORDER BY gap_pct_points DESC, rep_name ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: rep_name, merchant, opportunities_closed, count_win_rate_pct, total_amount_won_eur, total_amount_lost_eur, dollar_weighted_win_rate_pct, gap_pct_points

FelixQuery compiles and returns exactly the requested columns. Submitting.

Felix calls submit

SELECT
  u.full_name AS rep_name,
  t.name AS merchant,
  COUNT(*) AS opportunities_closed,
  ROUND(100.0 * SUM(CASE WHEN sub.is_won THEN 1 ELSE 0 END) / COUNT(*), 1) AS count_win_rate_pct,
  ROUND(SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END), 2) AS total_amount_won_eur,
  ROUND(SUM(CASE WHEN NOT sub.is_won THEN sub.amount_eur ELSE 0 END), 2) AS total_amount_lost_eur,
  ROUND(100.0 * SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) / NULLIF(SUM(sub.amount_eur), 0), 1) AS dollar_weighted_win_rate_pct,
  ROUND(
    (100.0 * SUM(CASE WHEN sub.is_won THEN 1 ELSE 0 END) / COUNT(*))
    - (100.0 * SUM(CASE WHEN sub.is_won THEN sub.amount_eur ELSE 0 END) / NULLIF(SUM(sub.amount_eur), 0))
  , 1) AS gap_pct_points
FROM (
  SELECT
    o.tenant_id,
    o.owner_user_id,
    o.is_won,
    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
        LIMIT 1
      )
    END AS amount_eur
  FROM opportunities o
  JOIN tenants t2 ON t2.id = o.tenant_id
  JOIN pipelines p ON p.id = o.pipeline_id
  WHERE t2.name IN (
      'Ballycorran Industrial Supply',
      'Castell Fasteners',
      'Hanse Industriebedarf',
      'Midland Safety Wear',
      'Polder Packaging'
    )
    AND p.name = 'New business'
    AND o.is_closed = true
) sub
JOIN users u ON u.id = sub.owner_user_id
JOIN tenants t ON t.id = sub.tenant_id
GROUP BY sub.owner_user_id, u.full_name, t.name
HAVING COUNT(*) >= 3
ORDER BY gap_pct_points DESC, rep_name ASC

The warehouse answers

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

FelixSubmitted. The query computes, per rep (owner_user_id) within the five named B2B tenants' "New business" pipelines, closed opportunities, count-based win rate, EUR-converted won/lost totals, dollar-weighted win rate, and the gap between the two — ordered so reps whose dollar performance is worse than their deal-count win rate suggests (like Youssef Meijer) appear first.

Not shown here: 240 KB of context the agent was given, its instructions and the schema documentation; it is in the raw transcript.

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

B2B Wholesale: Dollar-Weighted vs Count-Weighted Win Rate by Rep

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

Patrick O'Neill writes to Daniel Kim

Daniel Kim plans the night

Felix writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q386

Everything, as recorded