Queries · q115 · Data Protection Officer · week 1, night 2
“Which specific data subject requests are still open right now, how old are they, and which are overdue?”
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 2, request 1 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.
The engineer · 1 dry run on the empty schema, then the submission
WITH open_requests AS ( SELECT dsr.public_id, dsr.tenant_id, dsr.request_type, dsr.status, dsr.received_at, dsr.due_at, dsr.assigned_to_user_id FROM data_subject_requests dsr WHERE dsr.completed_at IS NULL ), enriched AS ( SELECT r.public_id AS request_public_id, t.name AS tenant_name, r.request_type, r.status, CAST(r.received_at AS date) AS received_date, CAST(r.due_at AS date) AS due_date, DATE '2026-01-05' - CAST(r.received_at AS date) AS days_since_received, CASE WHEN r.due_at IS NULL THEN NULL WHEN DATE '2026-01-05' < CAST(r.due_at AS date) THEN 0 ELSE DATE '2026-01-05' - CAST(r.due_at AS date) END AS days_overdue, COALESCE(u.full_name, 'unassigned') AS assigned_to FROM open_requests r JOIN tenants t ON t.id = r.tenant_id LEFT JOIN users u ON u.id = r.assigned_to_user_id ) SELECT request_public_id, tenant_name, request_type, status, received_date AS received_at, due_date AS due_at, days_since_received, days_overdue, assigned_to FROM enriched ORDER BY received_at ASC, request_public_id ASC
“Query compiles and returns the expected columns.”
“Submitted. The report lists every data subject request still open as of 2026-01-05 (completed_at IS NULL), one row per request, with tenant name, type, status, received/due dates, age in days, overdue days (NULL if no due date, 0 if due date hasn't passed yet), and the assigned agent (or 'unassigned'), ordered oldest-first by received_at.”
As Priyanka Desai saw it, from the small dataset the agents worked on
request_public_id | tenant_name | request_type | status | received_at | due_at | days_since_received | days_overdue | assigned_to -------------------------------------+----------------------+--------------+-------------+-------------+------------+---------------------+--------------+---------------- 0bd4ff3e-19c5-44d7-9288-04b73f44dccb | Marrowby & Vane | erasure | rejected | 2023-05-26 | 2023-06-25 | 955 | 925 | Emma Kim e5fcf055-b01f-479e-b995-34d0c5e3eb8c | Rue Lafayette Studio | erasure | rejected | 2024-07-09 | 2024-08-08 | 545 | 515 | Aurélie Richard 89c20516-5f50-4f0e-99c9-616fb24a9eaf | Cragsley Outfitters | erasure | rejected | 2025-03-21 | 2025-04-20 | 290 | 260 | William Martin 1ae91754-0856-42a6-947a-05725d6bc5e5 | Marrowby & Vane | erasure | received | 2025-12-06 | 2026-01-05 | 30 | 0 | Emma Kim 8a0c1a1b-3ad0-4720-be86-dcfaded836c7 | Studio Vrij | access | in_progress | 2025-12-29 | 2026-01-28 | 7 | 0 | Yara Woźniak (5 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
WITH open_requests AS ( SELECT dsr.public_id, dsr.tenant_id, dsr.request_type, dsr.status, dsr.received_at, dsr.due_at, dsr.assigned_to_user_id dsr.public_id AS public_id, dsr.tenant_id AS tenant_id, dsr.request_type AS request_type, dsr.status AS status, dsr.received_at AS received_at, dsr.due_at AS due_at, dsr.assigned_to_user_id AS assigned_to_user_id FROM data_subject_requests dsr WHERE dsr.completed_at IS NULL),enriched AS ( SELECT r.public_id AS request_public_id, t.name AS tenant_name, r.request_type, r.status, r.request_type AS request_type, r.status AS status, CAST(r.received_at AS date) AS received_date, CAST(r.due_at AS date) AS due_date, DATE '2026-01-05' - CAST(r.received_at AS date) AS days_since_received, CASE WHEN r.due_at IS NULL THEN NULL WHEN DATE '2026-01-05' < CAST(r.due_at AS date) THEN 0 ELSE DATE '2026-01-05' - CAST(r.due_at AS date) END AS days_overdue, COALESCE(u.full_name, 'unassigned') AS assigned_to FROM open_requests r JOIN tenants t ON t.id = r.tenant_id LEFT JOIN users u ON u.id = r.assigned_to_user_id)SELECT request_public_id, tenant_name, request_type, status, received_date AS received_at, due_date AS due_at, days_since_received, days_overdue, assigned_toFROM enrichedORDER BY received_at ASC, request_public_id ASCORDER BY received_at ASC NULLS LAST, request_public_id ASC NULLS LAST
WITH open_requests AS ( SELECT dsr.public_id AS public_id, dsr.tenant_id AS tenant_id, dsr.request_type AS request_type, dsr.status AS status, dsr.received_at AS received_at, dsr.due_at AS due_at, dsr.assigned_to_user_id AS assigned_to_user_id FROM data_subject_requests dsr WHERE dsr.completed_at IS NULL ), enriched AS ( SELECT r.public_id AS request_public_id, t.name AS tenant_name, r.request_type AS request_type, r.status AS status, CAST(r.received_at AS date) AS received_date, CAST(r.due_at AS date) AS due_date, DATE '2026-01-05' - CAST(r.received_at AS date) AS days_since_received, CASE WHEN r.due_at IS NULL THEN NULL WHEN DATE '2026-01-05' < CAST(r.due_at AS date) THEN 0 ELSE DATE '2026-01-05' - CAST(r.due_at AS date) END AS days_overdue, COALESCE(u.full_name, 'unassigned') AS assigned_to FROM open_requests r JOIN tenants t ON t.id = r.tenant_id LEFT JOIN users u ON u.id = r.assigned_to_user_id ) SELECT request_public_id, tenant_name, request_type, status, received_date AS received_at, due_date AS due_at, days_since_received, days_overdue, assigned_to FROM enriched ORDER BY received_at ASC NULLS LAST, request_public_id ASC 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: Currently open data subject requests: age and overdue detail Question: Which specific data subject requests are still open right now, how old are they, and which are overdue? Grain: one row per data_subject_requests record where completed_at is null as of 2026-01-05 Columns: - request public_id - tenant name - request type - status - received at (date) - due at (date, or blank if not set) - days since received (2026-01-05 minus received_at) - days overdue (2026-01-05 minus due_at, 0 or blank if due_at is null or in the future) - assigned to (users.full_name via assigned_to_user_id, 'unassigned' if null) Details: Source: data_subject_requests joined to tenants (tenant name) and users (assigned_to_user_id -> full_name). Filter: completed_at IS NULL. Use literal date 2026-01-05 for all age math, not CURRENT_DATE. This should return a small number of rows (expected around 5, matching the still_open_count totals from last night's monthly report: 2023-05, 2024-07, 2025-03, and two from 2025-12). Order by received_at ascending so the oldest, most at-risk requests print first. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH open_requests AS ( SELECT dsr.public_id, dsr.tenant_id, dsr.request_type, dsr.status, dsr.received_at, dsr.due_at, dsr.assigned_to_user_id FROM data_subject_requests dsr WHERE dsr.completed_at IS NULL ), enriched AS ( SELECT r.public_id AS request_public_id, t.name AS tenant_name, r.request_type, r.status, CAST(r.received_at AS date) AS received_date, CAST(r.due_at AS date) AS due_date, DATE '2026-01-05' - CAST(r.received_at AS date) AS days_since_received, CASE WHEN r.due_at IS NULL THEN NULL WHEN DATE '2026-01-05' < CAST(r.due_at AS date) THEN 0 ELSE DATE '2026-01-05' - CAST(r.due_at AS date) END AS days_overdue, COALESCE(u.full_name, 'unassigned') AS assigned_to FROM open_requests r JOIN tenants t ON t.id = r.tenant_id LEFT JOIN users u ON u.id = r.assigned_to_user_id ) SELECT request_public_id, tenant_name, request_type, status, received_date AS received_at, due_date AS due_at, days_since_received, days_overdue, assigned_to FROM enriched ORDER BY received_at ASC, request_public_id ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: request_public_id, tenant_name, request_type, status, received_at, due_at, days_since_received, days_overdue, assigned_to
submitWITH open_requests AS ( SELECT dsr.public_id, dsr.tenant_id, dsr.request_type, dsr.status, dsr.received_at, dsr.due_at, dsr.assigned_to_user_id FROM data_subject_requests dsr WHERE dsr.completed_at IS NULL ), enriched AS ( SELECT r.public_id AS request_public_id, t.name AS tenant_name, r.request_type, r.status, CAST(r.received_at AS date) AS received_date, CAST(r.due_at AS date) AS due_date, DATE '2026-01-05' - CAST(r.received_at AS date) AS days_since_received, CASE WHEN r.due_at IS NULL THEN NULL WHEN DATE '2026-01-05' < CAST(r.due_at AS date) THEN 0 ELSE DATE '2026-01-05' - CAST(r.due_at AS date) END AS days_overdue, COALESCE(u.full_name, 'unassigned') AS assigned_to FROM open_requests r JOIN tenants t ON t.id = r.tenant_id LEFT JOIN users u ON u.id = r.assigned_to_user_id ) SELECT request_public_id, tenant_name, request_type, status, received_date AS received_at, due_date AS due_at, days_since_received, days_overdue, assigned_to FROM enriched ORDER BY received_at ASC, request_public_id ASC
Submitted to tonight's batch. Your work on this request is done.