Quick Overview

This question evaluates a data scientist's proficiency in SQL-based data manipulation, including joins, distinct counting and grouping, aggregations, string concatenation for user identity, and NULL handling across event and review tables.

Analyze User Flags and Review Outcomes for Moderation Prioritization

Company: Google

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

UserFlags +---------------+--------------+----------+---------+ | User_FirstName| User_LastName| Video_ID | Flag_ID | +---------------+--------------+----------+---------+ | Alice | Zhang | v1 | f101 | | Bob | Singh | v1 | f102 | | Alice | Zhang | v2 | f103 | | Carol | Lee | v3 | f104 | | Bob | Singh | v3 | f105 | +---------------+--------------+----------+---------+ ​ FlagReviews +----------+---------+--------------+-----------------+ | Video_ID | Flag_ID | Reviewed_date| Reviewed_outcome| +----------+---------+--------------+-----------------+ | v1 | f101 | 2023-01-02 | APPROVED | | v1 | f102 | 2023-01-03 | REJECTED | | v2 | f103 | 2023-01-05 | APPROVED | | v3 | f104 | 2023-01-04 | APPROVED | | v3 | f105 | NULL | NULL | +----------+---------+--------------+-----------------+ ##### Scenario YouTube Trust & Safety team wants to analyze user-generated video flags and their review outcomes to prioritize moderation resources. ##### Question Q1. Given table UserFlags(User_FirstName, User_LastName, Video_ID, Flag_ID), write a SQL query that returns, for every Video_ID, the number of flags submitted by distinct users. Q2. Using UserFlags and FlagReviews(Video_ID, Flag_ID, Reviewed_date, Reviewed_outcome), find the count of flags reviewed by YouTube for the single video that received the highest total number of user flags. Q3. Combining both tables, determine which user (concatenate first and last name) flagged the greatest number of videos that were ultimately APPROVED by YouTube. Q4. Write a query that lists every row from any provided table where at least one column contains NULL. ##### Hints Think joins, distinct counts, grouping, and IS NULL filters. For Q3, count unique Video_IDs per user where Reviewed_outcome = 'APPROVED'.

Overview: This question evaluates a data scientist's proficiency in SQL-based data manipulation, including joins, distinct counting and grouping, aggregations, string concatenation for user identity, and NULL handling across event and review tables.

Distinct user flags per video

For every Video_ID, return the number of flags submitted by distinct users (distinct first and last name together).

Tables

UserFlags(User_FirstName VARCHAR, User_LastName VARCHAR, Video_ID VARCHAR, Flag_ID VARCHAR)

FlagReviews(Video_ID VARCHAR, Flag_ID VARCHAR, Reviewed_date DATE, Reviewed_outcome VARCHAR)

Hints

  1. Count distinct (Video_ID, User_FirstName, User_LastName) combinations.
  2. Use a subquery or COUNT(DISTINCT ...) depending on your SQL dialect.

Reviewed Flags for the Top Video

Using `UserFlags` and `FlagReviews`, find the single video with the highest total number of user flags, breaking ties by the smallest `Video_ID`. Return that `Video_ID` and the count of its flags that were reviewed by YouTube, where reviewed means both `Reviewed_date` and `Reviewed_outcome` are non-NULL.

Tables

UserFlags(User_FirstName VARCHAR, User_LastName VARCHAR, Video_ID VARCHAR, Flag_ID VARCHAR)

FlagReviews(Video_ID VARCHAR, Flag_ID VARCHAR, Reviewed_date DATE, Reviewed_outcome VARCHAR)

Hints

  1. Count flags per `Video_ID` in `UserFlags` first.
  2. Break ties by `Video_ID` ascending.

Top User by Approved Videos Flagged

Find the user who flagged the greatest number of distinct videos that were ultimately `APPROVED` in `FlagReviews`. Concatenate first and last name as `user_full_name`, return `approved_videos_flagged`, and break ties alphabetically by the full name.

Tables

UserFlags(User_FirstName VARCHAR, User_LastName VARCHAR, Video_ID VARCHAR, Flag_ID VARCHAR)

FlagReviews(Video_ID VARCHAR, Flag_ID VARCHAR, Reviewed_date DATE, Reviewed_outcome VARCHAR)

Hints

  1. Join `UserFlags` to `FlagReviews` on both `Video_ID` and `Flag_ID`.
  2. Filter to `Reviewed_outcome = 'APPROVED'`.

Rows with NULLs per table

List every row from each provided table where at least one column contains NULL. You may write separate queries per table.

Tables

UserFlags(User_FirstName VARCHAR, User_LastName VARCHAR, Video_ID VARCHAR, Flag_ID VARCHAR)

FlagReviews(Video_ID VARCHAR, Flag_ID VARCHAR, Reviewed_date DATE, Reviewed_outcome VARCHAR)

Hints

  1. Use IS NULL checks on each column.
  2. Run separate queries per table if schemas differ.

Loading coding console...