Queries · q331 · Data Protection Officer · week 2, night 3

Post-withdrawal marketing suppression check

“When a shopper withdraws marketing_email consent, do we actually stop sending them marketing campaign emails, or does sending continue after withdrawal?”

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 SQLFelixJunior engineer who nests subqueries in FROM and WHERE (IN, EXISTS, correlated subqueries) rather than CTEs.

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 Data Protection Officer's desk
Week 2; shared with Henrik Aalto, Chief Information Security Officer
A data protection authority asked how we handle privacy: how many privacy requests we receive and how fast we complete them, how consent is recorded, and what remains after an account is deleted.
One of the 35 events we wrote for the role play; the manager heard of it at the start of week 2 and had to cover it in that week's note. How events arise · All the events
Ines Laurent writes to Priyanka Desai
The manager, at the start of week 2
Subject: Week 2 — DPA inquiry changes our priorities

Priyanka,

Good work — this is exactly the kind of digging I wanted. A few things surprised me and now matter more because of the regulator inquiry (which I'm handling jointly with Henrik Aalto, CISO — our response needs to be accurate and defensible, not just reassuring).

What struck me:
- The three "open" erasure requests aren't really open, they're parked behind legal holds — but we don't actually know if those holds are still justified. If a regulator asked us today why those erasures haven't happened, "disputes" that are 1.5–2.6 years old is not a comfortable answer without evidence.
- We have a closure-tracking gap (rejected requests never get a completion date) and apparently zero portability requests in three years. Both could be true, or both could be an artifact of how we log things — and I don't want to hand the regulator a number I can't stand behind.
- The email retention failure is concrete and fixable, but it also means I can't yet answer "what remains after an account is deleted" with confidence for other categories until we know how widespread this is.
- The consent anomaly (one category showing suspiciously perfect opt-in) undermines my confidence in saying "this is how consent is recorded" to a regulator until it's explained.
So, for this week, please prioritize work that lets us answer the DPA's three questions honestly: volume and speed of privacy requests, how consent is captured and whether it's reliable, and what data persists post-deletion. That's the deliverable Henrik and I need to brief the board audit committee before we respond.

My questions:
1. For the three long-running legal holds, can we establish — from whatever evidence exists — whether the underlying disputes are still active, so I can decide whether to push for release or document why they must stand?
2. Can we get a trustworthy, defensible count of privacy requests received and resolved, including the rejected ones and whether "zero portability requests ever" is real or a logging blind spot — the regulator will ask for exactly this?
3. How confident can we be in what consent records actually show, given the anomaly you flagged — do we have other categories with the same too-clean pattern, and does that change how I'd describe our consent process to a regulator?
4. Beyond email, how much shopper data elsewhere is sitting past its stated retention window right now — I need a true current picture of "what remains after deletion," not just the one confirmed violation.
If all four can't fit, start with 2 and 4 — those go directly into the regulator response.

Thanks, Ines
A note from the Chief Information Security Officer, Henrik Aalto
Subject: Security angle for the DPA response

Hi Priyanka,

A few things from my side that would strengthen the privacy response:
1. When a data subject request results in erasure, can we show what login/session and access history remains afterward, and for how long — i.e. do our retention rules actually clear it out, or does it linger?
2. For consent withdrawals, do we have any evidence that we stop using/collecting the related data promptly (e.g. no further sign-ins or sensitive-data access tied to that account after withdrawal)?
3. On the completion-time question the DPA is asking about — can we also show whether requests touching account security data (sessions, API/service credentials, access logs) take longer to close than others? That's often where delays hide.
Happy to compare notes before you send anything to data engineering.

Henrik
Priyanka Desai plans the night
The analyst · week 2, night 3, request 3 of 3
Two good resolutions from last night: the data_exports status audit shows all 11 exports have completed_at AND file_url populated — they did fulfill, they're just labeled 'expired' because the download link's retention window has since passed, so I'm closing out the "did access requests actually deliver data" concern. The pre-2023 DSAR rows show created_at exactly equal to received_at for all 12 — that's the opposite of what I'd expect from a deliberate migration backfill (where insertion would postdate the original event), so I can't confidently call this "verified legitimate backfill"; I'm flagging it as an open inconsistency with the documented 2023-01-01 history start that needs an answer from engineering, not more SQL from me. The legal-hold evidence (report 3) is useful but indirect: 2 of 3 accounts already show a partial refund 6–12 months ago with zero ticket activity since, suggesting the disputes may already be effectively resolved while the hold was never released — worth flagging to Ines as "likely stale, recommend review," with the caveat that I matched by "most recent payment for that user," not a guaranteed link to the specific disputed payment. With priorities 1, 2, and 4 now substantially covered, tonight I'm moving to the untouched items: priority 3 (consent reliability — is the analytics_cookies anomaly isolated or systemic?) and Henrik's two unaddressed security asks (what persists in sessions/logins/access_log after erasure; do we actually stop marketing to someone who withdrew consent). Henrik's third ask (closure time for security-data requests) still has no clean way to tag which DSARs touch security data, so I'm deferring it to the final night or flagging it as not currently answerable.
Post-withdrawal marketing suppression check
Question
When a shopper withdraws marketing_email consent, do we actually stop sending them marketing campaign emails, or does sending continue after withdrawal?
One row per
single summary row across all users with an effective withdrawn marketing_email consent as of 2026-01-05 (i.e. their most recent consent_records row for purpose='marketing_email' has status='withdrawn', with no later 'granted' row)
Columns
- users with effective withdrawal as of today (count)
- of those, users who received at least 1 campaign_messages send after their withdrawal date (count)
- pct of withdrawn users contacted after withdrawal
- total post-withdrawal sends across all such users (count)
- max days between withdrawal and a post-withdrawal send (worst case observed)
- most recent post-withdrawal send date observed

Felix 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
  COUNT(DISTINCT w.user_key) AS withdrawn_users,
  COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) AS contacted_after_withdrawal_users,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) / NULLIF(COUNT(DISTINCT w.user_key), 0), 2) AS pct_contacted_after_withdrawal,
  COUNT(cm.id) AS total_post_withdrawal_sends,
  MAX(COALESCE(cm.sent_at, cm.created_at)::date - w.withdrawn_at::date) AS max_days_withdrawal_to_send,
  MAX(COALESCE(cm.sent_at, cm.created_at)) AS most_recent_post_withdrawal_send
FROM (
  SELECT
    tenant_id,
    user_id,
    occurred_at AS withdrawn_at,
    ROW_NUMBER() OVER (ORDER BY tenant_id, user_id) AS user_key
  FROM (
    SELECT
      tenant_id,
      user_id,
      status,
      occurred_at,
      ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY occurred_at DESC, id DESC) AS rn
    FROM consent_records
    WHERE purpose = 'marketing_email'
  ) ranked
  WHERE rn = 1 AND status = 'withdrawn'
) w
LEFT JOIN campaign_messages cm
  ON cm.tenant_id = w.tenant_id
  AND cm.user_id = w.user_id
  AND COALESCE(cm.sent_at, cm.created_at) > w.withdrawn_at

Submitted · 1 change from the dry run before · Submitted to tonight's batch. Your work on this request is done.

SELECT  COUNT(DISTINCT w.user_key) AS withdrawn_users,  COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) AS contacted_after_withdrawal_users,  ROUND(100.0 * COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) / NULLIF(COUNT(DISTINCT w.user_key), 0), 2) AS pct_contacted_after_withdrawal,  COUNT(cm.id) AS total_post_withdrawal_sends,  MAX(COALESCE(cm.sent_at, cm.created_at)::date - w.withdrawn_at::date) AS max_days_withdrawal_to_send,  MAX(COALESCE(cm.sent_at, cm.created_at)) AS most_recent_post_withdrawal_sendFROM (  SELECT    tenant_id,    user_id,    occurred_at AS withdrawn_at,    ROW_NUMBER() OVER (ORDER BY tenant_id, user_id) AS user_key  FROM (    SELECT      tenant_id,      user_id,      status,      occurred_at,      ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY occurred_at DESC, id DESC) AS rn    FROM consent_records    WHERE purpose = 'marketing_email'  ) ranked  WHERE rn = 1 AND status = 'withdrawn') wLEFT JOIN campaign_messages cm  ON cm.tenant_id = w.tenant_id  AND cm.user_id = w.user_id  AND COALESCE(cm.sent_at, cm.created_at) > w.withdrawn_atORDER BY withdrawn_users

“Submitted. The report returns one row with: total users whose latest marketing_email consent is 'withdrawn', how many of those received a campaign email after withdrawal, the contact rate, total post-withdrawal sends, the worst-case lag in days, and the most recent offending send date — exactly the grain and columns Priyanka asked for.”

The report comes back overnight

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

withdrawn_users | contacted_after_withdrawal_users | pct_contacted_after_withdrawal | total_post_withdrawal_sends | max_days_withdrawal_to_send | most_recent_post_withdrawal_send
----------------+----------------------------------+--------------------------------+-----------------------------+-----------------------------+---------------------------------
            431 |                                0 |                            0.0 |                           0 |                        NULL |                             NULL
(1 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
It builds on:
- q116Marketing-email consent opt-in trend, full history

It becomes benchmark query q331

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

spellingcast (2×)
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
placeholderdate diff days
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (5×)

The benchmark text, against the submitted SQL

SELECT  COUNT(DISTINCT w.user_key) AS withdrawn_users,  COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) AS contacted_after_withdrawal_users,  ROUND(100.0 * COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) / NULLIF(COUNT(DISTINCT w.user_key), 0), 2) AS pct_contacted_after_withdrawal,  100.0 * COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) / NULLIF(COUNT(DISTINCT w.user_key), 0) AS pct_contacted_after_withdrawal,  COUNT(cm.id) AS total_post_withdrawal_sends,  MAX(COALESCE(cm.sent_at, cm.created_at)::date - w.withdrawn_at::date) AS max_days_withdrawal_to_send,  MAX({{date_diff_days(w.withdrawn_at, COALESCE(cm.sent_at, cm.created_at))}}) AS max_days_withdrawal_to_send,  MAX(COALESCE(cm.sent_at, cm.created_at)) AS most_recent_post_withdrawal_sendFROM (  SELECT    tenant_id,    user_id,    occurred_at AS withdrawn_at,    ROW_NUMBER() OVER (ORDER BY tenant_id, user_id) AS user_key    ROW_NUMBER() OVER (ORDER BY tenant_id NULLS LAST, user_id NULLS LAST) AS user_key  FROM (    SELECT      tenant_id,      user_id,      status,      occurred_at,      ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY occurred_at DESC, id DESC) AS rn      ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY occurred_at DESC NULLS FIRST, id DESC NULLS FIRST) AS rn    FROM consent_records    WHERE purpose = 'marketing_email'  ) ranked  WHERE rn = 1 AND status = 'withdrawn') wLEFT JOIN campaign_messages cm  ON cm.tenant_id = w.tenant_id  AND cm.user_id = w.user_id  AND COALESCE(cm.sent_at, cm.created_at) > w.withdrawn_atORDER BY withdrawn_usersORDER BY withdrawn_users NULLS LAST

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

SELECT
  COUNT(DISTINCT w.user_key) AS withdrawn_users,
  COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) AS contacted_after_withdrawal_users,
  100.0 * COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) / NULLIF(COUNT(DISTINCT w.user_key), 0) AS pct_contacted_after_withdrawal,
  COUNT(cm.id) AS total_post_withdrawal_sends,
  MAX(DATE_DIFF('day', w.withdrawn_at, COALESCE(cm.sent_at, cm.created_at))) AS max_days_withdrawal_to_send,
  MAX(COALESCE(cm.sent_at, cm.created_at)) AS most_recent_post_withdrawal_send
FROM (
  SELECT
    tenant_id,
    user_id,
    occurred_at AS withdrawn_at,
    ROW_NUMBER() OVER (ORDER BY tenant_id NULLS LAST, user_id NULLS LAST) AS user_key
  FROM (
    SELECT
      tenant_id,
      user_id,
      status,
      occurred_at,
      ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY occurred_at DESC NULLS FIRST, id DESC NULLS FIRST) AS rn
    FROM consent_records
    WHERE purpose = 'marketing_email'
  ) ranked
  WHERE rn = 1 AND status = 'withdrawn'
) w
LEFT JOIN campaign_messages cm
  ON cm.tenant_id = w.tenant_id
  AND cm.user_id = w.user_id
  AND COALESCE(cm.sent_at, cm.created_at) > w.withdrawn_at
ORDER BY withdrawn_users NULLS LAST

SELECT
  COUNT(DISTINCT w.user_key) AS withdrawn_users,
  COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) AS contacted_after_withdrawal_users,
  100.0 * COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) / NULLIF(COUNT(DISTINCT w.user_key), 0) AS pct_contacted_after_withdrawal,
  COUNT(cm.id) AS total_post_withdrawal_sends,
  MAX((CAST(COALESCE(cm.sent_at, cm.created_at) AS DATE) - CAST(w.withdrawn_at AS DATE))) AS max_days_withdrawal_to_send,
  MAX(COALESCE(cm.sent_at, cm.created_at)) AS most_recent_post_withdrawal_send
FROM (
  SELECT
    tenant_id,
    user_id,
    occurred_at AS withdrawn_at,
    ROW_NUMBER() OVER (ORDER BY tenant_id NULLS LAST, user_id NULLS LAST) AS user_key
  FROM (
    SELECT
      tenant_id,
      user_id,
      status,
      occurred_at,
      ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY occurred_at DESC NULLS FIRST, id DESC NULLS FIRST) AS rn
    FROM consent_records
    WHERE purpose = 'marketing_email'
  ) ranked
  WHERE rn = 1 AND status = 'withdrawn'
) w
LEFT JOIN campaign_messages cm
  ON cm.tenant_id = w.tenant_id
  AND cm.user_id = w.user_id
  AND COALESCE(cm.sent_at, cm.created_at) > w.withdrawn_at
ORDER BY withdrawn_users NULLS LAST

SELECT
  COUNT(DISTINCT w.user_key) AS withdrawn_users,
  COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) AS contacted_after_withdrawal_users,
  100.0 * COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) / NULLIF(COUNT(DISTINCT w.user_key), 0) AS pct_contacted_after_withdrawal,
  COUNT(cm.id) AS total_post_withdrawal_sends,
  MAX(dateDiff('day', w.withdrawn_at, COALESCE(cm.sent_at, cm.created_at))) AS max_days_withdrawal_to_send,
  MAX(COALESCE(cm.sent_at, cm.created_at)) AS most_recent_post_withdrawal_send
FROM (
  SELECT
    tenant_id,
    user_id,
    occurred_at AS withdrawn_at,
    ROW_NUMBER() OVER (ORDER BY tenant_id NULLS LAST, user_id NULLS LAST) AS user_key
  FROM (
    SELECT
      tenant_id,
      user_id,
      status,
      occurred_at,
      ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY occurred_at DESC NULLS FIRST, id DESC NULLS FIRST) AS rn
    FROM consent_records
    WHERE purpose = 'marketing_email'
  ) ranked
  WHERE rn = 1 AND status = 'withdrawn'
) w
LEFT JOIN campaign_messages cm
  ON cm.tenant_id = w.tenant_id
  AND cm.user_id = w.user_id
  AND COALESCE(cm.sent_at, cm.created_at) > w.withdrawn_at
ORDER BY withdrawn_users NULLS LAST

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

Felix's whole session, 6 turns

The request, as Felix received it

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

Title: Post-withdrawal marketing suppression check
Question: When a shopper withdraws marketing_email consent, do we actually stop sending them marketing campaign emails, or does sending continue after withdrawal?
Grain: single summary row across all users with an effective withdrawn marketing_email consent as of 2026-01-05 (i.e. their most recent consent_records row for purpose='marketing_email' has status='withdrawn', with no later 'granted' row)
Columns:
- users with effective withdrawal as of today (count)
- of those, users who received at least 1 campaign_messages send after their withdrawal date (count)
- pct of withdrawn users contacted after withdrawal
- total post-withdrawal sends across all such users (count)
- max days between withdrawal and a post-withdrawal send (worst case observed)
- most recent post-withdrawal send date observed
Details: Source: consent_records (window function per user to find the latest marketing_email row, filtered to status='withdrawn') joined to campaign_messages on user_id, counting sends where campaign_messages.sent_at (or created_at if sent_at is null) is after the withdrawal's occurred_at. Platform-wide, full history. This is a single aggregate row by design — the underlying population of withdrawn users is in the thousands (per last week's consent trend), so a per-user listing would blow past the row cap; we need the magnitude and worst case, not a row-by-row listing. If the 'contacted after withdrawal' count is non-trivial, that's evidence we don't suppress sends promptly.

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

Felix calls dry_run

SELECT
  COUNT(DISTINCT w.user_key) AS withdrawn_users,
  COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) AS contacted_after_withdrawal_users,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) / NULLIF(COUNT(DISTINCT w.user_key), 0), 2) AS pct_contacted_after_withdrawal,
  COUNT(cm.id) AS total_post_withdrawal_sends,
  MAX(COALESCE(cm.sent_at, cm.created_at)::date - w.withdrawn_at::date) AS max_days_withdrawal_to_send,
  MAX(COALESCE(cm.sent_at, cm.created_at)) AS most_recent_post_withdrawal_send
FROM (
  SELECT
    tenant_id,
    user_id,
    occurred_at AS withdrawn_at,
    ROW_NUMBER() OVER (ORDER BY tenant_id, user_id) AS user_key
  FROM (
    SELECT
      tenant_id,
      user_id,
      status,
      occurred_at,
      ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY occurred_at DESC, id DESC) AS rn
    FROM consent_records
    WHERE purpose = 'marketing_email'
  ) ranked
  WHERE rn = 1 AND status = 'withdrawn'
) w
LEFT JOIN campaign_messages cm
  ON cm.tenant_id = w.tenant_id
  AND cm.user_id = w.user_id
  AND COALESCE(cm.sent_at, cm.created_at) > w.withdrawn_at

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: withdrawn_users, contacted_after_withdrawal_users, pct_contacted_after_withdrawal, total_post_withdrawal_sends, max_days_withdrawal_to_send, most_recent_post_withdrawal_send

Felix calls submit

SELECT
  COUNT(DISTINCT w.user_key) AS withdrawn_users,
  COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) AS contacted_after_withdrawal_users,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN cm.id IS NOT NULL THEN w.user_key END) / NULLIF(COUNT(DISTINCT w.user_key), 0), 2) AS pct_contacted_after_withdrawal,
  COUNT(cm.id) AS total_post_withdrawal_sends,
  MAX(COALESCE(cm.sent_at, cm.created_at)::date - w.withdrawn_at::date) AS max_days_withdrawal_to_send,
  MAX(COALESCE(cm.sent_at, cm.created_at)) AS most_recent_post_withdrawal_send
FROM (
  SELECT
    tenant_id,
    user_id,
    occurred_at AS withdrawn_at,
    ROW_NUMBER() OVER (ORDER BY tenant_id, user_id) AS user_key
  FROM (
    SELECT
      tenant_id,
      user_id,
      status,
      occurred_at,
      ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY occurred_at DESC, id DESC) AS rn
    FROM consent_records
    WHERE purpose = 'marketing_email'
  ) ranked
  WHERE rn = 1 AND status = 'withdrawn'
) w
LEFT JOIN campaign_messages cm
  ON cm.tenant_id = w.tenant_id
  AND cm.user_id = w.user_id
  AND COALESCE(cm.sent_at, cm.created_at) > w.withdrawn_at
ORDER BY withdrawn_users

The warehouse answers

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

FelixSubmitted. The report returns one row with: total users whose latest `marketing_email` consent is 'withdrawn', how many of those received a campaign email after withdrawal, the contact rate, total post-withdrawal sends, the worst-case lag in days, and the most recent offending send date — exactly the grain and columns Priyanka asked for.

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

Felix's transcript (270 KB) · Priyanka Desai's transcript (894 KB) · Ines Laurent's transcript (263 KB) · the night's plan (6 KB) · the night's reports (14 KB) · the week's memo (3 KB)

Post-withdrawal marketing suppression check

A business event lands on the Data Protection Officer's desk

Ines Laurent writes to Priyanka Desai

Priyanka Desai plans the night

Felix writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q331

Everything, as recorded