DB Olympics

Queries · q182 · Head of B2B Commerce · week 1, night 4

B2B Wholesale: Win Rate and Average Deal Size by Merchant (New Business) — Corrected

“Does the merchant-level win-rate pattern hold once we also see typical deal size per merchant, and can we fix last night's missing EUR averages?”

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 SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

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

  3. Daniel Kim plans the night

    The analyst · week 1, night 4, request 2 of 3

    Night 3 take: two of three reports came back broken/empty, and I need my last night to fix the essential one and still land priority 5. The merchant-level report (Report 1) worked but had a clear bug: avg_amount_eur was NULL for 4 of 5 merchants (all the EUR ones) — almost certainly a failed EUR-to-EUR identity lookup in exchange_rates rather than a real data gap. The rep-level report (Report 2) came back with 0 rows because the engineer used an exact-match tenant filter (industry IN (...)) instead of the pattern that's worked all week, so it silently matched nothing — this is still an open, important gap (Patrick's Q4) and needs a clean re-run. The open-pipeline-age report (Report 3) also returned 0 rows, but I'm treating that as a real, confirmed finding rather than a bug: night 1's stage report already showed zero open opportunities at every New-business stage for every wholesale tenant, so there is genuinely no live open new-business pipeline in the warehouse right now — worth flagging to Patrick/Samuel as 'we can't assess forward-looking staleness risk because there's no open pipeline to look at; the only staleness signal we have is backward-looking (lost deals average ~35 days open vs ~9 for won).' Spending tonight's three slots on: (1) the corrected rep-level report with an explicit, unambiguous tenant-name filter, (2) a corrected merchant-level report with an explicit EUR-conversion fix, and (3) a deal-size-band breakdown to chase the odd avg-vs-median flip in the won/lost amount comparison, which gives a real answer to Patrick's optional #5 on deal size vs win rate. Payment terms (also part of #5) aren't captured anywhere in the B2B CRM/deals schema for the wholesaler's own customers, so I'm leaving that out of the memo as 'not measurable from current data' rather than forcing a weak proxy.

    B2B Wholesale: Win Rate and Average Deal Size by Merchant (New Business) — Corrected
    Question
    Does the merchant-level win-rate pattern hold once we also see typical deal size per merchant, and can we fix last night's missing EUR averages?
    One row per
    wholesale merchant (tenant)
    Columns
    • merchant
    • currency_code (tenant default)
    • opportunities_closed
    • closed_won
    • closed_lost
    • win_rate_pct
    • avg_days_open
    • avg_amount_eur

  4. Bea 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
  t0.name AS merchant,
  t0.default_currency_code AS currency_code,
  COALESCE(s_counts.opportunities_closed, 0) AS opportunities_closed,
  COALESCE(s_counts.closed_won, 0) AS closed_won,
  COALESCE(s_counts.closed_lost, 0) AS closed_lost,
  ROUND(COALESCE(s_counts.closed_won, 0) * 100.0 / NULLIF(s_counts.opportunities_closed, 0), 2) AS win_rate_pct,
  ROUND(s_days.avg_days_open, 2) AS avg_days_open,
  ROUND(s_amt.avg_amount_eur, 2) AS avg_amount_eur
FROM tenants t0
LEFT JOIN (
  SELECT
    o.tenant_id,
    COUNT(*) AS opportunities_closed,
    SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) AS closed_won,
    SUM(CASE WHEN NOT o.is_won THEN 1 ELSE 0 END) AS closed_lost
  FROM opportunities o
  JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id
  WHERE o.is_closed = true
    AND p.name = 'New business'
  GROUP BY o.tenant_id
) s_counts ON s_counts.tenant_id = t0.id
LEFT JOIN (
  SELECT
    o.tenant_id,
    AVG(EXTRACT(EPOCH FROM (o.closed_at - o.created_at)) / 86400.0) AS avg_days_open
  FROM opportunities o
  JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id
  WHERE o.is_closed = true
    AND p.name = 'New business'
  GROUP BY o.tenant_id
) s_days ON s_days.tenant_id = t0.id
LEFT JOIN (
  SELECT
    o.tenant_id,
    AVG(
      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 avg_amount_eur
  FROM opportunities o
  JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id
  WHERE o.is_closed = true
    AND p.name = 'New business'
  GROUP BY o.tenant_id
) s_amt ON s_amt.tenant_id = t0.id
WHERE t0.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')
ORDER BY win_rate_pct ASC, opportunities_closed DESC, merchant ASC

    “Query compiles and returns exactly the requested columns at tenant 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 what the query does: - Grain: one row per wholesale merchant (tenant), filtered to the 5 named tenants. - Join logic: opportunities joined to each tenant's own pipelines row named 'New business', restricted to is_closed = true. - Counts & win rate: opportunities_closed, closed_won/closed_lost from is_won, and win_rate_pct = closed_won / opportunities_closed * 100. - avg_days_open: average calendar days between created_at and closed_at. - avg_amount_eur (the fix): for EUR-denominated opportunities, amount_eur = amount directly (no exchange-rate join, which is what caused last night's NULLs). For non-EUR (Castell Fasteners, USD), it divides by the EUR→quote rate from exchange_rates as of the latest date on or before closed_at. - Reference column: tenants.default_currency_code is surfaced as currency_code so the reader can see which merchants are EUR vs USD. - Ordered by win rate ascending, then opportunity volume descending, then merchant name, as requested.”

  5. The report comes back overnight

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

    merchant                      | currency_code | opportunities_closed | closed_won | closed_lost | win_rate_pct | avg_days_open | avg_amount_eur
------------------------------+---------------+----------------------+------------+-------------+--------------+---------------+---------------
Polder Packaging              | EUR           |                   16 |         12 |           4 |         75.0 |          16.4 |         6400.0
Ballycorran Industrial Supply | EUR           |                   76 |         63 |          13 |        82.89 |         15.25 |        4063.16
Midland Safety Wear           | EUR           |                   40 |         34 |           6 |         85.0 |         10.43 |         5342.5
Castell Fasteners             | USD           |                   36 |         31 |           5 |        86.11 |         12.85 |        6835.52
Hanse Industriebedarf         | EUR           |                   17 |         15 |           2 |        88.24 |          9.86 |        5511.76
(5 rows)

  6. What came of it

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

    Later queries that build on this report:

    It builds on:

  7. It becomes benchmark query q182

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

    • spellingfetch first
    • determinismtie-breaker: tenant names are not unique
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • 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 (3×)
    • 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 (5×)
    • placeholderfetch first
    The benchmark text, against the submitted SQL
    SELECT  t0.name AS merchant,  t0.default_currency_code AS currency_code,  COALESCE(s_counts.opportunities_closed, 0) AS opportunities_closed,  COALESCE(s_counts.closed_won, 0) AS closed_won,  COALESCE(s_counts.closed_lost, 0) AS closed_lost,  ROUND(COALESCE(s_counts.closed_won, 0) * 100.0 / NULLIF(s_counts.opportunities_closed, 0), 2) AS win_rate_pct,  ROUND(s_days.avg_days_open, 2) AS avg_days_open,  ROUND(s_amt.avg_amount_eur, 2) AS avg_amount_eur  COALESCE(s_counts.closed_won, 0) * 100.0 / NULLIF(s_counts.opportunities_closed, 0) AS win_rate_pct,  s_days.avg_days_open AS avg_days_open,  s_amt.avg_amount_eur AS avg_amount_eurFROM tenants t0LEFT JOIN (  SELECT    o.tenant_id,    o.tenant_id AS tenant_id,    COUNT(*) AS opportunities_closed,    SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) AS closed_won,    SUM(CASE WHEN NOT o.is_won THEN 1 ELSE 0 END) AS closed_lost  FROM opportunities o  JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id  WHERE o.is_closed = true    AND p.name = 'New business'  GROUP BY o.tenant_id) s_counts ON s_counts.tenant_id = t0.idLEFT JOIN (  SELECT    o.tenant_id,    AVG(EXTRACT(EPOCH FROM (o.closed_at - o.created_at)) / 86400.0) AS avg_days_open    o.tenant_id AS tenant_id,    AVG({{seconds_between(o.created_at, o.closed_at)}} / 86400.0) AS avg_days_open  FROM opportunities o  JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id  WHERE o.is_closed = true    AND p.name = 'New business'  GROUP BY o.tenant_id) s_days ON s_days.tenant_id = t0.idLEFT JOIN (  SELECT    o.tenant_id,    o.tenant_id AS tenant_id,    AVG(      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 avg_amount_eur  FROM opportunities o  JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id  WHERE o.is_closed = true    AND p.name = 'New business'  GROUP BY o.tenant_id) s_amt ON s_amt.tenant_id = t0.idWHERE t0.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')ORDER BY win_rate_pct ASC, opportunities_closed DESC, merchant ASCORDER BY win_rate_pct ASC NULLS LAST, opportunities_closed DESC NULLS FIRST, merchant ASC NULLS LAST, t0.id NULLS LAST

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

    SELECT
  t0.name AS merchant,
  t0.default_currency_code AS currency_code,
  COALESCE(s_counts.opportunities_closed, 0) AS opportunities_closed,
  COALESCE(s_counts.closed_won, 0) AS closed_won,
  COALESCE(s_counts.closed_lost, 0) AS closed_lost,
  COALESCE(s_counts.closed_won, 0) * 100.0 / NULLIF(s_counts.opportunities_closed, 0) AS win_rate_pct,
  s_days.avg_days_open AS avg_days_open,
  s_amt.avg_amount_eur AS avg_amount_eur
FROM tenants t0
LEFT JOIN (
  SELECT
    o.tenant_id AS tenant_id,
    COUNT(*) AS opportunities_closed,
    SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) AS closed_won,
    SUM(CASE WHEN NOT o.is_won THEN 1 ELSE 0 END) AS closed_lost
  FROM opportunities o
  JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id
  WHERE o.is_closed = true
    AND p.name = 'New business'
  GROUP BY o.tenant_id
) s_counts ON s_counts.tenant_id = t0.id
LEFT JOIN (
  SELECT
    o.tenant_id AS tenant_id,
    AVG(CAST(EXTRACT(EPOCH FROM (o.closed_at - o.created_at)) AS NUMERIC(18,6)) / 86400.0) AS avg_days_open
  FROM opportunities o
  JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id
  WHERE o.is_closed = true
    AND p.name = 'New business'
  GROUP BY o.tenant_id
) s_days ON s_days.tenant_id = t0.id
LEFT JOIN (
  SELECT
    o.tenant_id AS tenant_id,
    AVG(
      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 avg_amount_eur
  FROM opportunities o
  JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id
  WHERE o.is_closed = true
    AND p.name = 'New business'
  GROUP BY o.tenant_id
) s_amt ON s_amt.tenant_id = t0.id
WHERE t0.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')
ORDER BY win_rate_pct ASC NULLS LAST, opportunities_closed DESC NULLS FIRST, merchant ASC NULLS LAST, t0.id NULLS LAST
    100 ms1 sHyper227 msDuckDB377 msGizmoSQL365 msclickhouse_brew186 msFirebolterrorDataFusionerrorClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 39, Column 23: 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": "c7420a1b-6da4-4808-9b82-64cc784f3930", "query_lab
    • DataFusionThis feature is not implemented: Physical plan does not support logical expression ScalarSubquery(<subquery>)
    • ClickHouseCode: 48. DB::Exception: Correlated subqueries in aggregate function argument are not supported. (NOT_IMPLEMENTED)

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

    Bea's whole session, 7 turns
    • The request, as Bea received it
      Report request from Daniel Kim (analyst for the Head of B2B Commerce):

Title: B2B Wholesale: Win Rate and Average Deal Size by Merchant (New Business) — Corrected
Question: Does the merchant-level win-rate pattern hold once we also see typical deal size per merchant, and can we fix last night's missing EUR averages?
Grain: one row per wholesale merchant (tenant)
Columns:
- merchant
- currency_code (tenant default)
- opportunities_closed
- closed_won
- closed_lost
- win_rate_pct
- avg_days_open
- avg_amount_eur
Details: Re-run of last night's merchant report, which returned NULL avg_amount_eur for 4 of 5 merchants (all the EUR-denominated ones; only the one USD merchant, Castell Fasteners, returned a value) — almost certainly because converting EUR amounts via a EUR/EUR identity lookup in exchange_rates failed to find a matching row. Filter explicitly: 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. For avg_amount_eur: if currency_code = 'EUR', amount_eur = amount directly (skip the exchange_rates join entirely for EUR rows); otherwise amount_eur = amount / rate from exchange_rates where base_code = 'EUR', quote_code = opportunity.currency_code, as_of = nearest date on or before closed_at. Include tenants.default_currency_code as a reference column so the reader can see which merchant is USD vs EUR. Order by win_rate_pct ascending, tie-break by opportunities_closed descending, then merchant name.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      SELECT
  t0.name AS merchant,
  t0.default_currency_code AS currency_code,
  COALESCE(s_counts.opportunities_closed, 0) AS opportunities_closed,
  COALESCE(s_counts.closed_won, 0) AS closed_won,
  COALESCE(s_counts.closed_lost, 0) AS closed_lost,
  ROUND(COALESCE(s_counts.closed_won, 0) * 100.0 / NULLIF(s_counts.opportunities_closed, 0), 2) AS win_rate_pct,
  ROUND(s_days.avg_days_open, 2) AS avg_days_open,
  ROUND(s_amt.avg_amount_eur, 2) AS avg_amount_eur
FROM tenants t0
LEFT JOIN (
  SELECT
    o.tenant_id,
    COUNT(*) AS opportunities_closed,
    SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) AS closed_won,
    SUM(CASE WHEN NOT o.is_won THEN 1 ELSE 0 END) AS closed_lost
  FROM opportunities o
  JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id
  WHERE o.is_closed = true
    AND p.name = 'New business'
  GROUP BY o.tenant_id
) s_counts ON s_counts.tenant_id = t0.id
LEFT JOIN (
  SELECT
    o.tenant_id,
    AVG(EXTRACT(EPOCH FROM (o.closed_at - o.created_at)) / 86400.0) AS avg_days_open
  FROM opportunities o
  JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id
  WHERE o.is_closed = true
    AND p.name = 'New business'
  GROUP BY o.tenant_id
) s_days ON s_days.tenant_id = t0.id
LEFT JOIN (
  SELECT
    o.tenant_id,
    AVG(
      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 avg_amount_eur
  FROM opportunities o
  JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id
  WHERE o.is_closed = true
    AND p.name = 'New business'
  GROUP BY o.tenant_id
) s_amt ON s_amt.tenant_id = t0.id
WHERE t0.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')
ORDER BY win_rate_pct ASC, opportunities_closed DESC, merchant ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: merchant, currency_code, opportunities_closed, closed_won, closed_lost, win_rate_pct, avg_days_open, avg_amount_eur
    • BeaQuery compiles and returns exactly the requested columns at tenant grain. Submitting.
    • Bea calls submit
      SELECT
  t0.name AS merchant,
  t0.default_currency_code AS currency_code,
  COALESCE(s_counts.opportunities_closed, 0) AS opportunities_closed,
  COALESCE(s_counts.closed_won, 0) AS closed_won,
  COALESCE(s_counts.closed_lost, 0) AS closed_lost,
  ROUND(COALESCE(s_counts.closed_won, 0) * 100.0 / NULLIF(s_counts.opportunities_closed, 0), 2) AS win_rate_pct,
  ROUND(s_days.avg_days_open, 2) AS avg_days_open,
  ROUND(s_amt.avg_amount_eur, 2) AS avg_amount_eur
FROM tenants t0
LEFT JOIN (
  SELECT
    o.tenant_id,
    COUNT(*) AS opportunities_closed,
    SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) AS closed_won,
    SUM(CASE WHEN NOT o.is_won THEN 1 ELSE 0 END) AS closed_lost
  FROM opportunities o
  JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id
  WHERE o.is_closed = true
    AND p.name = 'New business'
  GROUP BY o.tenant_id
) s_counts ON s_counts.tenant_id = t0.id
LEFT JOIN (
  SELECT
    o.tenant_id,
    AVG(EXTRACT(EPOCH FROM (o.closed_at - o.created_at)) / 86400.0) AS avg_days_open
  FROM opportunities o
  JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id
  WHERE o.is_closed = true
    AND p.name = 'New business'
  GROUP BY o.tenant_id
) s_days ON s_days.tenant_id = t0.id
LEFT JOIN (
  SELECT
    o.tenant_id,
    AVG(
      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 avg_amount_eur
  FROM opportunities o
  JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id
  WHERE o.is_closed = true
    AND p.name = 'New business'
  GROUP BY o.tenant_id
) s_amt ON s_amt.tenant_id = t0.id
WHERE t0.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')
ORDER BY win_rate_pct ASC, opportunities_closed DESC, merchant ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. Summary of what the query does: - **Grain:** one row per wholesale merchant (tenant), filtered to the 5 named tenants. - **Join logic:** opportunities joined to each tenant's own `pipelines` row named `'New business'`, restricted to `is_closed = true`. - **Counts & win rate:** `opportunities_closed`, `closed_won`/`closed_lost` from `is_won`, and `win_rate_pct = closed_won / opportunities_closed * 100`. - **avg_days_open:** average calendar days between `created_at` and `closed_at`. - **avg_amount_eur (the fix):** for EUR-denominated opportunities, `amount_eur = amount` directly (no exchange-rate join, which is what caused last night's NULLs). For non-EUR (Castell Fasteners, USD), it divides by the EUR→quote rate from `exchange_rates` as of the latest date on or before `closed_at`. - **Reference column:** `tenants.default_currency_code` is surfaced as `currency_code` so the reader can see which merchants are EUR vs USD. - Ordered by win rate ascending, then opportunity volume descending, then merchant name, as requested.

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

    Bea's transcript (273 KB) · Daniel Kim's transcript (789 KB) · Patrick O'Neill's transcript (263 KB) · the night's plan (7 KB) · the night's reports (5 KB) · the week's memo (3 KB)