Queries · q116 · Data Protection Officer · week 1, night 2

Marketing-email consent opt-in trend, full history

“Has the marketing-email opt-in rate meaningfully changed over the full 3-year history, and did the early decline continue or stabilize?”

Manager · asked the questionInes LaurentData Protection Officer. Lawyer by training, previously at a European marketplace. Also reports to the board's audit committee.

Analyst · planned the reportPriyanka DesaiAuditor's mindset: traces individual records end to end to check that the totals are real.

Engineer · wrote the SQLViktorOld-school warehouse engineer: explicit JOIN chains straight to the base tables, derived tables, GROUP BY with CASE pivots, few CTEs.

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

Ines Laurent writes to Priyanka Desai
The manager, at the start of week 1
Subject: A few starting questions on privacy & data handling

Hi Priyanka,

Welcome aboard, and thank you for taking these on this week. As I get oriented, here's what I'd like to understand first. Given the slot constraints, please treat these as priorities in order — if only three fit this week, start from the top and we'll pick up the rest next week.
1. How are we doing on privacy requests from shoppers? I want to know how many access/erasure/portability requests we're getting, how long they typically take to close, and whether any are overdue. This is the thing the board audit committee will ask about first, and it's also a legal deadline risk, so I need a realistic picture, not just a snapshot.
2. What does our consent picture look like? I'd like a sense of how many of our users have actually opted in versus withdrawn or never given consent, and whether that's changed meaningfully over time. This tells me how exposed we are if we rely on consent for marketing or data use.
3. Are there open compliance issues sitting unresolved, and how serious are they? I want to understand the volume and severity of flagged incidents (things like suspicious data access or retention problems) and how quickly they tend to get closed out. I need to know if we have a backlog building up anywhere.
4. How long are we actually holding onto shopper data, and does it match what our retention rules say it should be? I'm less interested in the rulebook and more in whether reality matches it — are there data types we're sitting on well past when we said we would delete them?
5. Are there any legal holds in place right now, and what do they cover? I want to know how many are active, what they're blocking, and whether any look stale (i.e., should have been released a while ago). This matters for both litigation readiness and for explaining why certain deletions haven't happened.
For now, could you scope the first request for me? I'd like to see it broken down in a way that shows me trend over the last several months, not just a single total — I want to catch whether we're improving or falling behind.

Let me know if any of these need to be split further once you see what the data looks like.

Thanks, Ines
Priyanka Desai plans the night
The analyst · week 1, night 2, request 2 of 3
Report 1 showed overall low DSAR volume but exposed a real red flag for Ines: 3 requests have sat open since 2023-05, 2024-07, and 2025-03 (now 0.5–2.5 years old) with no completion — these are the "legal deadline risk" she asked about, and the aggregate monthly view hides who/what they are. Also portability requests were 0 in every single month across 3 years, worth confirming in the drill-down rather than assuming it's a data artifact. Report 2 was truncated at 50 rows mid-way through marketing_email's monthly series (2024-02), and marketing_email showed a real declining opt-in trend (99.4%→94.4%) that I need to see through to 2025-12; analytics_cookies showed a suspiciously perfect 100% opt-in with zero withdrawals ever recorded across 36 months, which I'm deferring rather than burning a slot on tonight, but will flag/verify later if time allows. Report 3 came back clean — only 6 compliance_events total, all resolved, no backlog, no unresolved aging — so I consider Ines's question 3 answered for now and am moving a slot to question 4 (retention vs. policy), starting with a cheap inventory of what retention_policies actually exist before committing to the harder actual-vs-policy comparison next.
Marketing-email consent opt-in trend, full history
Question
Has the marketing-email opt-in rate meaningfully changed over the full 3-year history, and did the early decline continue or stabilize?
One row per
calendar month, purpose fixed to 'marketing_email'
Columns
- month (YYYY-MM)
- grants recorded in month
- withdrawals recorded in month
- expirations recorded in month
- users with any consent record as of month end
- net active opt-ins at month end
- active opt-in rate at month end (%, 1 decimal)

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

WITH months AS (
  SELECT month_label, MIN(calendar_date) AS month_start, MAX(calendar_date) AS month_end
  FROM calendar_days
  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
  GROUP BY month_label
),
flow AS (
  SELECT cd.month_label,
         SUM(CASE WHEN cr.status = 'granted' THEN 1 ELSE 0 END) AS grants,
         SUM(CASE WHEN cr.status = 'withdrawn' THEN 1 ELSE 0 END) AS withdrawals,
         SUM(CASE WHEN cr.status = 'expired' THEN 1 ELSE 0 END) AS expirations
  FROM calendar_days cd
  JOIN consent_records cr
    ON cr.occurred_at::date = cd.calendar_date
   AND cr.purpose = 'marketing_email'
  WHERE cd.calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
  GROUP BY cd.month_label
),
snapshot_base AS (
  SELECT m.month_label,
         cr.user_id,
         cr.status,
         ROW_NUMBER() OVER (PARTITION BY m.month_label, cr.user_id ORDER BY cr.occurred_at DESC, cr.id DESC) AS rn
  FROM months m
  JOIN consent_records cr
    ON cr.purpose = 'marketing_email'
   AND cr.occurred_at < (m.month_end + INTERVAL '1 day')
),
monthly_active AS (
  SELECT month_label,
         COUNT(*) AS users_with_consent,
         SUM(CASE WHEN status = 'granted' THEN 1 ELSE 0 END) AS net_active
  FROM snapshot_base
  WHERE rn = 1
  GROUP BY month_label
)
SELECT
  m.month_label AS month,
  COALESCE(f.grants, 0) AS grants_recorded,
  COALESCE(f.withdrawals, 0) AS withdrawals_recorded,
  COALESCE(f.expirations, 0) AS expirations_recorded,
  COALESCE(ma.users_with_consent, 0) AS users_with_any_consent,
  COALESCE(ma.net_active, 0) AS net_active_opt_ins,
  ROUND(100.0 * COALESCE(ma.net_active, 0) / NULLIF(ma.users_with_consent, 0), 1) AS active_opt_in_rate_pct
FROM months m
LEFT JOIN flow f ON f.month_label = m.month_label
LEFT JOIN monthly_active ma ON ma.month_label = m.month_label
ORDER BY m.month_label ASC

“Query compiles and returns the expected columns. Submitting.”

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

“Submitted. The report computes, per calendar month from 2023-01 through 2025-12 (36 rows, platform-wide, purpose = marketing_email): - grants/withdrawals/expirations recorded that month (counted from consent_records.status by occurred_at date), - users with any consent record as of month end, and net active opt-ins at month end (via a ROW_NUMBER() window per user picking the latest record with occurred_at on or before that month's last day), - the active opt-in rate (%, 1 decimal) as net active ÷ users with any record. This mirrors the prior consent report's methodology, scoped to the single purpose and the full unclipped 3-year range.”

The report comes back overnight

As Priyanka Desai saw it, from the small dataset the agents worked on

month   | grants_recorded | withdrawals_recorded | expirations_recorded | users_with_any_consent | net_active_opt_ins | active_opt_in_rate_pct
--------+-----------------+----------------------+----------------------+------------------------+--------------------+-----------------------
2023-01 |              66 |                    4 |                    0 |                    713 |                709 |                   99.4
2023-02 |              73 |                    2 |                    0 |                    786 |                780 |                   99.2
2023-03 |              99 |                    9 |                    0 |                    885 |                870 |                   98.3
2023-04 |              86 |                    0 |                    0 |                    971 |                956 |                   98.5
2023-05 |              89 |                    5 |                    0 |                   1060 |               1040 |                   98.1
2023-06 |              90 |                    9 |                    0 |                   1150 |               1121 |                   97.5
2023-07 |              76 |                    7 |                    0 |                   1226 |               1190 |                   97.1
2023-08 |              77 |                    8 |                    0 |                   1303 |               1259 |                   96.6
2023-09 |              83 |                    6 |                    0 |                   1386 |               1336 |                   96.4
2023-10 |              98 |                   13 |                    0 |                   1484 |               1421 |                   95.8
2023-11 |             170 |                    7 |                    0 |                   1654 |               1584 |                   95.8
2023-12 |             149 |                   17 |                    0 |                   1803 |               1716 |                   95.2
2024-01 |              86 |                   12 |                    0 |                   1889 |               1790 |                   94.8
2024-02 |              66 |                   10 |                    0 |                   1955 |               1846 |                   94.4
2024-03 |              94 |                    7 |                    0 |                   2049 |               1933 |                   94.3
2024-04 |              89 |                   10 |                    0 |                   2138 |               2012 |                   94.1
2024-05 |             100 |                   11 |                    0 |                   2238 |               2101 |                   93.9
2024-06 |              99 |                   14 |                    0 |                   2337 |               2186 |                   93.5
2024-07 |              90 |                   19 |                    0 |                   2427 |               2257 |                   93.0
2024-08 |             104 |                   16 |                    0 |                   2531 |               2345 |                   92.7
2024-09 |              97 |                    9 |                    0 |                   2628 |               2433 |                   92.6
2024-10 |             102 |                   14 |                    0 |                   2730 |               2521 |                   92.3
2024-11 |             154 |                    9 |                    0 |                   2884 |               2666 |                   92.4
2024-12 |             174 |                   18 |                    0 |                   3058 |               2822 |                   92.3
2025-01 |              91 |                   15 |                    0 |                   3149 |               2898 |                   92.0
2025-02 |              82 |                   12 |                    0 |                   3231 |               2968 |                   91.9
2025-03 |             107 |                   17 |                    0 |                   3338 |               3058 |                   91.6
2025-04 |             101 |                   13 |                    0 |                   3439 |               3146 |                   91.5
2025-05 |             125 |                   14 |                    0 |                   3564 |               3257 |                   91.4
2025-06 |              96 |                    9 |                    0 |                   3660 |               3344 |                   91.4
2025-07 |             106 |                   28 |                    0 |                   3766 |               3422 |                   90.9
2025-08 |              86 |                   26 |                    0 |                   3852 |               3482 |                   90.4
2025-09 |             132 |                   17 |                    0 |                   3984 |               3597 |                   90.3
2025-10 |             146 |                   17 |                    0 |                   4130 |               3726 |                   90.2
2025-11 |             184 |                   13 |                    0 |                   4314 |               3897 |                   90.3
2025-12 |             236 |                   14 |                    0 |                   4550 |               4119 |                   90.5
(36 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
- q331Post-withdrawal marketing suppression check
It builds on:
- q113Consent trend by purpose and month: grants, withdrawals, and net opt-in rate

It becomes benchmark query q116

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

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

The benchmark text, against the submitted SQL

WITH months AS (  SELECT month_label, MIN(calendar_date) AS month_start, MAX(calendar_date) AS month_end  FROM calendar_days  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'  GROUP BY month_label),flow AS (  SELECT cd.month_label,  SELECT cd.month_label AS month_label,         SUM(CASE WHEN cr.status = 'granted' THEN 1 ELSE 0 END) AS grants,         SUM(CASE WHEN cr.status = 'withdrawn' THEN 1 ELSE 0 END) AS withdrawals,         SUM(CASE WHEN cr.status = 'expired' THEN 1 ELSE 0 END) AS expirations  FROM calendar_days cd  JOIN consent_records cr    ON cr.occurred_at::date = cd.calendar_date    ON CAST(cr.occurred_at AS date) = cd.calendar_date   AND cr.purpose = 'marketing_email'  WHERE cd.calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'  GROUP BY cd.month_label),snapshot_base AS (  SELECT m.month_label,         cr.user_id,         cr.status,         ROW_NUMBER() OVER (PARTITION BY m.month_label, cr.user_id ORDER BY cr.occurred_at DESC, cr.id DESC) AS rn  SELECT m.month_label AS month_label,         cr.user_id AS user_id,         cr.status AS status,         ROW_NUMBER() OVER (PARTITION BY m.month_label, cr.user_id ORDER BY cr.occurred_at DESC NULLS FIRST, cr.id DESC NULLS FIRST) AS rn  FROM months m  JOIN consent_records cr    ON cr.purpose = 'marketing_email'   AND cr.occurred_at < (m.month_end + INTERVAL '1 day')),monthly_active AS (  SELECT month_label,         COUNT(*) AS users_with_consent,         SUM(CASE WHEN status = 'granted' THEN 1 ELSE 0 END) AS net_active  FROM snapshot_base  WHERE rn = 1  GROUP BY month_label)SELECT  m.month_label AS month,  COALESCE(f.grants, 0) AS grants_recorded,  COALESCE(f.withdrawals, 0) AS withdrawals_recorded,  COALESCE(f.expirations, 0) AS expirations_recorded,  COALESCE(ma.users_with_consent, 0) AS users_with_any_consent,  COALESCE(ma.net_active, 0) AS net_active_opt_ins,  ROUND(100.0 * COALESCE(ma.net_active, 0) / NULLIF(ma.users_with_consent, 0), 1) AS active_opt_in_rate_pct  100.0 * COALESCE(ma.net_active, 0) / NULLIF(ma.users_with_consent, 0) AS active_opt_in_rate_pctFROM months mLEFT JOIN flow f ON f.month_label = m.month_labelLEFT JOIN monthly_active ma ON ma.month_label = m.month_labelORDER BY m.month_label ASCORDER BY m.month_label ASC NULLS LAST

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

WITH months AS (
  SELECT month_label, MIN(calendar_date) AS month_start, MAX(calendar_date) AS month_end
  FROM calendar_days
  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
  GROUP BY month_label
),
flow AS (
  SELECT cd.month_label AS month_label,
         SUM(CASE WHEN cr.status = 'granted' THEN 1 ELSE 0 END) AS grants,
         SUM(CASE WHEN cr.status = 'withdrawn' THEN 1 ELSE 0 END) AS withdrawals,
         SUM(CASE WHEN cr.status = 'expired' THEN 1 ELSE 0 END) AS expirations
  FROM calendar_days cd
  JOIN consent_records cr
    ON CAST(cr.occurred_at AS date) = cd.calendar_date
   AND cr.purpose = 'marketing_email'
  WHERE cd.calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
  GROUP BY cd.month_label
),
snapshot_base AS (
  SELECT m.month_label AS month_label,
         cr.user_id AS user_id,
         cr.status AS status,
         ROW_NUMBER() OVER (PARTITION BY m.month_label, cr.user_id ORDER BY cr.occurred_at DESC NULLS FIRST, cr.id DESC NULLS FIRST) AS rn
  FROM months m
  JOIN consent_records cr
    ON cr.purpose = 'marketing_email'
   AND cr.occurred_at < (m.month_end + INTERVAL '1 day')
),
monthly_active AS (
  SELECT month_label,
         COUNT(*) AS users_with_consent,
         SUM(CASE WHEN status = 'granted' THEN 1 ELSE 0 END) AS net_active
  FROM snapshot_base
  WHERE rn = 1
  GROUP BY month_label
)
SELECT
  m.month_label AS month,
  COALESCE(f.grants, 0) AS grants_recorded,
  COALESCE(f.withdrawals, 0) AS withdrawals_recorded,
  COALESCE(f.expirations, 0) AS expirations_recorded,
  COALESCE(ma.users_with_consent, 0) AS users_with_any_consent,
  COALESCE(ma.net_active, 0) AS net_active_opt_ins,
  100.0 * COALESCE(ma.net_active, 0) / NULLIF(ma.users_with_consent, 0) AS active_opt_in_rate_pct
FROM months m
LEFT JOIN flow f ON f.month_label = m.month_label
LEFT JOIN monthly_active ma ON ma.month_label = m.month_label
ORDER BY m.month_label ASC NULLS LAST

Fireboltno result within 30 s

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

Viktor's whole session, 7 turns

The request, as Viktor received it

Report request from Priyanka Desai (analyst for the Data Protection Officer):

Title: Marketing-email consent opt-in trend, full history
Question: Has the marketing-email opt-in rate meaningfully changed over the full 3-year history, and did the early decline continue or stabilize?
Grain: one row per calendar month, purpose fixed to 'marketing_email'
Columns:
- month (YYYY-MM)
- grants recorded in month
- withdrawals recorded in month
- expirations recorded in month
- users with any consent record as of month end
- net active opt-ins at month end
- active opt-in rate at month end (%, 1 decimal)
Details: Same logic as last night's consent report (consent_records, effective status via most-recent-row-per-user-per-purpose-as-of-month-end using a window function), but filtered to purpose = 'marketing_email' only, and spanning the full 2023-01 through 2025-12 range so none of the 36 months are cut off by the row limit. Platform-wide, all tenants. Order by month ascending.

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

Viktor calls dry_run

WITH months AS (
  SELECT month_label, MIN(calendar_date) AS month_start, MAX(calendar_date) AS month_end
  FROM calendar_days
  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
  GROUP BY month_label
),
flow AS (
  SELECT cd.month_label,
         SUM(CASE WHEN cr.status = 'granted' THEN 1 ELSE 0 END) AS grants,
         SUM(CASE WHEN cr.status = 'withdrawn' THEN 1 ELSE 0 END) AS withdrawals,
         SUM(CASE WHEN cr.status = 'expired' THEN 1 ELSE 0 END) AS expirations
  FROM calendar_days cd
  JOIN consent_records cr
    ON cr.occurred_at::date = cd.calendar_date
   AND cr.purpose = 'marketing_email'
  WHERE cd.calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
  GROUP BY cd.month_label
),
snapshot_base AS (
  SELECT m.month_label,
         cr.user_id,
         cr.status,
         ROW_NUMBER() OVER (PARTITION BY m.month_label, cr.user_id ORDER BY cr.occurred_at DESC, cr.id DESC) AS rn
  FROM months m
  JOIN consent_records cr
    ON cr.purpose = 'marketing_email'
   AND cr.occurred_at < (m.month_end + INTERVAL '1 day')
),
monthly_active AS (
  SELECT month_label,
         COUNT(*) AS users_with_consent,
         SUM(CASE WHEN status = 'granted' THEN 1 ELSE 0 END) AS net_active
  FROM snapshot_base
  WHERE rn = 1
  GROUP BY month_label
)
SELECT
  m.month_label AS month,
  COALESCE(f.grants, 0) AS grants_recorded,
  COALESCE(f.withdrawals, 0) AS withdrawals_recorded,
  COALESCE(f.expirations, 0) AS expirations_recorded,
  COALESCE(ma.users_with_consent, 0) AS users_with_any_consent,
  COALESCE(ma.net_active, 0) AS net_active_opt_ins,
  ROUND(100.0 * COALESCE(ma.net_active, 0) / NULLIF(ma.users_with_consent, 0), 1) AS active_opt_in_rate_pct
FROM months m
LEFT JOIN flow f ON f.month_label = m.month_label
LEFT JOIN monthly_active ma ON ma.month_label = m.month_label
ORDER BY m.month_label ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, grants_recorded, withdrawals_recorded, expirations_recorded, users_with_any_consent, net_active_opt_ins, active_opt_in_rate_pct

ViktorQuery compiles and returns the expected columns. Submitting.

Viktor calls submit

WITH months AS (
  SELECT month_label, MIN(calendar_date) AS month_start, MAX(calendar_date) AS month_end
  FROM calendar_days
  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
  GROUP BY month_label
),
flow AS (
  SELECT cd.month_label,
         SUM(CASE WHEN cr.status = 'granted' THEN 1 ELSE 0 END) AS grants,
         SUM(CASE WHEN cr.status = 'withdrawn' THEN 1 ELSE 0 END) AS withdrawals,
         SUM(CASE WHEN cr.status = 'expired' THEN 1 ELSE 0 END) AS expirations
  FROM calendar_days cd
  JOIN consent_records cr
    ON cr.occurred_at::date = cd.calendar_date
   AND cr.purpose = 'marketing_email'
  WHERE cd.calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
  GROUP BY cd.month_label
),
snapshot_base AS (
  SELECT m.month_label,
         cr.user_id,
         cr.status,
         ROW_NUMBER() OVER (PARTITION BY m.month_label, cr.user_id ORDER BY cr.occurred_at DESC, cr.id DESC) AS rn
  FROM months m
  JOIN consent_records cr
    ON cr.purpose = 'marketing_email'
   AND cr.occurred_at < (m.month_end + INTERVAL '1 day')
),
monthly_active AS (
  SELECT month_label,
         COUNT(*) AS users_with_consent,
         SUM(CASE WHEN status = 'granted' THEN 1 ELSE 0 END) AS net_active
  FROM snapshot_base
  WHERE rn = 1
  GROUP BY month_label
)
SELECT
  m.month_label AS month,
  COALESCE(f.grants, 0) AS grants_recorded,
  COALESCE(f.withdrawals, 0) AS withdrawals_recorded,
  COALESCE(f.expirations, 0) AS expirations_recorded,
  COALESCE(ma.users_with_consent, 0) AS users_with_any_consent,
  COALESCE(ma.net_active, 0) AS net_active_opt_ins,
  ROUND(100.0 * COALESCE(ma.net_active, 0) / NULLIF(ma.users_with_consent, 0), 1) AS active_opt_in_rate_pct
FROM months m
LEFT JOIN flow f ON f.month_label = m.month_label
LEFT JOIN monthly_active ma ON ma.month_label = m.month_label
ORDER BY m.month_label ASC

The warehouse answers

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

ViktorSubmitted. The report computes, per calendar month from 2023-01 through 2025-12 (36 rows, platform-wide, purpose = `marketing_email`): - grants/withdrawals/expirations recorded that month (counted from `consent_records.status` by `occurred_at` date), - users with any consent record as of month end, and net active opt-ins at month end (via a `ROW_NUMBER()` window per user picking the latest record with `occurred_at` on or before that month's last day), - the active opt-in rate (%, 1 decimal) as net active ÷ users with any record. This mirrors the prior consent report's methodology, scoped to the single purpose and the full unclipped 3-year range.

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

Viktor's transcript (273 KB) · Priyanka Desai's transcript (894 KB) · Ines Laurent's transcript (263 KB) · the night's plan (4 KB) · the night's reports (15 KB) · the week's memo (3 KB)

Marketing-email consent opt-in trend, full history

Ines Laurent writes to Priyanka Desai

Priyanka Desai plans the night

Viktor writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q116

Everything, as recorded