Queries · q118 · Data Protection Officer · week 1, night 3

Data subject request status audit: does completed_at reflect true closure?

“Across all data subject requests (not just currently open ones), which status values leave completed_at null, and is that a systemic pattern (e.g. all rejections) or isolated to a few records?”

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

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 3, request 1 of 3
Report 1's big finding: all 3 of the long-overdue DSARs are status='rejected' yet have completed_at = NULL — my original monthly report (night 1) treated 'completed_at IS NULL' as the sole "still open" signal, which may be wrongly counting rejected (i.e., actually-decided) requests as backlog. Before I hand Ines a number, I need to know whether 'rejected' is a systemic gap (completed_at never set on rejections) or an isolated failure on these 3 — that reshapes the whole answer to her question 1, so it's the top priority tonight. Report 3 (retention policy inventory) was dominated by one audit_log policy repeated per tenant and got truncated at 50 of 720 rows before showing any other entity_table — I need a properly aggregated view of what policies actually exist before I can check reality against them (item 4). With only this night and one more left, I'm also opening item 5 (legal holds) tonight since it looks like a single, self-contained report I can likely close in one shot, leaving night 4 free for the retention actual-vs-policy comparison and any remaining loose ends.
Data subject request status audit: does completed_at reflect true closure?
Question
Across all data subject requests (not just currently open ones), which status values leave completed_at null, and is that a systemic pattern (e.g. all rejections) or isolated to a few records?
One row per
distinct data_subject_requests.status value, across full history
Columns
- status
- total requests with this status
- count with completed_at not null
- count with completed_at null
- oldest received_at in this status
- newest received_at in this status
- avg age in days of the completed_at-null rows (2026-01-05 minus received_at, averaged, null if none)
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
  status,
  count(*) as total_requests,
  count(completed_at) as completed_count,
  count(*) - count(completed_at) as not_completed_count,
  min(received_at) as oldest_received_at,
  max(received_at) as newest_received_at,
  avg(case when completed_at is null then date(DATE '2026-01-05') - date(received_at) end) as avg_age_days_not_completed
from data_subject_requests
group by status
order by total_requests desc, status asc
```
“Query compiles. 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 groups all data_subject_requests by status and reports total count, completed vs. not-completed counts, the oldest/newest received_at, and the average age (in days, relative to 2026-01-05) of the not-completed rows — which will directly show whether statuses like rejected systematically leave completed_at null or whether it's just a few stragglers.”

The report comes back overnight

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

status      | total_requests | completed_count | not_completed_count | oldest_received_at         | newest_received_at         | avg_age_days_not_completed
------------+----------------+-----------------+---------------------+----------------------------+----------------------------+---------------------------
completed   |             55 |              55 |                   0 | 2020-04-24 22:06:57.286184 | 2025-10-18 12:53:44.859874 |                       NULL
rejected    |              3 |               0 |                   3 | 2023-05-26 18:29:11.494494 | 2025-03-21 06:44:26.231555 |          596.6666666666666
in_progress |              1 |               0 |                   1 | 2025-12-29 04:46:25.41114  | 2025-12-29 04:46:25.41114  |                        7.0
received    |              1 |               0 |                   1 | 2025-12-06 01:50:30.669686 | 2025-12-06 01:50:30.669686 |                       30.0
(4 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:
- q121Linking rejected erasure requests to active legal holds
- q323Privacy request volume reconciliation: counts by type and status
It builds on:
- q115Currently open data subject requests: age and overdue detail

It becomes benchmark query q118

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

spellingdate cast (2×)
numericsdocs/NUMERICS.md: AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
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 (2×)

The benchmark text, against the submitted SQL

select  status,  count(*) as total_requests,  count(completed_at) as completed_count,  count(*) - count(completed_at) as not_completed_count,  min(received_at) as oldest_received_at,  max(received_at) as newest_received_at,  avg(case when completed_at is null then date(DATE '2026-01-05') - date(received_at) end) as avg_age_days_not_completed  avg(cast(case when completed_at is null then {{date_diff_days(received_at, DATE '2026-01-05')}} end as numeric(18,6))) as avg_age_days_not_completedfrom data_subject_requestsgroup by statusorder by total_requests desc, status ascorder by total_requests desc NULLS FIRST, status asc NULLS LAST

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

select
  status,
  count(*) as total_requests,
  count(completed_at) as completed_count,
  count(*) - count(completed_at) as not_completed_count,
  min(received_at) as oldest_received_at,
  max(received_at) as newest_received_at,
  avg(cast(case when completed_at is null then DATE_DIFF('day', received_at, DATE '2026-01-05') end as numeric(18,6))) as avg_age_days_not_completed
from data_subject_requests
group by status
order by total_requests desc NULLS FIRST, status asc NULLS LAST

select
  status,
  count(*) as total_requests,
  count(completed_at) as completed_count,
  count(*) - count(completed_at) as not_completed_count,
  min(received_at) as oldest_received_at,
  max(received_at) as newest_received_at,
  avg(cast(case when completed_at is null then (CAST(DATE '2026-01-05' AS DATE) - CAST(received_at AS DATE)) end as numeric(18,6))) as avg_age_days_not_completed
from data_subject_requests
group by status
order by total_requests desc NULLS FIRST, status asc NULLS LAST

select
  status,
  count(*) as total_requests,
  count(completed_at) as completed_count,
  count(*) - count(completed_at) as not_completed_count,
  min(received_at) as oldest_received_at,
  max(received_at) as newest_received_at,
  avg(cast(case when completed_at is null then dateDiff('day', received_at, DATE '2026-01-05') end as numeric(18,6))) as avg_age_days_not_completed
from data_subject_requests
group by status
order by total_requests desc NULLS FIRST, status asc NULLS LAST

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

The request, as Felix received it

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

Title: Data subject request status audit: does completed_at reflect true closure?
Question: Across all data subject requests (not just currently open ones), which status values leave completed_at null, and is that a systemic pattern (e.g. all rejections) or isolated to a few records?
Grain: one row per distinct data_subject_requests.status value, across full history
Columns:
- status
- total requests with this status
- count with completed_at not null
- count with completed_at null
- oldest received_at in this status
- newest received_at in this status
- avg age in days of the completed_at-null rows (2026-01-05 minus received_at, averaged, null if none)
Details: Source: data_subject_requests, no date filter (full history), platform-wide. Group by status. This is a diagnostic to check whether 'rejected' (and any other terminal-sounding status) systematically never gets a completed_at stamp, which would mean my earlier 'still open'/'overdue' counts (based on completed_at IS NULL) overstate the real backlog. Use literal date 2026-01-05 for age math. Order by total requests with this status descending.

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

Felix calls dry_run

select
  status,
  count(*) as total_requests,
  count(completed_at) as completed_count,
  count(*) - count(completed_at) as not_completed_count,
  min(received_at) as oldest_received_at,
  max(received_at) as newest_received_at,
  avg(case when completed_at is null then date(DATE '2026-01-05') - date(received_at) end) as avg_age_days_not_completed
from data_subject_requests
group by status
order by total_requests desc, status asc

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: status, total_requests, completed_count, not_completed_count, oldest_received_at, newest_received_at, avg_age_days_not_completed

FelixQuery compiles. Submitting.

Felix calls submit

select
  status,
  count(*) as total_requests,
  count(completed_at) as completed_count,
  count(*) - count(completed_at) as not_completed_count,
  min(received_at) as oldest_received_at,
  max(received_at) as newest_received_at,
  avg(case when completed_at is null then date(DATE '2026-01-05') - date(received_at) end) as avg_age_days_not_completed
from data_subject_requests
group by status
order by total_requests desc, status asc

The warehouse answers

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

FelixSubmitted. The report groups all `data_subject_requests` by `status` and reports total count, completed vs. not-completed counts, the oldest/newest `received_at`, and the average age (in days, relative to 2026-01-05) of the not-completed rows — which will directly show whether statuses like `rejected` systematically leave `completed_at` null or whether it's just a few stragglers.

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 (257 KB) · Priyanka Desai's transcript (894 KB) · Ines Laurent's transcript (263 KB) · the night's plan (4 KB) · the night's reports (4 KB) · the week's memo (3 KB)

Data subject request status audit: does completed_at reflect true closure?

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 q118

Everything, as recorded