Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Apr 2026

Summary

Meta data scientist interview with a SQL question focused on content safety metrics. Pretty straightforward setup but the specific angle they were going for took me a second to figure out.

Questions Asked (1)

Given a table of videos with a harmful content flag and a table of views with timestamps, write a SQL query that calculates the daily percentage of views that were on harmful videos.

Product Analytics & MetricsData Modeling

Author's notes

I knew I needed a join and a group by date, but I fumbled the aggregation syntax for a minute.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the table schemas and the definition of 'harmful' (e.g., flag = 1). Then, join the views and videos tables on video_id, group by date, and compute the percentage as the sum of views on harmful videos divided by total views, multiplied by 100. Use a LEFT JOIN to ensure all views are counted, and handle NULLs appropriately.

Pro tip: Always clarify edge cases: What if a video has no flag? Should it be considered non-harmful? Also, consider time zones for daily aggregation—Meta often uses UTC. Mentioning these shows attention to detail.

1. Clarify requirements and schema

Ask about the table structures: views (view_id, video_id, timestamp) and videos (video_id, harmful_flag). Confirm the definition of 'harmful' and how to handle missing flags.

2. Join views with video flags

Use a LEFT JOIN to combine views with video metadata, ensuring all views are included. Treat NULL harmful_flag as non-harmful (0).

3. Aggregate daily views

Group by date (using DATE(timestamp)) and calculate total views and harmful views (SUM(CASE WHEN harmful_flag = 1 THEN 1 ELSE 0 END)).

4. Compute percentage

Calculate the percentage as (harmful_views * 100.0 / total_views), ensuring floating-point division to avoid integer truncation.

5. Handle edge cases and optimize

Consider days with zero views (avoid division by zero), and if needed, filter out days with no views. Also, discuss indexing on date and video_id for performance.

Key Points to Mention

Use LEFT JOIN to include all views, even those without a matching video record.
Use CASE WHEN to count harmful views, and SUM to aggregate.
Group by DATE(timestamp) to get daily metrics.
Multiply by 100.0 to get percentage and avoid integer division.
Consider time zone conversion if timestamps are not in UTC.
Handle NULLs in harmful_flag by treating them as 0 (non-harmful).

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