DB Olympics

Queries · q168 · Head of Lifecycle Marketing · week 1, night 3

B2B invoice payment behavior: loyalty-enrolled vs not

“Among our B2B wholesaler tenants (industrial supplies, office equipment), do customers enrolled in the loyalty programme settle invoices faster and carry fewer overdue balances than non-enrolled customers?”

Manager · asked the questionAisha RahmanHead of Lifecycle Marketing. Ran customer messaging at a subscription box company.
Analyst · planned the reportIngrid SolbergRisk-focused: looks for the tail, the outliers and the worst cases.
Engineer · wrote the SQLOmarPragmatic: the shortest correct query, aggregates first and joins dimensions afterwards, FILTER clauses for conditional counts.

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 Lifecycle Marketing's desk

    Week 1; shared with Tobias Lindqvist, Chief Financial Officer

    Is the loyalty and referral programme worth it? Do members and referred customers behave differently from others, and what does the programme cost?

    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. Aisha Rahman writes to Ingrid Solberg

    The manager, at the start of week 1

    Subject: Loyalty & referral programme — what I need for Friday's memo

    Hi Ingrid,

    Welcome aboard — thank you for jumping on this in week one. Tobias and I need to give the leadership team a joint, evidence-based view on whether the loyalty and referral programme is paying for itself. The deliverable is your Friday memo, which Tobias and I will use to decide whether we expand the programme, leave it as is, or rework it. Given the slot budget, let's prioritize breadth of the right questions over depth on any one — we can always follow up next week.

    Here's what I'm trying to answer, in my own words:

    1. Do loyalty members actually buy differently? Compared with similar customers who aren't enrolled, do members order more often, spend more, or stick around longer? I want to know if membership correlates with genuinely better customer behavior, not just that our best customers happen to join.
    2. Do referred customers turn into good customers? When someone comes in through a referral, how do they behave in their first months compared to customers acquired other ways — do they buy, do they come back, do they stay?
    3. What is the programme actually costing us? I want a sense of the points/rewards and referral payouts we're giving out, ideally relative to the revenue those same members or referred customers generate, so we can see if the economics make sense.
    4. Is participation worth the overhead? How many of our customers are actually enrolled or have used a referral code, and is that share growing or flat? If take-up is small, the upside is capped no matter how good the behavior looks.
    5. Any red flags on how the programme is used? For example, signs that rewards are being redeemed without real repeat purchase behavior behind them, or that referral activity is concentrated in a way that looks more like gaming than genuine word-of-mouth.

    Given we only have a few report slots a night, I'd rank these in order 3, 1, 2 as the must-haves for Friday; 4 and 5 are nice-to-haves if slots allow. Please loop in whatever Tobias's analyst is already pulling on the finance side so we're not duplicating report requests.

    Thanks, Ingrid — happy to talk through any of this before you start drafting requests.

    Best, Aisha

    A note from the Chief Financial Officer, Tobias Lindqvist

    Subject: Finance angle on the loyalty/referral review

    Hi Ingrid,

    A few things I'd like us to cover as you scope this:

    1. What is the programme actually costing us net — points and referral rewards issued versus redeemed/liability outstanding — and how does that compare to the margin uplift (if any) from members? I don't want to judge this on engagement alone.
    2. Do members and referred customers carry a different refund/return profile than regular customers? If the programme is quietly skewing our refund rate, that matters for how we read "incremental" revenue.
    3. For the B2B side especially, are enrolled accounts paying on better terms — faster settlement, fewer overdue balances — or is that unrelated to the programme?

    Happy to look at a first cut together before it goes wider.

    Tobias

  3. Ingrid Solberg plans the night

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

    Night 2 sharpened the picture. The tenure-controlled cut (Report 1) rules out the 'members are just newer accounts' explanation: at every tenure band members order less, spend less, and are refunded 2x more than non-members - and the refund gap actually widens with tenure (27% to 34% for members vs 20% down to 14% for non-members). So this isn't a cohort artifact; it's a persistent, worsening pattern. The before/after enrollment report (Report 2) came back with zero pre-enrollment orders for everyone - meaning enrollment essentially happens at or before a member's very first order (likely at signup), so there is no 'pre' baseline to diff against; that test was a dead end for isolating causality but confirms enrollment is immediate, not something people do after building a track record. Report 3 shows no smoking-gun concentration: redemption counts per referrer are almost all 1 (occasional 2), spread across dozens of tenants and referrers with normal-looking order histories (from near-zero to tens of thousands of EUR) - this looks like genuine light word-of-mouth, not gaming, though volume is tiny. For the last full night before the memo, I want to: (1) directly test Aisha's 'rewards redeemed without real repeat purchase' hypothesis by checking whether orders where points were actually redeemed show elevated refund rates versus other member orders - this could explain the mechanism behind the refund gap; (2) quantify participation and its trend for Aisha's #4; (3) close out Tobias's B2B payment-terms question, which is still untouched. I'm leaving one night in reserve for any necessary follow-up before Friday.

    B2B invoice payment behavior: loyalty-enrolled vs not
    Question
    Among our B2B wholesaler tenants (industrial supplies, office equipment), do customers enrolled in the loyalty programme settle invoices faster and carry fewer overdue balances than non-enrolled customers?
    One row per
    segment: 'loyalty member' vs 'non-member', within B2B wholesaler tenants only
    Columns
    • segment
    • customer_count
    • invoice_count
    • avg_days_to_pay
    • pct_invoices_paid_late
    • pct_invoices_currently_overdue
    • avg_invoice_amount_eur

  4. Omar writes the SQL

    The engineer · 2 dry runs on the empty schema, then the submission

    Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)
    SELECT id, name, slug, industry FROM tenants ORDER BY id
    Dry run 2 · 1 change from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)
    SELECT id, name, slug, industry FROM tenants ORDER BY idWITH b2b_tenants AS (  SELECT id, name  FROM tenants  WHERE lower(coalesce(industry,'')) LIKE '%industrial%supplies%'     OR lower(coalesce(industry,'')) LIKE '%office%equipment%'     OR lower(coalesce(industry,'')) LIKE '%industrial%'     OR lower(coalesce(industry,'')) LIKE '%wholesale%'     OR lower(coalesce(name,''))     LIKE '%industrial%'     OR lower(coalesce(name,''))     LIKE '%office%equipment%'     OR lower(coalesce(slug,''))     LIKE '%industrial%'     OR lower(coalesce(slug,''))     LIKE '%office-equipment%'),scoped_invoices AS (  SELECT i.*  FROM invoices i  JOIN b2b_tenants t ON t.id = i.tenant_id  WHERE i.issued_at >= DATE '2023-01-01'    AND i.issued_at <  DATE '2026-01-01'),loyalty_users AS (  SELECT DISTINCT tenant_id, user_id  FROM loyalty_accounts),segmented AS (  SELECT    si.*,    CASE WHEN lu.user_id IS NOT NULL THEN 'loyalty member' ELSE 'non-member' END AS segment  FROM scoped_invoices si  LEFT JOIN loyalty_users lu    ON lu.tenant_id = si.tenant_id AND lu.user_id = si.user_id),eur_amounts AS (  SELECT    s.*,    s.total / NULLIF(er.rate, 0) AS total_eur  FROM segmented s  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = s.currency_code   AND er.as_of = CAST(s.issued_at AS date))SELECT  segment,  COUNT(DISTINCT user_id) AS customer_count,  COUNT(*) AS invoice_count,  ROUND(AVG(EXTRACT(EPOCH FROM (paid_at - issued_at)) / 86400.0) FILTER (WHERE status = 'paid'), 2) AS avg_days_to_pay,  ROUND(    100.0 * COUNT(*) FILTER (WHERE status = 'paid' AND paid_at > due_at)    / NULLIF(COUNT(*) FILTER (WHERE status = 'paid'), 0), 2  ) AS pct_invoices_paid_late,  ROUND(    100.0 * COUNT(*) FILTER (      WHERE status = 'past_due'         OR (status = 'open' AND due_at < DATE '2025-12-31' AND amount_due > 0)    ) / NULLIF(COUNT(*), 0), 2  ) AS pct_invoices_currently_overdue,  ROUND(AVG(total_eur), 2) AS avg_invoice_amount_eurFROM eur_amountsGROUP BY segmentORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END, segment
    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: - Scope: tenants whose industry/name/slug match industrial-supplies/office-equipment/wholesale patterns. - Segment: invoice user_id is "loyalty member" if any loyalty_accounts row exists for that tenant+user, else "non-member". - Metrics per segment: distinct customers, invoice count (2023‑01‑01 to 2025‑12‑31), average days from issued_at to paid_at for paid invoices, % of paid invoices paid after due_at, % of all invoices currently overdue (past_due, or open with due_at < 2025‑12‑31 and amount_due > 0), and average invoice total converted to EUR via exchange_rates (EUR→currency rate as of the issue date). Note flagged to the analyst: since I only have dry-run (schema-only) access, I couldn't literally enumerate the matched tenant names for the report notes — I described the exact matching rule instead and suggested a quick follow-up pull if sign-off on the roster is needed.”

  5. The report comes back overnight

    As Ingrid Solberg saw it, from the small dataset the agents worked on

    segment    | customer_count | invoice_count | avg_days_to_pay | pct_invoices_paid_late | pct_invoices_currently_overdue | avg_invoice_amount_eur
-----------+----------------+---------------+-----------------+------------------------+--------------------------------+-----------------------
non-member |            164 |          1287 |           41.42 |                  74.63 |                           0.62 |                 2203.5
(1 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:

  7. It becomes benchmark query q168

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

    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6)
    • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
    • 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×)
    The benchmark text, against the submitted SQL
    WITH b2b_tenants AS (  SELECT id, name  FROM tenants  WHERE lower(coalesce(industry,'')) LIKE '%industrial%supplies%'     OR lower(coalesce(industry,'')) LIKE '%office%equipment%'     OR lower(coalesce(industry,'')) LIKE '%industrial%'     OR lower(coalesce(industry,'')) LIKE '%wholesale%'     OR lower(coalesce(name,''))     LIKE '%industrial%'     OR lower(coalesce(name,''))     LIKE '%office%equipment%'     OR lower(coalesce(slug,''))     LIKE '%industrial%'     OR lower(coalesce(slug,''))     LIKE '%office-equipment%'),scoped_invoices AS (  SELECT i.*  FROM invoices i  JOIN b2b_tenants t ON t.id = i.tenant_id  WHERE i.issued_at >= DATE '2023-01-01'    AND i.issued_at <  DATE '2026-01-01'),loyalty_users AS (  SELECT DISTINCT tenant_id, user_id  FROM loyalty_accounts),segmented AS (  SELECT    si.*,    CASE WHEN lu.user_id IS NOT NULL THEN 'loyalty member' ELSE 'non-member' END AS segment  FROM scoped_invoices si  LEFT JOIN loyalty_users lu    ON lu.tenant_id = si.tenant_id AND lu.user_id = si.user_id),eur_amounts AS (  SELECT    s.*,    s.total / NULLIF(er.rate, 0) AS total_eur    CAST(s.total / NULLIF(er.rate, 0) AS NUMERIC(18,6)) AS total_eur  FROM segmented s  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = s.currency_code   AND er.as_of = CAST(s.issued_at AS date))SELECT  segment,  COUNT(DISTINCT user_id) AS customer_count,  COUNT(*) AS invoice_count,  ROUND(AVG(EXTRACT(EPOCH FROM (paid_at - issued_at)) / 86400.0) FILTER (WHERE status = 'paid'), 2) AS avg_days_to_pay,  ROUND(    100.0 * COUNT(*) FILTER (WHERE status = 'paid' AND paid_at > due_at)    / NULLIF(COUNT(*) FILTER (WHERE status = 'paid'), 0), 2  ) AS pct_invoices_paid_late,  ROUND(    100.0 * COUNT(*) FILTER (      WHERE status = 'past_due'         OR (status = 'open' AND due_at < DATE '2025-12-31' AND amount_due > 0)    ) / NULLIF(COUNT(*), 0), 2  ) AS pct_invoices_currently_overdue,  ROUND(AVG(total_eur), 2) AS avg_invoice_amount_eur  AVG({{seconds_between(issued_at, paid_at)}} / 86400.0) FILTER (WHERE status = 'paid') AS avg_days_to_pay,  100.0 * COUNT(*) FILTER (WHERE status = 'paid' AND paid_at > due_at)  / NULLIF(COUNT(*) FILTER (WHERE status = 'paid'), 0) AS pct_invoices_paid_late,  100.0 * COUNT(*) FILTER (    WHERE status = 'past_due'       OR (status = 'open' AND due_at < DATE '2025-12-31' AND amount_due > 0)  ) / NULLIF(COUNT(*), 0) AS pct_invoices_currently_overdue,  AVG(total_eur) AS avg_invoice_amount_eurFROM eur_amountsGROUP BY segmentORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END, segmentORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END NULLS LAST, segment NULLS LAST

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

    WITH b2b_tenants AS (
  SELECT id, name
  FROM tenants
  WHERE lower(coalesce(industry,'')) LIKE '%industrial%supplies%'
     OR lower(coalesce(industry,'')) LIKE '%office%equipment%'
     OR lower(coalesce(industry,'')) LIKE '%industrial%'
     OR lower(coalesce(industry,'')) LIKE '%wholesale%'
     OR lower(coalesce(name,''))     LIKE '%industrial%'
     OR lower(coalesce(name,''))     LIKE '%office%equipment%'
     OR lower(coalesce(slug,''))     LIKE '%industrial%'
     OR lower(coalesce(slug,''))     LIKE '%office-equipment%'
),
scoped_invoices AS (
  SELECT i.*
  FROM invoices i
  JOIN b2b_tenants t ON t.id = i.tenant_id
  WHERE i.issued_at >= DATE '2023-01-01'
    AND i.issued_at <  DATE '2026-01-01'
),
loyalty_users AS (
  SELECT DISTINCT tenant_id, user_id
  FROM loyalty_accounts
),
segmented AS (
  SELECT
    si.*,
    CASE WHEN lu.user_id IS NOT NULL THEN 'loyalty member' ELSE 'non-member' END AS segment
  FROM scoped_invoices si
  LEFT JOIN loyalty_users lu
    ON lu.tenant_id = si.tenant_id AND lu.user_id = si.user_id
),
eur_amounts AS (
  SELECT
    s.*,
    CAST(s.total / NULLIF(er.rate, 0) AS NUMERIC(18,6)) AS total_eur
  FROM segmented s
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = s.currency_code
   AND er.as_of = CAST(s.issued_at AS date)
)
SELECT
  segment,
  COUNT(DISTINCT user_id) AS customer_count,
  COUNT(*) AS invoice_count,
  AVG(CAST(EXTRACT(EPOCH FROM (paid_at - issued_at)) AS NUMERIC(18,6)) / 86400.0) FILTER (WHERE status = 'paid') AS avg_days_to_pay,
  100.0 * COUNT(*) FILTER (WHERE status = 'paid' AND paid_at > due_at)
  / NULLIF(COUNT(*) FILTER (WHERE status = 'paid'), 0) AS pct_invoices_paid_late,
  100.0 * COUNT(*) FILTER (
    WHERE status = 'past_due'
       OR (status = 'open' AND due_at < DATE '2025-12-31' AND amount_due > 0)
  ) / NULLIF(COUNT(*), 0) AS pct_invoices_currently_overdue,
  AVG(total_eur) AS avg_invoice_amount_eur
FROM eur_amounts
GROUP BY segment
ORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END NULLS LAST, segment NULLS LAST
    10 ms100 ms1 sHyper67.7 msDuckDB96.5 msGizmoSQL104 msclickhouse_brew187 msFirebolterrorDataFusion117 msClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 35, Column 18: 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": "e33492b3-1392-429e-987d-3f3141e938d5", "query_lab
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table2.total Decimal(14, 4) Decimal64(size = 0), nullIf(__table9.rate, 0_UInt8) Nullable(Decimal(12, 6)) Nullable(size = 0, Decimal64(size = 0), UInt8(size = 0)). (ARGUMENT_OUT

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

    Omar's whole session, 8 turns
    • The request, as Omar received it
      Report request from Ingrid Solberg (analyst for the Head of Lifecycle Marketing):

Title: B2B invoice payment behavior: loyalty-enrolled vs not
Question: Among our B2B wholesaler tenants (industrial supplies, office equipment), do customers enrolled in the loyalty programme settle invoices faster and carry fewer overdue balances than non-enrolled customers?
Grain: one row per segment: 'loyalty member' vs 'non-member', within B2B wholesaler tenants only
Columns:
- segment
- customer_count
- invoice_count
- avg_days_to_pay
- pct_invoices_paid_late
- pct_invoices_currently_overdue
- avg_invoice_amount_eur
Details: Scope to tenants whose industry/vertical is a B2B wholesaler (industrial supplies or office equipment - identify via tenants.industry or tenants.name/slug matching those verticals; list which tenants were included in the report notes). Within those tenants, segment users (invoices.user_id) into 'loyalty member' (has a loyalty_accounts row) vs 'non-member'. invoice_count = count of invoices (any status) issued 2023-01-01 to 2025-12-31 for users in that segment. avg_days_to_pay = average of (paid_at - issued_at) in days for invoices with status = 'paid'. pct_invoices_paid_late = share of paid invoices where paid_at is after due_at. pct_invoices_currently_overdue = share of all invoices in the segment with status = 'past_due', or status = 'open' and due_at before 2025-12-31 and amount_due greater than 0. avg_invoice_amount_eur = average invoices.total converted to EUR via exchange_rates (currency_code to EUR, rate as_of issued_at). Order rows: 'loyalty member' first, then 'non-member'.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Omar calls dry_run
      SELECT id, name, slug, industry FROM tenants ORDER BY id
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: id, name, slug, industry
    • Omar calls dry_run
      WITH b2b_tenants AS (
  SELECT id, name
  FROM tenants
  WHERE lower(coalesce(industry,'')) LIKE '%industrial%supplies%'
     OR lower(coalesce(industry,'')) LIKE '%office%equipment%'
     OR lower(coalesce(industry,'')) LIKE '%industrial%'
     OR lower(coalesce(industry,'')) LIKE '%wholesale%'
     OR lower(coalesce(name,''))     LIKE '%industrial%'
     OR lower(coalesce(name,''))     LIKE '%office%equipment%'
     OR lower(coalesce(slug,''))     LIKE '%industrial%'
     OR lower(coalesce(slug,''))     LIKE '%office-equipment%'
),
scoped_invoices AS (
  SELECT i.*
  FROM invoices i
  JOIN b2b_tenants t ON t.id = i.tenant_id
  WHERE i.issued_at >= DATE '2023-01-01'
    AND i.issued_at <  DATE '2026-01-01'
),
loyalty_users AS (
  SELECT DISTINCT tenant_id, user_id
  FROM loyalty_accounts
),
segmented AS (
  SELECT
    si.*,
    CASE WHEN lu.user_id IS NOT NULL THEN 'loyalty member' ELSE 'non-member' END AS segment
  FROM scoped_invoices si
  LEFT JOIN loyalty_users lu
    ON lu.tenant_id = si.tenant_id AND lu.user_id = si.user_id
),
eur_amounts AS (
  SELECT
    s.*,
    s.total / NULLIF(er.rate, 0) AS total_eur
  FROM segmented s
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = s.currency_code
   AND er.as_of = CAST(s.issued_at AS date)
)
SELECT
  segment,
  COUNT(DISTINCT user_id) AS customer_count,
  COUNT(*) AS invoice_count,
  ROUND(AVG(EXTRACT(EPOCH FROM (paid_at - issued_at)) / 86400.0) FILTER (WHERE status = 'paid'), 2) AS avg_days_to_pay,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE status = 'paid' AND paid_at > due_at)
    / NULLIF(COUNT(*) FILTER (WHERE status = 'paid'), 0), 2
  ) AS pct_invoices_paid_late,
  ROUND(
    100.0 * COUNT(*) FILTER (
      WHERE status = 'past_due'
         OR (status = 'open' AND due_at < DATE '2025-12-31' AND amount_due > 0)
    ) / NULLIF(COUNT(*), 0), 2
  ) AS pct_invoices_currently_overdue,
  ROUND(AVG(total_eur), 2) AS avg_invoice_amount_eur
FROM eur_amounts
GROUP BY segment
ORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END, segment
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: segment, customer_count, invoice_count, avg_days_to_pay, pct_invoices_paid_late, pct_invoices_currently_overdue, avg_invoice_amount_eur
    • Omar calls submit
      WITH b2b_tenants AS (
  SELECT id, name
  FROM tenants
  WHERE lower(coalesce(industry,'')) LIKE '%industrial%supplies%'
     OR lower(coalesce(industry,'')) LIKE '%office%equipment%'
     OR lower(coalesce(industry,'')) LIKE '%industrial%'
     OR lower(coalesce(industry,'')) LIKE '%wholesale%'
     OR lower(coalesce(name,''))     LIKE '%industrial%'
     OR lower(coalesce(name,''))     LIKE '%office%equipment%'
     OR lower(coalesce(slug,''))     LIKE '%industrial%'
     OR lower(coalesce(slug,''))     LIKE '%office-equipment%'
),
scoped_invoices AS (
  SELECT i.*
  FROM invoices i
  JOIN b2b_tenants t ON t.id = i.tenant_id
  WHERE i.issued_at >= DATE '2023-01-01'
    AND i.issued_at <  DATE '2026-01-01'
),
loyalty_users AS (
  SELECT DISTINCT tenant_id, user_id
  FROM loyalty_accounts
),
segmented AS (
  SELECT
    si.*,
    CASE WHEN lu.user_id IS NOT NULL THEN 'loyalty member' ELSE 'non-member' END AS segment
  FROM scoped_invoices si
  LEFT JOIN loyalty_users lu
    ON lu.tenant_id = si.tenant_id AND lu.user_id = si.user_id
),
eur_amounts AS (
  SELECT
    s.*,
    s.total / NULLIF(er.rate, 0) AS total_eur
  FROM segmented s
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = s.currency_code
   AND er.as_of = CAST(s.issued_at AS date)
)
SELECT
  segment,
  COUNT(DISTINCT user_id) AS customer_count,
  COUNT(*) AS invoice_count,
  ROUND(AVG(EXTRACT(EPOCH FROM (paid_at - issued_at)) / 86400.0) FILTER (WHERE status = 'paid'), 2) AS avg_days_to_pay,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE status = 'paid' AND paid_at > due_at)
    / NULLIF(COUNT(*) FILTER (WHERE status = 'paid'), 0), 2
  ) AS pct_invoices_paid_late,
  ROUND(
    100.0 * COUNT(*) FILTER (
      WHERE status = 'past_due'
         OR (status = 'open' AND due_at < DATE '2025-12-31' AND amount_due > 0)
    ) / NULLIF(COUNT(*), 0), 2
  ) AS pct_invoices_currently_overdue,
  ROUND(AVG(total_eur), 2) AS avg_invoice_amount_eur
FROM eur_amounts
GROUP BY segment
ORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END, segment
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • OmarSubmitted. Summary of what the query does: - **Scope:** tenants whose `industry`/`name`/`slug` match industrial-supplies/office-equipment/wholesale patterns. - **Segment:** invoice `user_id` is "loyalty member" if any `loyalty_accounts` row exists for that tenant+user, else "non-member". - **Metrics per segment:** distinct customers, invoice count (2023‑01‑01 to 2025‑12‑31), average days from `issued_at` to `paid_at` for paid invoices, % of paid invoices paid after `due_at`, % of all invoices currently overdue (`past_due`, or `open` with `due_at < 2025‑12‑31` and `amount_due > 0`), and average invoice `total` converted to EUR via `exchange_rates` (EUR→currency rate as of the issue date). Note flagged to the analyst: since I only have dry-run (schema-only) access, I couldn't literally enumerate the matched tenant names for the report notes — I described the exact matching rule instead and suggested a quick follow-up pull if sign-off on the roster is needed.

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

    Omar's transcript (276 KB) · Ingrid Solberg's transcript (906 KB) · Aisha Rahman's transcript (261 KB) · the night's plan (6 KB) · the night's reports (5 KB) · the week's memo (4 KB)