DB Olympics

Queries · q264 · Head of Growth Marketing · week 2, night 3

Is the attribution blind spot growing, shrinking, or stable over 2025?

“Across the months of 2025, what share of orders and revenue can we trace to a marketing touchpoint, and is that coverage changing over time?”

Manager · asked the questionChloé MarchettiHead of Growth Marketing. Joined from a direct-to-consumer brand. Wants to sell merchants more of Tidewick's marketing tools.
Analyst · planned the reportSofie NilsenStoryteller: looks for the two or three findings that matter and digs until each is explained.
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. Chloé Marchetti writes to Sofie Nilsen

    The manager, at the start of week 2

    Subject: Good progress — four things I want nailed down this week

    Hi Sofie,

    Thanks for this — genuinely useful, and the vertical breakdown on discounting is exactly the kind of "don't trust the pooled number" catch I was worried about. A few things surprised me and I want to go deeper before I take anything to leadership.

    What surprised me / needs explaining:

    • Aisha's team flagged that loyalty members "cost" nearly a fifth of their own revenue in redeemed points, plus a large pile of points issued but not yet redeemed sitting as a liability. That's a very different lens from AOV — a program can look flat on AOV and still be expensive. I want these two views reconciled, not treated as separate facts.
    • Aisha also walked back the "loyalty members return more" claim once category mix was accounted for. That's a good catch, but it makes me nervous about the apparel-vs-electronics AOV comparison in your memo — is that difference also just a category mix artifact, or is it real?
    • Referrals look like our best-performing lever by far, yet reach is under 1% of customers. That gap between "strong where it exists" and "barely exists" feels like the single biggest opportunity I've seen all week.

    My questions for this week, roughly in priority order:

    1. Does discounting actually cost us margin, and in which verticals is it worst? This was already your next step — please keep it top of the list. I need a real cost picture (not just return rates) before I tell merchants to rein in sitewide sales.
    2. Is the loyalty programme actually worth running as-is? I want one clear picture that weighs what members cost us (redeemed and owed points) against what they're worth (spend, frequency, retention), controlling for the category mix issue Aisha found. I need to decide whether we fix it, shrink it, or let it be.
    3. What would it take to grow referrals, and who's a good referrer? Given how strong but tiny this program is, I want to understand what referring customers look like (so we can find more of them) and whether growth here looks more promising than fixing loyalty.
    4. Of the orders we can't currently link to a channel, is there any pattern at all (timing, merchant type, repeat vs new customer) that hints at what's driving them? I don't expect a clean answer — I just want to know if this blind spot is worth investing in closing, or if it's simply untrackable and I should stop asking.

    What I need this for: I'm trying to turn this into a short list of 2-3 concrete recommendations for merchants and for our own roadmap — where to tell them to pull back (discounting), where to double down (referrals), and where we might be quietly burning money (loyalty payouts). I'd rather have fewer, firmer answers than a wider, shakier set.

    Thanks again — this is coming together well.

    Chloé

  2. Sofie Nilsen plans the night

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

    Loyalty picture is now reconcilable: Apparel carries the lion's share of both the redemption cost (€40k, 23.7% of member revenue) and the outstanding liability (€192k of ~€354k total), yet its repeat-purchase rate is flat between members and non-members (58.5% vs 59.6%) — expensive with no retention payoff, the clearest 'fix or shrink' case. Electronics and Home & Living cost far less but show a real repeat-rate lift for members (+27pp, +13pp) — worth leaving alone. (Caveat: member_revenue_eur reads as exactly €0 for Books, Food & Coffee, Pet Supplies and Jewelry despite those segments having real members and orders in the companion report — looks like a currency-conversion gap for a few small tenants; doesn't change the Apparel/Sports-driven headline since those two verticals hold ~85% of the cost, but I'm flagging it as unverified rather than re-querying tonight.) The referral report found something clean and robust: referrers order 1.5-2.4x more often than non-referrers in every single vertical, even where their average order value is actually lower — frequency, not basket size, is the referrer signature. Tonight I want to find the lifecycle trigger point (how many orders in before someone typically refers) to make the 'how do we get more of them' question concrete, then move to Chloé's lowest-priority but still-requested Q4 (the unattributed-orders blind spot) since this is likely my last real chance before Friday.

    Is the attribution blind spot growing, shrinking, or stable over 2025?
    Question
    Across the months of 2025, what share of orders and revenue can we trace to a marketing touchpoint, and is that coverage changing over time?
    One row per
    calendar month (2025)
    Columns
    • month - YYYY-MM label from orders.placed_at
    • orders_count - count of orders placed_at in that month, all tenants
    • attributed_orders_count - count of those orders whose id appears in attributions.order_id
    • attribution_coverage_pct - attributed_orders_count / orders_count * 100, rounded to 1 decimal
    • total_revenue_eur - sum of grand_total (EUR) for that month's orders, rounded to 0 decimals
    • attributed_revenue_eur - sum of grand_total (EUR) for just the attributed orders in that month, rounded to 0 decimals

  3. Omar writes the SQL

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

    Dry run 1 · STATUS: REJECTED
    WITH order_amounts AS (
  SELECT o.id, o.placed_at, o.currency_code, o.grand_total,
    CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
         ELSE o.grand_total / er.rate END AS grand_total_eur
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS date)
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
),
attributed AS (
  SELECT DISTINCT order_id FROM attributions WHERE order_id IS NOT NULL
)
SELECT
  TO_CHAR(oa.placed_at, 'YYYY-MM') AS month,
  COUNT(*) AS orders_count,
  COUNT(*) FILTER (WHERE a.order_id IS NOT NULL) AS attributed_orders_count,
  ROUND(100.0 * COUNT(*) FILTER (WHERE a.order_id IS NOT NULL) / COUNT(*), 1) AS attribution_coverage_pct,
  ROUND(SUM(oa.grand_total_eur), 0) AS total_revenue_eur,
  ROUND(SUM(oa.grand_total_eur) FILTER (WHERE a.order_id IS NOT NULL), 0) AS attributed_revenue_eur
FROM order_amounts oa
LEFT JOIN attributed a ON a.order_id = oa.id
GROUP BY 1
ORDER BY 1
    Dry run 2 · 1 change from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)
    WITH order_amounts AS (  SELECT o.id, o.placed_at, o.currency_code, o.grand_total,    CASE WHEN o.currency_code = 'EUR' THEN o.grand_total         ELSE o.grand_total / er.rate END AS grand_total_eur  FROM orders o  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = o.currency_code   AND er.as_of = CAST(o.placed_at AS date)  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'),attributed AS (  SELECT DISTINCT order_id FROM attributions WHERE order_id IS NOT NULL)SELECT  TO_CHAR(oa.placed_at, 'YYYY-MM') AS month,  strftime(oa.placed_at, '%Y-%m') AS month,  COUNT(*) AS orders_count,  COUNT(*) FILTER (WHERE a.order_id IS NOT NULL) AS attributed_orders_count,  ROUND(100.0 * COUNT(*) FILTER (WHERE a.order_id IS NOT NULL) / COUNT(*), 1) AS attribution_coverage_pct,  ROUND(SUM(oa.grand_total_eur), 0) AS total_revenue_eur,  ROUND(SUM(oa.grand_total_eur) FILTER (WHERE a.order_id IS NOT NULL), 0) AS attributed_revenue_eurFROM order_amounts oaLEFT JOIN attributed a ON a.order_id = oa.idGROUP BY 1ORDER BY 1

    “This compiles correctly with the expected columns.”

    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 2025 month, order counts/revenue (converted to EUR via exchange_rates keyed on the order's placed date) alongside the subset of orders found in attributions, giving coverage % and attributed revenue to track the attribution blind spot over the year.”

  4. The report comes back overnight

    As Sofie Nilsen saw it, from the small dataset the agents worked on

    month   | orders_count | attributed_orders_count | attribution_coverage_pct | total_revenue_eur | attributed_revenue_eur
--------+--------------+-------------------------+--------------------------+-------------------+-----------------------
2025-01 |         1827 |                    1418 |                     77.6 |          590933.0 |               581931.0
2025-02 |         1400 |                    1012 |                     72.3 |          434674.0 |               431919.0
2025-03 |         1638 |                    1197 |                     73.1 |          422216.0 |               418009.0
2025-04 |         1568 |                    1150 |                     73.3 |          479142.0 |               472688.0
2025-05 |         1691 |                    1287 |                     76.1 |          489232.0 |               471861.0
2025-06 |         1509 |                    1116 |                     74.0 |          352794.0 |               348894.0
2025-07 |         1648 |                    1232 |                     74.8 |          373491.0 |               367645.0
2025-08 |         1564 |                    1133 |                     72.4 |          364953.0 |               358672.0
2025-09 |         1712 |                    1275 |                     74.5 |          472423.0 |               468375.0
2025-10 |         1879 |                    1442 |                     76.7 |          521689.0 |               499927.0
2025-11 |         2645 |                    2171 |                     82.1 |          837654.0 |               824058.0
2025-12 |         3112 |                    2594 |                     83.4 |          705947.0 |               693468.0
(12 rows)

  5. 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:

  6. It becomes benchmark query q264

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

    • placeholderyear month
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
    • made explicita selected t.col gets AS col, its standard name made explicit (4×)
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results
    The benchmark text, against the submitted SQL
    WITH order_amounts AS (  SELECT o.id, o.placed_at, o.currency_code, o.grand_total,  SELECT o.id AS id, o.placed_at AS placed_at, o.currency_code AS currency_code, o.grand_total AS grand_total,    CASE WHEN o.currency_code = 'EUR' THEN o.grand_total         ELSE o.grand_total / er.rate END AS grand_total_eur         ELSE CAST(o.grand_total / er.rate AS NUMERIC(18,6)) END AS grand_total_eur  FROM orders o  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = o.currency_code   AND er.as_of = CAST(o.placed_at AS date)  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'),attributed AS (  SELECT DISTINCT order_id FROM attributions WHERE order_id IS NOT NULL)SELECT  strftime(oa.placed_at, '%Y-%m') AS month,  {{year_month(oa.placed_at)}} AS month,  COUNT(*) AS orders_count,  COUNT(*) FILTER (WHERE a.order_id IS NOT NULL) AS attributed_orders_count,  ROUND(100.0 * COUNT(*) FILTER (WHERE a.order_id IS NOT NULL) / COUNT(*), 1) AS attribution_coverage_pct,  ROUND(SUM(oa.grand_total_eur), 0) AS total_revenue_eur,  ROUND(SUM(oa.grand_total_eur) FILTER (WHERE a.order_id IS NOT NULL), 0) AS attributed_revenue_eur  100.0 * COUNT(*) FILTER (WHERE a.order_id IS NOT NULL) / COUNT(*) AS attribution_coverage_pct,  SUM(oa.grand_total_eur) AS total_revenue_eur,  SUM(oa.grand_total_eur) FILTER (WHERE a.order_id IS NOT NULL) AS attributed_revenue_eurFROM order_amounts oaLEFT JOIN attributed a ON a.order_id = oa.idGROUP BY 1ORDER BY 1ORDER BY 1 NULLS LAST

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

    WITH order_amounts AS (
  SELECT o.id AS id, o.placed_at AS placed_at, o.currency_code AS currency_code, o.grand_total AS grand_total,
    CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
         ELSE CAST(o.grand_total / er.rate AS NUMERIC(18,6)) END AS grand_total_eur
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS date)
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
),
attributed AS (
  SELECT DISTINCT order_id FROM attributions WHERE order_id IS NOT NULL
)
SELECT
  strftime(oa.placed_at, '%Y-%m') AS month,
  COUNT(*) AS orders_count,
  COUNT(*) FILTER (WHERE a.order_id IS NOT NULL) AS attributed_orders_count,
  100.0 * COUNT(*) FILTER (WHERE a.order_id IS NOT NULL) / COUNT(*) AS attribution_coverage_pct,
  SUM(oa.grand_total_eur) AS total_revenue_eur,
  SUM(oa.grand_total_eur) FILTER (WHERE a.order_id IS NOT NULL) AS attributed_revenue_eur
FROM order_amounts oa
LEFT JOIN attributed a ON a.order_id = oa.id
GROUP BY 1
ORDER BY 1 NULLS LAST
    100 ms1 sHyper395 msDuckDB844 msGizmoSQL736 msclickhouse_brew453 msFirebolterrorDataFusionwrong resultClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 4, Column 34: 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": "91e54ade-1f69-4a70-ab01-5ecae6349fe3", "query_labe
    • DataFusionrow count 12 vs 1
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table2.grand_total Decimal(14, 4) Decimal64(size = 0), __table3.rate Nullable(Decimal(12, 6)) Nullable(size = 0, Decimal64(size = 0), UInt8(size = 0)). (ARGUMENT_OUT_OF_BOUND)

    Scale factor 1000, c8a.24xlarge, after the last load. q264 on the start page, with every configuration.

  7. 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, 9 turns
    • The request, as Omar received it
      Report request from Sofie Nilsen (analyst for the Head of Growth Marketing):

Title: Is the attribution blind spot growing, shrinking, or stable over 2025?
Question: Across the months of 2025, what share of orders and revenue can we trace to a marketing touchpoint, and is that coverage changing over time?
Grain: one row per calendar month (2025)
Columns:
- month - YYYY-MM label from orders.placed_at
- orders_count - count of orders placed_at in that month, all tenants
- attributed_orders_count - count of those orders whose id appears in attributions.order_id
- attribution_coverage_pct - attributed_orders_count / orders_count * 100, rounded to 1 decimal
- total_revenue_eur - sum of grand_total (EUR) for that month's orders, rounded to 0 decimals
- attributed_revenue_eur - sum of grand_total (EUR) for just the attributed orders in that month, rounded to 0 decimals
Details: Source: orders left joined to attributions (distinct match on order_id) for 2025, all tenants. Convert grand_total to EUR using exchange_rates as of placed_at date (EUR=rate 1). Cover all 12 months of 2025. Order by month ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Omar calls dry_run
      WITH order_amounts AS (
  SELECT o.id, o.placed_at, o.currency_code, o.grand_total,
    CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
         ELSE o.grand_total / er.rate END AS grand_total_eur
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS date)
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
),
attributed AS (
  SELECT DISTINCT order_id FROM attributions WHERE order_id IS NOT NULL
)
SELECT
  TO_CHAR(oa.placed_at, 'YYYY-MM') AS month,
  COUNT(*) AS orders_count,
  COUNT(*) FILTER (WHERE a.order_id IS NOT NULL) AS attributed_orders_count,
  ROUND(100.0 * COUNT(*) FILTER (WHERE a.order_id IS NOT NULL) / COUNT(*), 1) AS attribution_coverage_pct,
  ROUND(SUM(oa.grand_total_eur), 0) AS total_revenue_eur,
  ROUND(SUM(oa.grand_total_eur) FILTER (WHERE a.order_id IS NOT NULL), 0) AS attributed_revenue_eur
FROM order_amounts oa
LEFT JOIN attributed a ON a.order_id = oa.id
GROUP BY 1
ORDER BY 1
    • The warehouse answers
      STATUS: REJECTED
  - does not compile: Catalog Error: Scalar Function with name to_char does not exist!
    • Omar calls dry_run
      WITH order_amounts AS (
  SELECT o.id, o.placed_at, o.currency_code, o.grand_total,
    CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
         ELSE o.grand_total / er.rate END AS grand_total_eur
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS date)
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
),
attributed AS (
  SELECT DISTINCT order_id FROM attributions WHERE order_id IS NOT NULL
)
SELECT
  strftime(oa.placed_at, '%Y-%m') AS month,
  COUNT(*) AS orders_count,
  COUNT(*) FILTER (WHERE a.order_id IS NOT NULL) AS attributed_orders_count,
  ROUND(100.0 * COUNT(*) FILTER (WHERE a.order_id IS NOT NULL) / COUNT(*), 1) AS attribution_coverage_pct,
  ROUND(SUM(oa.grand_total_eur), 0) AS total_revenue_eur,
  ROUND(SUM(oa.grand_total_eur) FILTER (WHERE a.order_id IS NOT NULL), 0) AS attributed_revenue_eur
FROM order_amounts oa
LEFT JOIN attributed a ON a.order_id = oa.id
GROUP BY 1
ORDER BY 1
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, orders_count, attributed_orders_count, attribution_coverage_pct, total_revenue_eur, attributed_revenue_eur
    • OmarThis compiles correctly with the expected columns.
    • Omar calls submit
      WITH order_amounts AS (
  SELECT o.id, o.placed_at, o.currency_code, o.grand_total,
    CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
         ELSE o.grand_total / er.rate END AS grand_total_eur
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS date)
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
),
attributed AS (
  SELECT DISTINCT order_id FROM attributions WHERE order_id IS NOT NULL
)
SELECT
  strftime(oa.placed_at, '%Y-%m') AS month,
  COUNT(*) AS orders_count,
  COUNT(*) FILTER (WHERE a.order_id IS NOT NULL) AS attributed_orders_count,
  ROUND(100.0 * COUNT(*) FILTER (WHERE a.order_id IS NOT NULL) / COUNT(*), 1) AS attribution_coverage_pct,
  ROUND(SUM(oa.grand_total_eur), 0) AS total_revenue_eur,
  ROUND(SUM(oa.grand_total_eur) FILTER (WHERE a.order_id IS NOT NULL), 0) AS attributed_revenue_eur
FROM order_amounts oa
LEFT JOIN attributed a ON a.order_id = oa.id
GROUP BY 1
ORDER BY 1
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • OmarSubmitted. The query computes, per 2025 month, order counts/revenue (converted to EUR via `exchange_rates` keyed on the order's placed date) alongside the subset of orders found in `attributions`, giving coverage % and attributed revenue to track the attribution blind spot over the year.

    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 (269 KB) · Sofie Nilsen's transcript (793 KB) · Chloé Marchetti's transcript (263 KB) · the night's plan (5 KB) · the night's reports (7 KB) · the week's memo (3 KB)