Compute violation rate and flag precision in SQL
Company: Google
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
You are analyzing a Trust & Safety product in BigQuery. Assume 'today' is 2025-09-01 (UTC). Define precise metrics and write SQL to compute them, being explicit about edge cases.
Schema (invented):
- users(user_id INT, country STRING, signup_time TIMESTAMP)
- events(user_id INT, event_time TIMESTAMP, event_type STRING) -- event_type in {'post','comment','message','login'}
- content(content_id INT, uploader_user_id INT, created_time TIMESTAMP, country STRING)
- moderation_decisions(content_id INT, decision STRING, decision_time TIMESTAMP) -- decision in {'violation','clean','needs_review'}; multiple rows per content over time possible
- auto_flags(content_id INT, flag_time TIMESTAMP, rule_id STRING)
Sample rows:
users
+----------+---------+---------------------+
| user_id | country | signup_time |
+----------+---------+---------------------+
| 1 | US | 2025-01-05 10:00:00 |
| 2 | IN | 2025-07-12 08:00:00 |
| 3 | US | 2025-08-20 12:00:00 |
| 4 | BR | 2025-06-01 09:30:00 |
+----------+---------+---------------------+
events
+----------+---------------------+-------------+
| user_id | event_time | event_type |
+----------+---------------------+-------------+
| 1 | 2025-08-29 02:00:00 | post |
| 1 | 2025-08-30 03:00:00 | login |
| 2 | 2025-08-28 11:00:00 | comment |
| 3 | 2025-08-27 16:00:00 | message |
| 4 | 2025-07-15 18:00:00 | post |
+----------+---------------------+-------------+
content
+------------+-------------------+---------------------+---------+
| content_id | uploader_user_id | created_time | country |
+------------+-------------------+---------------------+---------+
| 100 | 1 | 2025-08-28 05:00:00 | US |
| 101 | 2 | 2025-08-26 10:00:00 | IN |
| 102 | 3 | 2025-08-31 01:00:00 | US |
| 103 | 4 | 2025-07-01 06:00:00 | BR |
+------------+-------------------+---------------------+---------+
moderation_decisions
+------------+-----------+---------------------+
| content_id | decision | decision_time |
+------------+-----------+---------------------+
| 100 | violation | 2025-08-31 07:00:00 |
| 100 | clean | 2025-09-02 01:00:00 |
| 101 | clean | 2025-08-27 12:00:00 |
| 102 | violation | 2025-09-01 10:00:00 |
| 103 | violation | 2025-08-10 09:00:00 |
+------------+-----------+---------------------+
auto_flags
+------------+---------------------+----------+
| content_id | flag_time | rule_id |
+------------+---------------------+----------+
| 100 | 2025-08-31 06:30:00 | R1 |
| 101 | 2025-08-26 10:30:00 | R2 |
| 102 | 2025-09-01 09:00:00 | R1 |
| 102 | 2025-09-01 09:05:00 | R1 |
+------------+---------------------+----------+
Task A — 7-day Confirmed Violation Rate per 1,000 Active Users by country:
- Window: [2025-08-26 00:00:00, 2025-09-02 00:00:00) (the last 7 full days ending at 'today' inclusive via half-open interval).
- Active users: distinct users with ≥1 events where event_type IN ('post','comment','message') within the window.
- Country attribution: use users.country for the uploader_user_id; if NULL, fallback to content.country.
- Violations counted: distinct content_id with at least one decision='violation' whose decision_time falls within the window AND whose content.created_time < window_end (exclude content created after the window). If multiple decisions for the same content exist within the window, use the latest within-window decision only; if both 'clean' and 'violation' occur within the window, treat as violation if any within-window decision is 'violation'. Ignore decisions outside the window when determining within-window status.
- Output columns: country, window_start, window_end, active_users, violations, violation_rate_per_1000 (round to 2 decimals).
- Write a single BigQuery-standard SQL query to produce this.
Task B — 28-day Auto-Flag Precision by rule_id:
- Flag cohort: auto_flags where flag_time in [2025-08-05 00:00:00, 2025-09-02 00:00:00).
- Deduping: if multiple flags exist for the same content_id and rule_id within the cohort, keep only the earliest flag for that (content_id, rule_id) pair.
- Match window: a flag is a true positive if a moderation_decisions row with decision='violation' occurs within (flag_time, flag_time + 48 hours]. If multiple decisions occur in that 48h window, use the latest; 'needs_review' and 'clean' do not count as violations. Decisions after 48h do not count for precision.
- Output columns: rule_id, flags, matched_violations, precision = matched_violations / flags.
- Write a single BigQuery-standard SQL query to produce this.
Edge cases to handle explicitly in your SQL: time zones (assume all timestamps UTC), late-arriving decisions (outside windows are ignored), users with no country (coalesce with content.country), and zero-division (precision should be 0.0 if flags=0).
Overview: This question evaluates proficiency in analytical SQL and data engineering concepts—specifically time-windowed metric computation, user activity definitions, join and attribution logic, deduplication, and handling temporal moderation and auto-flag events in BigQuery.
Read the full Google Data Scientist interview experience this question came from
Assume every timestamp is UTC. For the half-open window [2025-08-26 00:00:00, 2025-09-02 00:00:00), compute each country's confirmed violations per 1,000 active users. An active user has at least one post, comment, or message event in the window. Attribute a user to users.country; when that is NULL, fall back to the country on content uploaded by that user (the fixture has one consistent fallback country per such user). Count a distinct content_id as a violation when the content was created before the window end and it has at least one violation decision inside the window; an in-window violation still wins if another in-window decision says clean, and all decisions outside the window are ignored. Return country, window_start, window_end, active_users, violations, and violation_rate_per_1000 rounded to two decimals. Include countries with active users and order the rows by country.
Tables
users(user_id INTEGER, country TEXT, signup_time TIMESTAMP)
events(user_id INTEGER, event_time TIMESTAMP, event_type TEXT)
content(content_id INTEGER, uploader_user_id INTEGER, created_time TIMESTAMP, country TEXT)
moderation_decisions(content_id INTEGER, decision TEXT, decision_time TIMESTAMP)
Hints
- Resolve one country per user before aggregating active users.
- Use EXISTS to count each violating content item once even when it has several decisions.
Community answers
Answer by Paramartha Sengupta
-- Write your SQL query here
with master_table as
(
SELECT a.user_id, a.country, b.event_time,b.event_type,c.content_id,
c.created_time, c.country as contentcountry,
d.decision, d.decision_time
FROM users as a
left join events as b on a.user_id=b.user_id
left join content as c on a.user_id=c.uploader_user_id
left join moderation_decisions as d on c.content_id=d.content_id
),
master_process as
(
select user_id, content_id, coalesce(country, contentcountry) as country,
'2025-08-26 00:00:00' as window_start,
'2025-09-02 00:00:00' as window_end,
max(case when event_type in ('post','comment','message')
and event_time between '2025-08-26' and '2026-09-02'then 1
else 0 end) as active_users,
max(case when decision in ('violation')
and created_time<'2025-09-02'
and decision_time between '2025-08-26' and '2026-09-02'then 1
else 0 end) as violations
from master_table
group by user_id,content_id,coalesce(country,contentcountry) , window_start, window_end
),
processed_table as
(
select country, window_start,window_end,
count(distinct (case when active_users=1 then user_id end)) as active_users,
count(distinct (case when violations=1 then content_id end)) as violations
from master_process
group by country, window_start,window_end
)
select , round((violations1000/active_users),2) as violation_rate_per_1000
from processed_table
where active_users>0
order by country