Queries · q059 · Head of Growth Marketing · week 1, night 4
“Among shoppers who place orders, do people who were referred in, or who refer others, show different order frequency or spend than everyone else?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
The manager, at the start of week 1
Subject: What I need to get oriented this week
Hi Sofie,
Thrilled to be here — here's where my head's at. I know you've only got a handful of report slots a night, so I've ordered these by what I need most. Please push back if any of these are too broad or should split into more than one report.
What I need this for: I'm meeting the leadership team Friday and want a grounded, numbers-based view of growth marketing's current state before I propose anything. No need for polish — I just want to know what's really happening so I don't propose things that are already working, or miss things that are quietly broken.
Thanks for taking this on — let me know if you want me to cut this list down.
Chloé
The analyst · week 1, night 4, request 2 of 3
Last night's industry breakdown overturned my earlier 'discounted orders are healthier' read: that was Simpson's-paradox-driven by B2B contract pricing (huge baskets, 40+ items/order) and near-free subscription trial offers in Food & Coffee/Pet Supplies (avg order value ~6-8 EUR). Within actual consumer retail verticals (apparel, beauty, electronics, home, sports), discounted orders have equal-or-lower AOV and meaningfully HIGHER return rates than full-price ones — the opposite of the pooled story. That's resolved and ready for the memo, no further query needed there. The loyalty report is the one open thread: members show lower order frequency and AOV than non-members, which is counterintuitive, and I suspect the same composition trap (loyalty concentrated in low-AOV subscription verticals) — worth one check before I tell Chloé 'loyalty doesn't move the needle.' Email engagement (29.9% open, 2.4% click, healthy bounce/unsubscribe) looked like a normal, non-alarming baseline with only one channel actually used, so I'm not spending a slot chasing it further. With this being the last night, I'm using the remaining two slots to close out Chloé's still-open questions: the referral half of Q4, and a first look at Q5 (A/B tests), both as compact baseline reports for the Friday memo.
The engineer · 1 dry run on the empty schema, then the submission
WITH classified AS ( SELECT o.id AS order_id, o.tenant_id, o.user_id, o.grand_total, o.currency_code, o.placed_at, CASE WHEN rr.referred_user_id IS NOT NULL THEN 'referred' WHEN rf.referrer_user_id IS NOT NULL THEN 'referrer' ELSE 'neither' END AS segment FROM orders o LEFT JOIN (SELECT DISTINCT tenant_id, referred_user_id FROM referral_redemptions) rr ON rr.tenant_id = o.tenant_id AND rr.referred_user_id = o.user_id LEFT JOIN (SELECT DISTINCT tenant_id, referrer_user_id FROM referrals) rf ON rf.tenant_id = o.tenant_id AND rf.referrer_user_id = o.user_id WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' ), converted AS ( SELECT c.*, CASE WHEN c.currency_code = 'EUR' THEN c.grand_total ELSE c.grand_total / er.rate END AS grand_total_eur FROM classified c LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = c.currency_code AND er.as_of = CAST(c.placed_at AS date) ) SELECT segment, COUNT(DISTINCT tenant_id || ':' || user_id) AS users_count, COUNT(*) AS orders_count_2025, ROUND(COUNT(*)::numeric / NULLIF(COUNT(DISTINCT tenant_id || ':' || user_id),0), 2) AS avg_orders_per_user, ROUND(AVG(grand_total_eur)::numeric, 0) AS avg_order_value_eur, ROUND(SUM(grand_total_eur)::numeric, 0) AS total_revenue_eur FROM converted GROUP BY segment ORDER BY CASE segment WHEN 'referred' THEN 1 WHEN 'referrer' THEN 2 ELSE 3 END
“The query compiles and returns exactly the requested columns and grain. Submitting.”
“Submitted. Summary of the approach:
- Classification: each 2025 ordering user (tenant_id + user_id, since user_id is only unique per tenant) is tagged referred if they appear in referral_redemptions.referred_user_id, else referrer if in referrals.referrer_user_id, else neither — checked in that priority order.
- Scope: only orders with placed_at in 2025, across all tenants.
- Currency: each order's grand_total is converted to EUR using that day's exchange_rates rate (EUR→currency_code), matching the convention used in prior money reports; orders already in EUR pass through unchanged.
- Output: one row per segment (referred, referrer, neither) with user counts, order counts, average orders/user, average order value in EUR, and total revenue in EUR — ordered exactly as requested.”
As Sofie Nilsen saw it, from the small dataset the agents worked on
segment | users_count | orders_count_2025 | avg_orders_per_user | avg_order_value_eur | total_revenue_eur ---------+-------------+-------------------+---------------------+---------------------+------------------ referred | 53 | 202 | 3.81 | 272 | 55004 referrer | 418 | 3352 | 8.02 | 286 | 957230 neither | 5688 | 18639 | 3.28 | 270 | 5032914 (3 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
Changed only where the benchmark needs the same result on every system
WITH classified AS ( SELECT o.id AS order_id, o.tenant_id, o.user_id, o.grand_total, o.currency_code, o.placed_at, SELECT o.id AS order_id, o.tenant_id AS tenant_id, o.user_id AS user_id, o.grand_total AS grand_total, o.currency_code AS currency_code, o.placed_at AS placed_at, CASE WHEN rr.referred_user_id IS NOT NULL THEN 'referred' WHEN rf.referrer_user_id IS NOT NULL THEN 'referrer' ELSE 'neither' END AS segment FROM orders o LEFT JOIN (SELECT DISTINCT tenant_id, referred_user_id FROM referral_redemptions) rr ON rr.tenant_id = o.tenant_id AND rr.referred_user_id = o.user_id LEFT JOIN (SELECT DISTINCT tenant_id, referrer_user_id FROM referrals) rf ON rf.tenant_id = o.tenant_id AND rf.referrer_user_id = o.user_id WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'),converted AS ( SELECT c.*, CASE WHEN c.currency_code = 'EUR' THEN c.grand_total ELSE c.grand_total / er.rate END AS grand_total_eur ELSE CAST(c.grand_total / er.rate AS NUMERIC(18,6)) END AS grand_total_eur FROM classified c LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = c.currency_code AND er.as_of = CAST(c.placed_at AS date))SELECT segment, COUNT(DISTINCT tenant_id || ':' || user_id) AS users_count, COUNT(*) AS orders_count_2025, ROUND(COUNT(*)::numeric / NULLIF(COUNT(DISTINCT tenant_id || ':' || user_id),0), 2) AS avg_orders_per_user, ROUND(AVG(grand_total_eur)::numeric, 0) AS avg_order_value_eur, ROUND(SUM(grand_total_eur)::numeric, 0) AS total_revenue_eur CAST(COUNT(*) AS numeric(18,6)) / NULLIF(COUNT(DISTINCT tenant_id || ':' || user_id),0) AS avg_orders_per_user, AVG(grand_total_eur) AS avg_order_value_eur, SUM(grand_total_eur) AS total_revenue_eurFROM convertedGROUP BY segmentORDER BY CASE segment WHEN 'referred' THEN 1 WHEN 'referrer' THEN 2 ELSE 3 ENDORDER BY CASE segment WHEN 'referred' THEN 1 WHEN 'referrer' THEN 2 ELSE 3 END NULLS LAST
WITH classified AS ( SELECT o.id AS order_id, o.tenant_id AS tenant_id, o.user_id AS user_id, o.grand_total AS grand_total, o.currency_code AS currency_code, o.placed_at AS placed_at, CASE WHEN rr.referred_user_id IS NOT NULL THEN 'referred' WHEN rf.referrer_user_id IS NOT NULL THEN 'referrer' ELSE 'neither' END AS segment FROM orders o LEFT JOIN (SELECT DISTINCT tenant_id, referred_user_id FROM referral_redemptions) rr ON rr.tenant_id = o.tenant_id AND rr.referred_user_id = o.user_id LEFT JOIN (SELECT DISTINCT tenant_id, referrer_user_id FROM referrals) rf ON rf.tenant_id = o.tenant_id AND rf.referrer_user_id = o.user_id WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' ), converted AS ( SELECT c.*, CASE WHEN c.currency_code = 'EUR' THEN c.grand_total ELSE CAST(c.grand_total / er.rate AS NUMERIC(18,6)) END AS grand_total_eur FROM classified c LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = c.currency_code AND er.as_of = CAST(c.placed_at AS date) ) SELECT segment, COUNT(DISTINCT tenant_id || ':' || user_id) AS users_count, COUNT(*) AS orders_count_2025, CAST(COUNT(*) AS numeric(18,6)) / NULLIF(COUNT(DISTINCT tenant_id || ':' || user_id),0) AS avg_orders_per_user, AVG(grand_total_eur) AS avg_order_value_eur, SUM(grand_total_eur) AS total_revenue_eur FROM converted GROUP BY segment ORDER BY CASE segment WHEN 'referred' THEN 1 WHEN 'referrer' THEN 2 ELSE 3 END NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Sofie Nilsen (analyst for the Head of Growth Marketing): Title: Do referred or referring shoppers spend differently than everyone else? Question: Among shoppers who place orders, do people who were referred in, or who refer others, show different order frequency or spend than everyone else? Grain: one row per shopper segment: 'referred', 'referrer', or 'neither' Columns: - segment - 'referred' (user_id appears in referral_redemptions.referred_user_id), else 'referrer' (user_id appears in referrals.referrer_user_id), else 'neither' - users_count - count of distinct users in that segment who placed at least one order with placed_at in 2025 - orders_count_2025 - count of those users' orders placed_at in 2025 - avg_orders_per_user - orders_count_2025 / users_count, rounded to 2 decimals - avg_order_value_eur - average grand_total (EUR) across that segment's 2025 orders, rounded to 0 decimals - total_revenue_eur - sum of grand_total (EUR) for that segment's 2025 orders, rounded to 0 decimals Details: Source: orders (placed_at in 2025, all tenants), classify each ordering user by checking membership in referral_redemptions.referred_user_id first, then referrals.referrer_user_id, else 'neither' (mutually exclusive, in that priority order, to handle users who are both). Convert grand_total to EUR the same way as prior money reports. Exactly 3 output rows, ordered 'referred', 'referrer', 'neither'. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH classified AS ( SELECT o.id AS order_id, o.tenant_id, o.user_id, o.grand_total, o.currency_code, o.placed_at, CASE WHEN rr.referred_user_id IS NOT NULL THEN 'referred' WHEN rf.referrer_user_id IS NOT NULL THEN 'referrer' ELSE 'neither' END AS segment FROM orders o LEFT JOIN (SELECT DISTINCT tenant_id, referred_user_id FROM referral_redemptions) rr ON rr.tenant_id = o.tenant_id AND rr.referred_user_id = o.user_id LEFT JOIN (SELECT DISTINCT tenant_id, referrer_user_id FROM referrals) rf ON rf.tenant_id = o.tenant_id AND rf.referrer_user_id = o.user_id WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' ), converted AS ( SELECT c.*, CASE WHEN c.currency_code = 'EUR' THEN c.grand_total ELSE c.grand_total / er.rate END AS grand_total_eur FROM classified c LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = c.currency_code AND er.as_of = CAST(c.placed_at AS date) ) SELECT segment, COUNT(DISTINCT tenant_id || ':' || user_id) AS users_count, COUNT(*) AS orders_count_2025, ROUND(COUNT(*)::numeric / NULLIF(COUNT(DISTINCT tenant_id || ':' || user_id),0), 2) AS avg_orders_per_user, ROUND(AVG(grand_total_eur)::numeric, 0) AS avg_order_value_eur, ROUND(SUM(grand_total_eur)::numeric, 0) AS total_revenue_eur FROM converted GROUP BY segment ORDER BY CASE segment WHEN 'referred' THEN 1 WHEN 'referrer' THEN 2 ELSE 3 END
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: segment, users_count, orders_count_2025, avg_orders_per_user, avg_order_value_eur, total_revenue_eur
submitWITH classified AS ( SELECT o.id AS order_id, o.tenant_id, o.user_id, o.grand_total, o.currency_code, o.placed_at, CASE WHEN rr.referred_user_id IS NOT NULL THEN 'referred' WHEN rf.referrer_user_id IS NOT NULL THEN 'referrer' ELSE 'neither' END AS segment FROM orders o LEFT JOIN (SELECT DISTINCT tenant_id, referred_user_id FROM referral_redemptions) rr ON rr.tenant_id = o.tenant_id AND rr.referred_user_id = o.user_id LEFT JOIN (SELECT DISTINCT tenant_id, referrer_user_id FROM referrals) rf ON rf.tenant_id = o.tenant_id AND rf.referrer_user_id = o.user_id WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' ), converted AS ( SELECT c.*, CASE WHEN c.currency_code = 'EUR' THEN c.grand_total ELSE c.grand_total / er.rate END AS grand_total_eur FROM classified c LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = c.currency_code AND er.as_of = CAST(c.placed_at AS date) ) SELECT segment, COUNT(DISTINCT tenant_id || ':' || user_id) AS users_count, COUNT(*) AS orders_count_2025, ROUND(COUNT(*)::numeric / NULLIF(COUNT(DISTINCT tenant_id || ':' || user_id),0), 2) AS avg_orders_per_user, ROUND(AVG(grand_total_eur)::numeric, 0) AS avg_order_value_eur, ROUND(SUM(grand_total_eur)::numeric, 0) AS total_revenue_eur FROM converted GROUP BY segment ORDER BY CASE segment WHEN 'referred' THEN 1 WHEN 'referrer' THEN 2 ELSE 3 END
Submitted to tonight's batch. Your work on this request is done.