Queries · q169 · Head of Lifecycle Marketing · week 1, night 4

Loyalty and referral programme reach by merchant vertical

“In which merchant verticals does the loyalty/referral programme even exist and get adopted - is B2B wholesale genuinely excluded, and is participation concentrated in a few verticals?”

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

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
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
Ingrid Solberg plans the night
The analyst · week 1, night 4, request 1 of 3
Two of last night's reports came back collapsed to a single row - which is itself informative, not just noise. Report 1 found zero member orders with a matched points-redemption transaction, so the 'reward-driven returns' hypothesis couldn't be tested via order_id linkage (either redemptions don't record order_id, or genuinely none exist in this join); I'm treating that specific mechanism as unconfirmed rather than ruled out, and won't push further on it given limited slots. Report 3 found literally zero loyalty-enrolled customers among B2B wholesaler tenants - before reporting 'the programme isn't offered/adopted in B2B' to Tobias as fact, I want to confirm it's a real pattern (the program simply isn't live there) rather than an artifact of the engineer's fuzzy industry-name matching. Report 2 confirms loyalty penetration plateaued at roughly 15 to 16 percent of customers for two years and referral penetration is still under 1 percent after three years - participation is small and flat, capping any upside regardless of behavior quality. For this final night I'm spending slots on: (1) a clean vertical-level view of where loyalty/referral even exist and are adopted - this resolves the B2B ambiguity and shows whether participation is concentrated in a few verticals; (2) checking whether the member refund-rate penalty is a generalizable pattern or an artifact of one or two verticals' return-prone mix, since that materially changes how we would advise fixing it; (3) a single consolidated unit-economics table (points cost versus revenue, per customer, member versus non-member) to give Tobias and Aisha the bottom-line ROI number the memo needs. This closes out the must-haves (1, 2, 3) with a robustness check and gives a clean answer on 3, 4 and 5 enough to write a defensible Friday memo.
Loyalty and referral programme reach by merchant vertical
Question
In which merchant verticals does the loyalty/referral programme even exist and get adopted - is B2B wholesale genuinely excluded, and is participation concentrated in a few verticals?
One row per
tenant industry/vertical (tenants.industry)
Columns
- industry
- tenant_count
- tenants_with_any_loyalty_program
- active_customers
- loyalty_members
- pct_customers_enrolled
- referred_customers
- pct_customers_referred

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.industry AS industry,
  t0.tenant_count AS tenant_count,
  COALESCE(t1.tenants_with_any_loyalty_program, 0) AS tenants_with_any_loyalty_program,
  COALESCE(t2.active_customers, 0) AS active_customers,
  COALESCE(t3.loyalty_members, 0) AS loyalty_members,
  CASE WHEN COALESCE(t2.active_customers, 0) = 0 THEN 0
       ELSE ROUND(100.0 * COALESCE(t3.loyalty_members, 0) / t2.active_customers, 2)
  END AS pct_customers_enrolled,
  COALESCE(t4.referred_customers, 0) AS referred_customers,
  CASE WHEN COALESCE(t2.active_customers, 0) = 0 THEN 0
       ELSE ROUND(100.0 * COALESCE(t4.referred_customers, 0) / t2.active_customers, 2)
  END AS pct_customers_referred
FROM (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT t.id) AS tenant_count
  FROM tenants t
  GROUP BY COALESCE(t.industry, 'Unknown')
) t0
LEFT JOIN (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT t.id) AS tenants_with_any_loyalty_program
  FROM tenants t
  JOIN loyalty_programs lp ON lp.tenant_id = t.id
  GROUP BY COALESCE(t.industry, 'Unknown')
) t1 ON t1.industry = t0.industry
LEFT JOIN (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT u.id) AS active_customers
  FROM tenants t
  JOIN users u ON u.tenant_id = t.id AND u.is_staff = false
  JOIN orders o ON o.user_id = u.id AND o.tenant_id = t.id
  WHERE o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
  GROUP BY COALESCE(t.industry, 'Unknown')
) t2 ON t2.industry = t0.industry
LEFT JOIN (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT u.id) AS loyalty_members
  FROM tenants t
  JOIN users u ON u.tenant_id = t.id AND u.is_staff = false
  JOIN orders o ON o.user_id = u.id AND o.tenant_id = t.id
  JOIN loyalty_accounts la ON la.user_id = u.id AND la.tenant_id = t.id
  WHERE o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
  GROUP BY COALESCE(t.industry, 'Unknown')
) t3 ON t3.industry = t0.industry
LEFT JOIN (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT u.id) AS referred_customers
  FROM tenants t
  JOIN users u ON u.tenant_id = t.id AND u.is_staff = false
  JOIN orders o ON o.user_id = u.id AND o.tenant_id = t.id
  JOIN referral_redemptions rr ON rr.referred_user_id = u.id AND rr.tenant_id = t.id
  WHERE o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
  GROUP BY COALESCE(t.industry, 'Unknown')
) t4 ON t4.industry = t0.industry
ORDER BY COALESCE(t2.active_customers, 0) DESC, t0.industry ASC

“Query compiles with the expected 8 columns at the tenant-industry grain. Submitting now.”

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

“Submitted. The report groups tenants by industry (free-text tenants without a value fall into an "Unknown" bucket so no tenant is silently dropped), and computes tenant counts, loyalty-program presence, active customers, enrollment, and referral reach per vertical, ordered by active customers descending — which should directly show whether B2B wholesale verticals are excluded from loyalty/referral programs and whether adoption is concentrated elsewhere.”

The report comes back overnight

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

industry                  | tenant_count | tenants_with_any_loyalty_program | active_customers | loyalty_members | pct_customers_enrolled | referred_customers | pct_customers_referred
--------------------------+--------------+----------------------------------+------------------+-----------------+------------------------+--------------------+-----------------------
Apparel & Footwear        |           25 |                               10 |             4364 |             953 |                  21.84 |                 24 |                   0.55
Home & Living             |           16 |                                4 |             1270 |              42 |                   3.31 |                  9 |                   0.71
Food & Coffee             |           11 |                                1 |             1226 |               7 |                   0.57 |                 15 |                   1.22
Sports & Outdoor          |           13 |                                6 |              747 |             206 |                  27.58 |                  3 |                    0.4
Beauty                    |           15 |                                4 |              713 |              57 |                   7.99 |                  6 |                   0.84
Consumer Electronics      |            9 |                                3 |              476 |              42 |                   8.82 |                  2 |                   0.42
Pet Supplies              |           10 |                                1 |              316 |              14 |                   4.43 |                  3 |                   0.95
Industrial Supplies (B2B) |            5 |                                0 |              239 |               0 |                    0.0 |                  3 |                   1.26
Office Supplies (B2B)     |            4 |                                0 |              151 |               0 |                    0.0 |                  2 |                   1.32
Books & Stationery        |            6 |                                1 |               77 |               9 |                  11.69 |                  0 |                    0.0
Jewelry & Accessories     |            6 |                                1 |               61 |               0 |                    0.0 |                  0 |                    0.0
(11 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
It builds on:
- q168B2B invoice payment behavior: loyalty-enrolled vs not
- q167Loyalty and referral programme participation rate over time

It becomes benchmark query q169

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

numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
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

SELECT  t0.industry AS industry,  t0.tenant_count AS tenant_count,  COALESCE(t1.tenants_with_any_loyalty_program, 0) AS tenants_with_any_loyalty_program,  COALESCE(t2.active_customers, 0) AS active_customers,  COALESCE(t3.loyalty_members, 0) AS loyalty_members,  CASE WHEN COALESCE(t2.active_customers, 0) = 0 THEN 0       ELSE ROUND(100.0 * COALESCE(t3.loyalty_members, 0) / t2.active_customers, 2)       ELSE 100.0 * COALESCE(t3.loyalty_members, 0) / t2.active_customers  END AS pct_customers_enrolled,  COALESCE(t4.referred_customers, 0) AS referred_customers,  CASE WHEN COALESCE(t2.active_customers, 0) = 0 THEN 0       ELSE ROUND(100.0 * COALESCE(t4.referred_customers, 0) / t2.active_customers, 2)       ELSE 100.0 * COALESCE(t4.referred_customers, 0) / t2.active_customers  END AS pct_customers_referredFROM (  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT t.id) AS tenant_count  FROM tenants t  GROUP BY COALESCE(t.industry, 'Unknown')) t0LEFT JOIN (  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT t.id) AS tenants_with_any_loyalty_program  FROM tenants t  JOIN loyalty_programs lp ON lp.tenant_id = t.id  GROUP BY COALESCE(t.industry, 'Unknown')) t1 ON t1.industry = t0.industryLEFT JOIN (  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT u.id) AS active_customers  FROM tenants t  JOIN users u ON u.tenant_id = t.id AND u.is_staff = false  JOIN orders o ON o.user_id = u.id AND o.tenant_id = t.id  WHERE o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'  GROUP BY COALESCE(t.industry, 'Unknown')) t2 ON t2.industry = t0.industryLEFT JOIN (  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT u.id) AS loyalty_members  FROM tenants t  JOIN users u ON u.tenant_id = t.id AND u.is_staff = false  JOIN orders o ON o.user_id = u.id AND o.tenant_id = t.id  JOIN loyalty_accounts la ON la.user_id = u.id AND la.tenant_id = t.id  WHERE o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'  GROUP BY COALESCE(t.industry, 'Unknown')) t3 ON t3.industry = t0.industryLEFT JOIN (  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT u.id) AS referred_customers  FROM tenants t  JOIN users u ON u.tenant_id = t.id AND u.is_staff = false  JOIN orders o ON o.user_id = u.id AND o.tenant_id = t.id  JOIN referral_redemptions rr ON rr.referred_user_id = u.id AND rr.tenant_id = t.id  WHERE o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'  GROUP BY COALESCE(t.industry, 'Unknown')) t4 ON t4.industry = t0.industryORDER BY COALESCE(t2.active_customers, 0) DESC, t0.industry ASCORDER BY COALESCE(t2.active_customers, 0) DESC NULLS FIRST, t0.industry ASC NULLS LAST

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

SELECT
  t0.industry AS industry,
  t0.tenant_count AS tenant_count,
  COALESCE(t1.tenants_with_any_loyalty_program, 0) AS tenants_with_any_loyalty_program,
  COALESCE(t2.active_customers, 0) AS active_customers,
  COALESCE(t3.loyalty_members, 0) AS loyalty_members,
  CASE WHEN COALESCE(t2.active_customers, 0) = 0 THEN 0
       ELSE 100.0 * COALESCE(t3.loyalty_members, 0) / t2.active_customers
  END AS pct_customers_enrolled,
  COALESCE(t4.referred_customers, 0) AS referred_customers,
  CASE WHEN COALESCE(t2.active_customers, 0) = 0 THEN 0
       ELSE 100.0 * COALESCE(t4.referred_customers, 0) / t2.active_customers
  END AS pct_customers_referred
FROM (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT t.id) AS tenant_count
  FROM tenants t
  GROUP BY COALESCE(t.industry, 'Unknown')
) t0
LEFT JOIN (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT t.id) AS tenants_with_any_loyalty_program
  FROM tenants t
  JOIN loyalty_programs lp ON lp.tenant_id = t.id
  GROUP BY COALESCE(t.industry, 'Unknown')
) t1 ON t1.industry = t0.industry
LEFT JOIN (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT u.id) AS active_customers
  FROM tenants t
  JOIN users u ON u.tenant_id = t.id AND u.is_staff = false
  JOIN orders o ON o.user_id = u.id AND o.tenant_id = t.id
  WHERE o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
  GROUP BY COALESCE(t.industry, 'Unknown')
) t2 ON t2.industry = t0.industry
LEFT JOIN (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT u.id) AS loyalty_members
  FROM tenants t
  JOIN users u ON u.tenant_id = t.id AND u.is_staff = false
  JOIN orders o ON o.user_id = u.id AND o.tenant_id = t.id
  JOIN loyalty_accounts la ON la.user_id = u.id AND la.tenant_id = t.id
  WHERE o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
  GROUP BY COALESCE(t.industry, 'Unknown')
) t3 ON t3.industry = t0.industry
LEFT JOIN (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT u.id) AS referred_customers
  FROM tenants t
  JOIN users u ON u.tenant_id = t.id AND u.is_staff = false
  JOIN orders o ON o.user_id = u.id AND o.tenant_id = t.id
  JOIN referral_redemptions rr ON rr.referred_user_id = u.id AND rr.tenant_id = t.id
  WHERE o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
  GROUP BY COALESCE(t.industry, 'Unknown')
) t4 ON t4.industry = t0.industry
ORDER BY COALESCE(t2.active_customers, 0) DESC NULLS FIRST, t0.industry ASC NULLS LAST

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

Bea's whole session, 7 turns

The request, as Bea received it

Report request from Ingrid Solberg (analyst for the Head of Lifecycle Marketing):

Title: Loyalty and referral programme reach by merchant vertical
Question: In which merchant verticals does the loyalty/referral programme even exist and get adopted - is B2B wholesale genuinely excluded, and is participation concentrated in a few verticals?
Grain: one row per tenant industry/vertical (tenants.industry)
Columns:
- industry
- tenant_count
- tenants_with_any_loyalty_program
- active_customers
- loyalty_members
- pct_customers_enrolled
- referred_customers
- pct_customers_referred
Details: Group all tenants by tenants.industry. tenant_count = distinct tenants in that industry. tenants_with_any_loyalty_program = count of distinct tenants in that industry that have at least one row in loyalty_programs (any status, any date). active_customers = distinct users (is_staff = false) belonging to tenants in that industry who placed at least one order between 2023-01-01 and 2025-12-31. loyalty_members = distinct users among those active customers who have a loyalty_accounts row. pct_customers_enrolled = loyalty_members divided by active_customers, as a percentage. referred_customers = distinct users among those active customers who appear as referred_user_id in referral_redemptions. pct_customers_referred = referred_customers divided by active_customers, as a percentage. Order by active_customers descending, then industry.

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

Bea calls dry_run

SELECT
  t0.industry AS industry,
  t0.tenant_count AS tenant_count,
  COALESCE(t1.tenants_with_any_loyalty_program, 0) AS tenants_with_any_loyalty_program,
  COALESCE(t2.active_customers, 0) AS active_customers,
  COALESCE(t3.loyalty_members, 0) AS loyalty_members,
  CASE WHEN COALESCE(t2.active_customers, 0) = 0 THEN 0
       ELSE ROUND(100.0 * COALESCE(t3.loyalty_members, 0) / t2.active_customers, 2)
  END AS pct_customers_enrolled,
  COALESCE(t4.referred_customers, 0) AS referred_customers,
  CASE WHEN COALESCE(t2.active_customers, 0) = 0 THEN 0
       ELSE ROUND(100.0 * COALESCE(t4.referred_customers, 0) / t2.active_customers, 2)
  END AS pct_customers_referred
FROM (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT t.id) AS tenant_count
  FROM tenants t
  GROUP BY COALESCE(t.industry, 'Unknown')
) t0
LEFT JOIN (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT t.id) AS tenants_with_any_loyalty_program
  FROM tenants t
  JOIN loyalty_programs lp ON lp.tenant_id = t.id
  GROUP BY COALESCE(t.industry, 'Unknown')
) t1 ON t1.industry = t0.industry
LEFT JOIN (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT u.id) AS active_customers
  FROM tenants t
  JOIN users u ON u.tenant_id = t.id AND u.is_staff = false
  JOIN orders o ON o.user_id = u.id AND o.tenant_id = t.id
  WHERE o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
  GROUP BY COALESCE(t.industry, 'Unknown')
) t2 ON t2.industry = t0.industry
LEFT JOIN (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT u.id) AS loyalty_members
  FROM tenants t
  JOIN users u ON u.tenant_id = t.id AND u.is_staff = false
  JOIN orders o ON o.user_id = u.id AND o.tenant_id = t.id
  JOIN loyalty_accounts la ON la.user_id = u.id AND la.tenant_id = t.id
  WHERE o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
  GROUP BY COALESCE(t.industry, 'Unknown')
) t3 ON t3.industry = t0.industry
LEFT JOIN (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT u.id) AS referred_customers
  FROM tenants t
  JOIN users u ON u.tenant_id = t.id AND u.is_staff = false
  JOIN orders o ON o.user_id = u.id AND o.tenant_id = t.id
  JOIN referral_redemptions rr ON rr.referred_user_id = u.id AND rr.tenant_id = t.id
  WHERE o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
  GROUP BY COALESCE(t.industry, 'Unknown')
) t4 ON t4.industry = t0.industry
ORDER BY COALESCE(t2.active_customers, 0) DESC, t0.industry ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: industry, tenant_count, tenants_with_any_loyalty_program, active_customers, loyalty_members, pct_customers_enrolled, referred_customers, pct_customers_referred

BeaQuery compiles with the expected 8 columns at the tenant-industry grain. Submitting now.

Bea calls submit

SELECT
  t0.industry AS industry,
  t0.tenant_count AS tenant_count,
  COALESCE(t1.tenants_with_any_loyalty_program, 0) AS tenants_with_any_loyalty_program,
  COALESCE(t2.active_customers, 0) AS active_customers,
  COALESCE(t3.loyalty_members, 0) AS loyalty_members,
  CASE WHEN COALESCE(t2.active_customers, 0) = 0 THEN 0
       ELSE ROUND(100.0 * COALESCE(t3.loyalty_members, 0) / t2.active_customers, 2)
  END AS pct_customers_enrolled,
  COALESCE(t4.referred_customers, 0) AS referred_customers,
  CASE WHEN COALESCE(t2.active_customers, 0) = 0 THEN 0
       ELSE ROUND(100.0 * COALESCE(t4.referred_customers, 0) / t2.active_customers, 2)
  END AS pct_customers_referred
FROM (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT t.id) AS tenant_count
  FROM tenants t
  GROUP BY COALESCE(t.industry, 'Unknown')
) t0
LEFT JOIN (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT t.id) AS tenants_with_any_loyalty_program
  FROM tenants t
  JOIN loyalty_programs lp ON lp.tenant_id = t.id
  GROUP BY COALESCE(t.industry, 'Unknown')
) t1 ON t1.industry = t0.industry
LEFT JOIN (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT u.id) AS active_customers
  FROM tenants t
  JOIN users u ON u.tenant_id = t.id AND u.is_staff = false
  JOIN orders o ON o.user_id = u.id AND o.tenant_id = t.id
  WHERE o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
  GROUP BY COALESCE(t.industry, 'Unknown')
) t2 ON t2.industry = t0.industry
LEFT JOIN (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT u.id) AS loyalty_members
  FROM tenants t
  JOIN users u ON u.tenant_id = t.id AND u.is_staff = false
  JOIN orders o ON o.user_id = u.id AND o.tenant_id = t.id
  JOIN loyalty_accounts la ON la.user_id = u.id AND la.tenant_id = t.id
  WHERE o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
  GROUP BY COALESCE(t.industry, 'Unknown')
) t3 ON t3.industry = t0.industry
LEFT JOIN (
  SELECT COALESCE(t.industry, 'Unknown') AS industry, COUNT(DISTINCT u.id) AS referred_customers
  FROM tenants t
  JOIN users u ON u.tenant_id = t.id AND u.is_staff = false
  JOIN orders o ON o.user_id = u.id AND o.tenant_id = t.id
  JOIN referral_redemptions rr ON rr.referred_user_id = u.id AND rr.tenant_id = t.id
  WHERE o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
  GROUP BY COALESCE(t.industry, 'Unknown')
) t4 ON t4.industry = t0.industry
ORDER BY COALESCE(t2.active_customers, 0) DESC, t0.industry ASC

The warehouse answers

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

BeaSubmitted. The report groups tenants by `industry` (free-text tenants without a value fall into an "Unknown" bucket so no tenant is silently dropped), and computes tenant counts, loyalty-program presence, active customers, enrollment, and referral reach per vertical, ordered by active customers descending — which should directly show whether B2B wholesale verticals are excluded from loyalty/referral programs and whether adoption is concentrated elsewhere.

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 (271 KB) · Ingrid Solberg's transcript (906 KB) · Aisha Rahman's transcript (261 KB) · the night's plan (6 KB) · the night's reports (6 KB) · the week's memo (4 KB)

Loyalty and referral programme reach by merchant vertical

A business event lands on the Head of Lifecycle Marketing's desk

Aisha Rahman writes to Ingrid Solberg

Ingrid Solberg plans the night

Bea writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q169

Everything, as recorded