Queries · q113 · Data Protection Officer · week 1, night 1
“How has the mix of granted vs. withdrawn vs. never-given consent changed over time, by purpose?”
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: 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.
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
The analyst · week 1, night 1, request 2 of 3
Ines asked me to scope item 1 first (DSAR volume/closure time/overdue, trended monthly) but also flagged items 2 and 3 as high priority and well-defined enough to start in parallel, since the week is tight (5 topics, 4 nights). None of these three depend on each other's results, so I'm running all as independent first-look reports tonight rather than waiting. I kept each to one grain and used a dimension-as-row-grain (purpose, severity) instead of guessing at unknown code values, per the carrier/month pattern in the handbook. Items 4 (retention vs. policy) and 5 (legal holds) are intentionally deferred to a later night since they need review of retention_policies/legal_holds structure first and aren't part of what Ines asked me to scope tonight.
The engineer · 1 dry run on the empty schema, then the submission
SELECT metrics.purpose AS purpose, metrics.month_label AS month, metrics.grants_recorded AS grants_recorded, metrics.withdrawals_recorded AS withdrawals_recorded, metrics.expirations_recorded AS expirations_recorded, metrics.users_with_any_record AS users_with_any_record, metrics.net_active_optins AS net_active_optins, ROUND( CASE WHEN metrics.users_with_any_record = 0 THEN NULL ELSE 100.0 * metrics.net_active_optins / metrics.users_with_any_record END, 1) AS active_optin_rate_pct FROM ( SELECT p.purpose AS purpose, m.month_start AS month_start, m.month_label AS month_label, m.month_end AS month_end, (SELECT COUNT(*) FROM consent_records cr WHERE cr.purpose = p.purpose AND cr.status = 'granted' AND CAST(cr.occurred_at AS DATE) BETWEEN m.month_start AND m.month_end) AS grants_recorded, (SELECT COUNT(*) FROM consent_records cr WHERE cr.purpose = p.purpose AND cr.status = 'withdrawn' AND CAST(cr.occurred_at AS DATE) BETWEEN m.month_start AND m.month_end) AS withdrawals_recorded, (SELECT COUNT(*) FROM consent_records cr WHERE cr.purpose = p.purpose AND cr.status = 'expired' AND CAST(cr.occurred_at AS DATE) BETWEEN m.month_start AND m.month_end) AS expirations_recorded, (SELECT COUNT(DISTINCT cr.user_id) FROM consent_records cr WHERE cr.purpose = p.purpose AND CAST(cr.occurred_at AS DATE) <= m.month_end) AS users_with_any_record, (SELECT COUNT(*) FROM ( SELECT cr.user_id AS user_id, cr.status AS status, ROW_NUMBER() OVER (PARTITION BY cr.tenant_id, cr.user_id, cr.purpose ORDER BY cr.occurred_at DESC) AS rn FROM consent_records cr WHERE cr.purpose = p.purpose AND CAST(cr.occurred_at AS DATE) <= m.month_end ) ranked WHERE ranked.rn = 1 AND ranked.status = 'granted') AS net_active_optins FROM (SELECT DISTINCT purpose FROM consent_records) p CROSS JOIN ( SELECT month_start, month_label, MAX(calendar_date) AS month_end FROM calendar_days WHERE year_num BETWEEN 2023 AND 2025 GROUP BY month_start, month_label ) m ) metrics ORDER BY metrics.purpose, metrics.month_start
“Query compiles and returns exactly the requested columns. Submitting.”
“Submitted. The report builds the full purpose × month grid from calendar_days (2023‑01 through 2025‑12) cross-joined with the distinct purposes found in consent_records, then computes monthly grants/withdrawals/expirations counts, cumulative distinct users with any record as of month end, and net active opt-ins via the specified ROW_NUMBER "most recent row as of month end" window function, with the opt-in rate rounded to 1 decimal percent (left NULL when the denominator is zero).”
As Priyanka Desai saw it, from the small dataset the agents worked on
purpose | month | grants_recorded | withdrawals_recorded | expirations_recorded | users_with_any_record | net_active_optins | active_optin_rate_pct ------------------+---------+-----------------+----------------------+----------------------+-----------------------+-------------------+---------------------- analytics_cookies | 2023-01 | 128 | 0 | 0 | 1199 | 1199 | 100.0 analytics_cookies | 2023-02 | 118 | 0 | 0 | 1317 | 1317 | 100.0 analytics_cookies | 2023-03 | 135 | 0 | 0 | 1452 | 1452 | 100.0 analytics_cookies | 2023-04 | 146 | 0 | 0 | 1598 | 1598 | 100.0 analytics_cookies | 2023-05 | 128 | 0 | 0 | 1726 | 1726 | 100.0 analytics_cookies | 2023-06 | 106 | 0 | 0 | 1832 | 1832 | 100.0 analytics_cookies | 2023-07 | 130 | 0 | 0 | 1962 | 1962 | 100.0 analytics_cookies | 2023-08 | 151 | 0 | 0 | 2113 | 2113 | 100.0 analytics_cookies | 2023-09 | 133 | 0 | 0 | 2246 | 2246 | 100.0 analytics_cookies | 2023-10 | 154 | 0 | 0 | 2400 | 2400 | 100.0 analytics_cookies | 2023-11 | 282 | 0 | 0 | 2682 | 2682 | 100.0 analytics_cookies | 2023-12 | 191 | 0 | 0 | 2873 | 2873 | 100.0 analytics_cookies | 2024-01 | 158 | 0 | 0 | 3031 | 3031 | 100.0 analytics_cookies | 2024-02 | 117 | 0 | 0 | 3148 | 3148 | 100.0 analytics_cookies | 2024-03 | 148 | 0 | 0 | 3296 | 3296 | 100.0 analytics_cookies | 2024-04 | 150 | 0 | 0 | 3446 | 3446 | 100.0 analytics_cookies | 2024-05 | 187 | 0 | 0 | 3633 | 3633 | 100.0 analytics_cookies | 2024-06 | 122 | 0 | 0 | 3755 | 3755 | 100.0 analytics_cookies | 2024-07 | 143 | 0 | 0 | 3898 | 3898 | 100.0 analytics_cookies | 2024-08 | 142 | 0 | 0 | 4040 | 4040 | 100.0 analytics_cookies | 2024-09 | 154 | 0 | 0 | 4194 | 4194 | 100.0 analytics_cookies | 2024-10 | 170 | 0 | 0 | 4364 | 4364 | 100.0 analytics_cookies | 2024-11 | 291 | 0 | 0 | 4655 | 4655 | 100.0 analytics_cookies | 2024-12 | 282 | 0 | 0 | 4937 | 4937 | 100.0 analytics_cookies | 2025-01 | 165 | 0 | 0 | 5102 | 5102 | 100.0 analytics_cookies | 2025-02 | 122 | 0 | 0 | 5224 | 5224 | 100.0 analytics_cookies | 2025-03 | 162 | 0 | 0 | 5386 | 5386 | 100.0 analytics_cookies | 2025-04 | 170 | 0 | 0 | 5556 | 5556 | 100.0 analytics_cookies | 2025-05 | 214 | 0 | 0 | 5770 | 5770 | 100.0 analytics_cookies | 2025-06 | 184 | 0 | 0 | 5954 | 5954 | 100.0 analytics_cookies | 2025-07 | 188 | 0 | 0 | 6142 | 6142 | 100.0 analytics_cookies | 2025-08 | 175 | 0 | 0 | 6317 | 6317 | 100.0 analytics_cookies | 2025-09 | 213 | 0 | 0 | 6530 | 6530 | 100.0 analytics_cookies | 2025-10 | 254 | 0 | 0 | 6784 | 6784 | 100.0 analytics_cookies | 2025-11 | 321 | 0 | 0 | 7105 | 7105 | 100.0 analytics_cookies | 2025-12 | 299 | 0 | 0 | 7404 | 7404 | 100.0 marketing_email | 2023-01 | 66 | 4 | 0 | 713 | 709 | 99.4 marketing_email | 2023-02 | 73 | 2 | 0 | 786 | 780 | 99.2 marketing_email | 2023-03 | 99 | 9 | 0 | 885 | 870 | 98.3 marketing_email | 2023-04 | 86 | 0 | 0 | 971 | 956 | 98.5 marketing_email | 2023-05 | 89 | 5 | 0 | 1060 | 1040 | 98.1 marketing_email | 2023-06 | 90 | 9 | 0 | 1150 | 1121 | 97.5 marketing_email | 2023-07 | 76 | 7 | 0 | 1226 | 1190 | 97.1 marketing_email | 2023-08 | 77 | 8 | 0 | 1303 | 1259 | 96.6 marketing_email | 2023-09 | 83 | 6 | 0 | 1386 | 1336 | 96.4 marketing_email | 2023-10 | 98 | 13 | 0 | 1484 | 1421 | 95.8 marketing_email | 2023-11 | 170 | 7 | 0 | 1654 | 1584 | 95.8 marketing_email | 2023-12 | 149 | 17 | 0 | 1803 | 1716 | 95.2 marketing_email | 2024-01 | 86 | 12 | 0 | 1889 | 1790 | 94.8 marketing_email | 2024-02 | 66 | 10 | 0 | 1955 | 1846 | 94.4 (72 rows, first 50 printed)
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
SELECT metrics.purpose AS purpose, metrics.month_label AS month, metrics.grants_recorded AS grants_recorded, metrics.withdrawals_recorded AS withdrawals_recorded, metrics.expirations_recorded AS expirations_recorded, metrics.users_with_any_record AS users_with_any_record, metrics.net_active_optins AS net_active_optins, ROUND( CASE WHEN metrics.users_with_any_record = 0 THEN NULL ELSE 100.0 * metrics.net_active_optins / metrics.users_with_any_record END, 1) AS active_optin_rate_pct CASE WHEN metrics.users_with_any_record = 0 THEN NULL ELSE 100.0 * metrics.net_active_optins / metrics.users_with_any_record END AS active_optin_rate_pctFROM ( SELECT p.purpose AS purpose, m.month_start AS month_start, m.month_label AS month_label, m.month_end AS month_end, (SELECT COUNT(*) FROM consent_records cr WHERE cr.purpose = p.purpose AND cr.status = 'granted' AND CAST(cr.occurred_at AS DATE) BETWEEN m.month_start AND m.month_end) AS grants_recorded, (SELECT COUNT(*) FROM consent_records cr WHERE cr.purpose = p.purpose AND cr.status = 'withdrawn' AND CAST(cr.occurred_at AS DATE) BETWEEN m.month_start AND m.month_end) AS withdrawals_recorded, (SELECT COUNT(*) FROM consent_records cr WHERE cr.purpose = p.purpose AND cr.status = 'expired' AND CAST(cr.occurred_at AS DATE) BETWEEN m.month_start AND m.month_end) AS expirations_recorded, (SELECT COUNT(DISTINCT cr.user_id) FROM consent_records cr WHERE cr.purpose = p.purpose AND CAST(cr.occurred_at AS DATE) <= m.month_end) AS users_with_any_record, (SELECT COUNT(*) FROM ( SELECT cr.user_id AS user_id, cr.status AS status, ROW_NUMBER() OVER (PARTITION BY cr.tenant_id, cr.user_id, cr.purpose ORDER BY cr.occurred_at DESC) AS rn ROW_NUMBER() OVER (PARTITION BY cr.tenant_id, cr.user_id, cr.purpose ORDER BY cr.occurred_at DESC NULLS FIRST, cr.id DESC NULLS FIRST) AS rn FROM consent_records cr WHERE cr.purpose = p.purpose AND CAST(cr.occurred_at AS DATE) <= m.month_end ) ranked WHERE ranked.rn = 1 AND ranked.status = 'granted') AS net_active_optins FROM (SELECT DISTINCT purpose FROM consent_records) p CROSS JOIN ( SELECT month_start, month_label, MAX(calendar_date) AS month_end FROM calendar_days WHERE year_num BETWEEN 2023 AND 2025 GROUP BY month_start, month_label ) m) metricsORDER BY metrics.purpose, metrics.month_startORDER BY metrics.purpose NULLS LAST, metrics.month_start NULLS LAST, metrics.month_label NULLS LAST
SELECT metrics.purpose AS purpose, metrics.month_label AS month, metrics.grants_recorded AS grants_recorded, metrics.withdrawals_recorded AS withdrawals_recorded, metrics.expirations_recorded AS expirations_recorded, metrics.users_with_any_record AS users_with_any_record, metrics.net_active_optins AS net_active_optins, CASE WHEN metrics.users_with_any_record = 0 THEN NULL ELSE 100.0 * metrics.net_active_optins / metrics.users_with_any_record END AS active_optin_rate_pct FROM ( SELECT p.purpose AS purpose, m.month_start AS month_start, m.month_label AS month_label, m.month_end AS month_end, (SELECT COUNT(*) FROM consent_records cr WHERE cr.purpose = p.purpose AND cr.status = 'granted' AND CAST(cr.occurred_at AS DATE) BETWEEN m.month_start AND m.month_end) AS grants_recorded, (SELECT COUNT(*) FROM consent_records cr WHERE cr.purpose = p.purpose AND cr.status = 'withdrawn' AND CAST(cr.occurred_at AS DATE) BETWEEN m.month_start AND m.month_end) AS withdrawals_recorded, (SELECT COUNT(*) FROM consent_records cr WHERE cr.purpose = p.purpose AND cr.status = 'expired' AND CAST(cr.occurred_at AS DATE) BETWEEN m.month_start AND m.month_end) AS expirations_recorded, (SELECT COUNT(DISTINCT cr.user_id) FROM consent_records cr WHERE cr.purpose = p.purpose AND CAST(cr.occurred_at AS DATE) <= m.month_end) AS users_with_any_record, (SELECT COUNT(*) FROM ( SELECT cr.user_id AS user_id, cr.status AS status, ROW_NUMBER() OVER (PARTITION BY cr.tenant_id, cr.user_id, cr.purpose ORDER BY cr.occurred_at DESC NULLS FIRST, cr.id DESC NULLS FIRST) AS rn FROM consent_records cr WHERE cr.purpose = p.purpose AND CAST(cr.occurred_at AS DATE) <= m.month_end ) ranked WHERE ranked.rn = 1 AND ranked.status = 'granted') AS net_active_optins FROM (SELECT DISTINCT purpose FROM consent_records) p CROSS JOIN ( SELECT month_start, month_label, MAX(calendar_date) AS month_end FROM calendar_days WHERE year_num BETWEEN 2023 AND 2025 GROUP BY month_start, month_label ) m ) metrics ORDER BY metrics.purpose NULLS LAST, metrics.month_start NULLS LAST, metrics.month_label 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 Priyanka Desai (analyst for the Data Protection Officer): Title: Consent trend by purpose and month: grants, withdrawals, and net opt-in rate Question: How has the mix of granted vs. withdrawn vs. never-given consent changed over time, by purpose? Grain: one row per consent purpose and calendar month (based on consent_records.occurred_at for the month's new events; net opt-in figures are evaluated as of that month's last day) Columns: - purpose - month (YYYY-MM) - grants recorded in month (status='granted' events occurring in month) - withdrawals recorded in month (status='withdrawn' events occurring in month) - expirations recorded in month (status='expired' events occurring in month) - users with any consent record for this purpose as of month end (distinct user_id across consent_records with occurred_at <= month end) - net active opt-ins at month end (distinct users whose most recent consent_records row for this purpose, as of month end, has status='granted') - active opt-in rate at month end (net active opt-ins / users with any consent record for this purpose as of month end, rounded to 1 decimal percent) Details: Source: consent_records. 'Most recent row per user per purpose as of month end' should use a window function (ROW_NUMBER partition by tenant_id, user_id, purpose order by occurred_at desc, restricted to occurred_at <= month end) to find the effective status. Build the month series from calendar_days (month_start) so months with zero events still appear, spanning 2023-01 through 2025-12. List each distinct purpose value found in consent_records as its own set of rows (do not assume specific purpose strings in advance — group by whatever purpose values exist in the data). Platform-wide, all tenants. Order by purpose, then month ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT metrics.purpose AS purpose, metrics.month_label AS month, metrics.grants_recorded AS grants_recorded, metrics.withdrawals_recorded AS withdrawals_recorded, metrics.expirations_recorded AS expirations_recorded, metrics.users_with_any_record AS users_with_any_record, metrics.net_active_optins AS net_active_optins, ROUND( CASE WHEN metrics.users_with_any_record = 0 THEN NULL ELSE 100.0 * metrics.net_active_optins / metrics.users_with_any_record END, 1) AS active_optin_rate_pct FROM ( SELECT p.purpose AS purpose, m.month_start AS month_start, m.month_label AS month_label, m.month_end AS month_end, (SELECT COUNT(*) FROM consent_records cr WHERE cr.purpose = p.purpose AND cr.status = 'granted' AND CAST(cr.occurred_at AS DATE) BETWEEN m.month_start AND m.month_end) AS grants_recorded, (SELECT COUNT(*) FROM consent_records cr WHERE cr.purpose = p.purpose AND cr.status = 'withdrawn' AND CAST(cr.occurred_at AS DATE) BETWEEN m.month_start AND m.month_end) AS withdrawals_recorded, (SELECT COUNT(*) FROM consent_records cr WHERE cr.purpose = p.purpose AND cr.status = 'expired' AND CAST(cr.occurred_at AS DATE) BETWEEN m.month_start AND m.month_end) AS expirations_recorded, (SELECT COUNT(DISTINCT cr.user_id) FROM consent_records cr WHERE cr.purpose = p.purpose AND CAST(cr.occurred_at AS DATE) <= m.month_end) AS users_with_any_record, (SELECT COUNT(*) FROM ( SELECT cr.user_id AS user_id, cr.status AS status, ROW_NUMBER() OVER (PARTITION BY cr.tenant_id, cr.user_id, cr.purpose ORDER BY cr.occurred_at DESC) AS rn FROM consent_records cr WHERE cr.purpose = p.purpose AND CAST(cr.occurred_at AS DATE) <= m.month_end ) ranked WHERE ranked.rn = 1 AND ranked.status = 'granted') AS net_active_optins FROM (SELECT DISTINCT purpose FROM consent_records) p CROSS JOIN ( SELECT month_start, month_label, MAX(calendar_date) AS month_end FROM calendar_days WHERE year_num BETWEEN 2023 AND 2025 GROUP BY month_start, month_label ) m ) metrics ORDER BY metrics.purpose, metrics.month_start
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: purpose, month, grants_recorded, withdrawals_recorded, expirations_recorded, users_with_any_record, net_active_optins, active_optin_rate_pct
submitSELECT metrics.purpose AS purpose, metrics.month_label AS month, metrics.grants_recorded AS grants_recorded, metrics.withdrawals_recorded AS withdrawals_recorded, metrics.expirations_recorded AS expirations_recorded, metrics.users_with_any_record AS users_with_any_record, metrics.net_active_optins AS net_active_optins, ROUND( CASE WHEN metrics.users_with_any_record = 0 THEN NULL ELSE 100.0 * metrics.net_active_optins / metrics.users_with_any_record END, 1) AS active_optin_rate_pct FROM ( SELECT p.purpose AS purpose, m.month_start AS month_start, m.month_label AS month_label, m.month_end AS month_end, (SELECT COUNT(*) FROM consent_records cr WHERE cr.purpose = p.purpose AND cr.status = 'granted' AND CAST(cr.occurred_at AS DATE) BETWEEN m.month_start AND m.month_end) AS grants_recorded, (SELECT COUNT(*) FROM consent_records cr WHERE cr.purpose = p.purpose AND cr.status = 'withdrawn' AND CAST(cr.occurred_at AS DATE) BETWEEN m.month_start AND m.month_end) AS withdrawals_recorded, (SELECT COUNT(*) FROM consent_records cr WHERE cr.purpose = p.purpose AND cr.status = 'expired' AND CAST(cr.occurred_at AS DATE) BETWEEN m.month_start AND m.month_end) AS expirations_recorded, (SELECT COUNT(DISTINCT cr.user_id) FROM consent_records cr WHERE cr.purpose = p.purpose AND CAST(cr.occurred_at AS DATE) <= m.month_end) AS users_with_any_record, (SELECT COUNT(*) FROM ( SELECT cr.user_id AS user_id, cr.status AS status, ROW_NUMBER() OVER (PARTITION BY cr.tenant_id, cr.user_id, cr.purpose ORDER BY cr.occurred_at DESC) AS rn FROM consent_records cr WHERE cr.purpose = p.purpose AND CAST(cr.occurred_at AS DATE) <= m.month_end ) ranked WHERE ranked.rn = 1 AND ranked.status = 'granted') AS net_active_optins FROM (SELECT DISTINCT purpose FROM consent_records) p CROSS JOIN ( SELECT month_start, month_label, MAX(calendar_date) AS month_end FROM calendar_days WHERE year_num BETWEEN 2023 AND 2025 GROUP BY month_start, month_label ) m ) metrics ORDER BY metrics.purpose, metrics.month_start
Submitted to tonight's batch. Your work on this request is done.