Google·Data Scientist·Technical Phone Screen
- You're given a Trust & Safety schema in BigQuery with users, events, content, and moderation decisions. Define a 7-day confirmed violation rate per 1,000 active users, broken down by country, and write a single BigQuery SQL query to compute it. Be explicit about how you handle country attribution, which decisions count, late-arriving decisions, and the exact time window boundaries.
- Using the same schema, compute 28-day auto-flag precision by rule_id. Deduplicate flags to the earliest per (content_id, rule_id) pair, then define a true positive as a violation decision that lands within 48 hours after the flag time. Write a single BigQuery SQL query.
“The country attribution piece is where I nearly tripped.”