Google·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Apr 2026

Summary

SQL-heavy technical screen for a Data Scientist role at Google, focused entirely on Trust & Safety query design. Four problems, all written SQL, all around a flags-and-reviews schema. No behavioral stuff, just grinding through joins and edge cases.

Questions Asked (4)

Given a schema with users, videos, and flags, write a SQL query that returns each video's count of distinct users who flagged it (counting a user only once per video regardless of how many times they flagged it), plus the total flag row count. Order by distinct flaggers descending, then total flags, then video ID.

Product Analytics & MetricsData Modeling

Author's notes

This one looks easy and then you realize you need two different counts in the same SELECT.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and the exact metrics: distinct flaggers per video and total flag rows. Then write a query that groups by video, uses COUNT(DISTINCT user_id) for distinct flaggers and COUNT(*) for total flags, and orders by the two counts descending, then video_id ascending. Finally, consider edge cases like videos with no flags and performance implications.

Pro tip: Mention that COUNT(DISTINCT) can be expensive on large datasets and suggest alternatives like using a subquery with GROUP BY user_id, video_id or approximate functions if exact counts aren't required. Also, clarify whether videos with zero flags should be included; if so, use a LEFT JOIN from videos to flags.

1. Clarify requirements and schema

Confirm the table structures, especially the flags table columns (user_id, video_id) and whether videos with no flags should appear. Ask about the expected output format and any constraints.

2. Identify aggregation logic

Determine that you need to group by video_id and compute two aggregates: COUNT(DISTINCT user_id) for distinct flaggers and COUNT(*) for total flags. Consider if any filters (e.g., active flags) apply.

3. Write the core query

Construct a SELECT statement with GROUP BY video_id, using COUNT(DISTINCT user_id) AS distinct_flaggers and COUNT(*) AS total_flags. If including videos with no flags, use a LEFT JOIN from videos to flags.

4. Apply ordering and handle edge cases

Add ORDER BY distinct_flaggers DESC, total_flags DESC, video_id ASC. Check for NULLs and ensure the query returns expected results for videos with zero flags (if included).

5. Optimize and validate

Discuss potential performance improvements (e.g., indexing on video_id, user_id) and validate the query with sample data or by explaining the output.

Key Points to Mention

Use of COUNT(DISTINCT user_id) to count each user only once per video.
Use of COUNT(*) to get the total number of flag rows.
Grouping by video_id to aggregate per video.
Ordering by distinct flaggers descending, then total flags descending, then video_id ascending.
Handling videos with no flags: LEFT JOIN vs. INNER JOIN and the impact on results.
Performance considerations: COUNT(DISTINCT) can be resource-intensive; alternatives like subqueries or approximate counts.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Find the video(s) with the highest total number of flags, and for those videos return the total flag count alongside a count of flags that have actually been reviewed (i.e., have a non-NULL reviewed_outcome). Handle ties.

Data ModelingProduct Analytics & Metrics

Author's notes

The tie-handling part is what makes this annoying.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by aggregating flag counts per video, then identify the maximum total flag count and filter videos that match it. For those videos, compute the count of flags with non-NULL reviewed_outcome, ensuring ties are handled by returning all top videos.

Pro tip: Clarify whether 'total number of flags' includes all flags or only distinct flags per user; also consider if videos with zero flags should be excluded. Explicitly state your assumptions to show analytical rigor.

1. Aggregate flag counts per video

Group the flags table by video_id and count the total number of flags for each video. This gives the total flag count per video.

2. Identify the maximum total flag count

Find the highest total flag count across all videos. This may involve a subquery or window function to compute the max.

3. Filter videos with the maximum count

Select all videos whose total flag count equals the maximum. This handles ties by returning multiple videos if they share the highest count.

4. Count reviewed flags for those videos

For the selected videos, count the number of flags where reviewed_outcome IS NOT NULL. This gives the reviewed flag count per video.

5. Return video_id, total_flag_count, reviewed_flag_count

Output the final result with the video identifier, total flags, and reviewed flags for each top video, ensuring ties are included.

Key Points to Mention

Use of GROUP BY and COUNT for aggregation
Handling ties by using a subquery or window function to find max and filter
Conditional counting with CASE WHEN or FILTER for reviewed flags
Assumption about NULL reviewed_outcome meaning not reviewed
Consideration of videos with zero flags (exclude or include?)
Efficiency: avoid multiple scans by using CTEs or subqueries

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Which user flagged the most distinct videos that ended up getting an APPROVED review outcome? Count each user-video pair once even if the user flagged that video multiple times. Return all tied users if there's a tie.

Data ModelingProduct Analytics & Metrics

Author's notes

Three layers of deduplication here.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, identify the relevant tables: one for flags (user_id, video_id, flag_timestamp) and one for review outcomes (video_id, review_outcome). Join these tables on video_id, filter for review_outcome = 'APPROVED', and deduplicate user-video pairs using DISTINCT or GROUP BY. Then, count distinct video_id per user_id, rank users by this count, and return all users with the maximum count.

Pro tip: Clarify whether 'distinct videos' means unique video IDs regardless of multiple flags, and confirm that 'APPROVED' is the exact outcome value. Also, consider if there's a time window or if flags after approval should be excluded.

1. Understand the data model

Identify the tables containing flag events and review outcomes. Determine the join key (likely video_id) and the fields needed: user_id, video_id, review_outcome.

2. Filter and deduplicate

Filter review outcomes to only 'APPROVED'. Then, deduplicate user-video pairs by selecting distinct combinations of user_id and video_id from the flags table that join to approved videos.

3. Aggregate and rank

Count the number of distinct videos per user. Rank users by this count in descending order and identify the maximum count.

4. Handle ties

Return all users whose count equals the maximum count. Use a window function like RANK() or DENSE_RANK() to handle ties, or simply filter where count = (SELECT MAX(count) ...).

Key Points to Mention

Use DISTINCT or GROUP BY to ensure each user-video pair is counted once, even if flagged multiple times.
Join flags and review outcomes on video_id, and filter for review_outcome = 'APPROVED'.
Count distinct video_id per user_id to get the number of distinct approved videos flagged by each user.
Handle ties by returning all users with the maximum count, using window functions or a subquery.
Consider data quality issues: nulls, duplicate flags, and whether review outcomes can change over time.
Optimize for performance by filtering before joining if possible, and using appropriate indexes.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Write two separate queries: one that returns all rows from the Reviews table where any column contains a NULL, and one that returns all rows from the Flags table where any non-primary-key column (user_id, video_id, or flagged_at) is NULL.

Data Modeling

Author's notes

Easiest one of the four.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

For the Reviews table, use a WHERE clause with OR conditions checking each column IS NULL. For the Flags table, explicitly list the non-primary-key columns (user_id, video_id, flagged_at) in OR conditions, excluding the primary key. Write both queries separately and consider performance implications.

Pro tip: Mention that while SELECT * is fine for ad-hoc analysis, in production you'd specify columns and consider indexing or using COALESCE for better performance. Also, clarify that 'any column' means any column in the table schema, not just those in the result set.

1. Identify table schemas

Determine the columns in the Reviews table and the primary key of the Flags table. For Flags, the non-primary-key columns are given as user_id, video_id, and flagged_at.

2. Construct Reviews query

Write a SELECT statement with a WHERE clause that ORs IS NULL checks for every column in the Reviews table.

3. Construct Flags query

Write a SELECT statement with a WHERE clause that ORs IS NULL checks for user_id, video_id, and flagged_at, explicitly excluding the primary key column.

4. Validate and optimize

Double-check column names and consider if any columns are known to be NOT NULL. Discuss potential performance improvements like using indexes or avoiding OR conditions if possible.

Key Points to Mention

Use of IS NULL (not = NULL) in SQL
OR conditions to check multiple columns
Explicitly listing columns vs. using dynamic SQL or information_schema
Exclusion of primary key in Flags table
Performance considerations: OR conditions can be slow, indexes on nullable columns
Clarify assumptions about table schemas if not provided

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.