Write SQL for hashtag source and safety rates
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Write SQL for the two tasks below. Assume the schema and sample data as given, and that “today” is 2025‑09‑01. Deduplicate exact duplicates by (date, user_id, hashtag_id, source). If a hashtag_id is missing from the hashtag table, exclude it only from task (2); include it in task (1). Use UTC dates. Schema:
Table: following_behavior(date DATE, user_id INT, hashtag_id INT, source VARCHAR) -- each row is a follow event
Table: hashtag(hashtag_id INT, safety VARCHAR) -- safety in {'safety','violating'}
Sample rows — following_behavior:
2025-09-01 | 1 | 100 | hashtag page
2025-09-01 | 1 | 100 | hashtag page -- duplicate
2025-09-01 | 1 | 101 | feed
2025-09-01 | 2 | 100 | feed
2025-09-01 | 2 | 102 | hashtag page
2025-09-01 | 3 | 103 | hashtag page
2025-08-31 | 4 | 100 | hashtag page -- not today
2025-09-01 | 5 | 104 | feed
2025-09-01 | 6 | 105 | hashtag page
2025-09-01 | 6 | 105 | feed
2025-09-01 | 7 | 106 | hashtag page
2025-09-01 | 8 | 107 | feed
2025-09-01 | 9 | 108 | hashtag page
2025-09-01 | 10 | 109 | feed
2025-09-01 | 11 | 110 | hashtag page
2025-09-01 | 12 | 999 | hashtag page -- hashtag 999 missing from hashtag table
Sample rows — hashtag:
100 | safety
101 | violating
102 | safety
103 | violating
104 | safety
105 | safety
106 | violating
107 | safety
108 | violating
109 | safety
110 | safety
Tasks:
(1) Which source ('hashtag page' vs 'feed') has the most follows today? Return: source, follows_today, and rank (1=most). Break ties by alphabetical source.
(2) What percent of today’s follows from source='hashtag page' are on violating hashtags? Return a single row with pct_violating (0–100 with two decimals).
Overview: This question evaluates SQL data-manipulation competencies such as deduplication, date filtering, aggregations and ranking, handling missing foreign-key references, and calculating percentages from event and reference tables.
Rank follow sources by number of follows on a specific date
Using the tables below, write a SQL query to find which source ('hashtag page' vs 'feed') has the most follows on the UTC date 2025-06-01 (treated as "today").
Rules:
- First, deduplicate exact duplicate follow events: rows are considered duplicates if all of (date, user_id, hashtag_id, source) are identical. Count each such event only once.
- Include all follows regardless of whether the hashtag_id exists in the hashtag table (do NOT join to the hashtag table for this task).
Return the following columns:
- source
- follows_today: the number of deduplicated follows from that source on 2025-06-01
- rank: 1 for the source with the most follows, 2 for the next, etc. If there is a tie in follows_today, the source that is alphabetically earlier should get the better (smaller) rank.
Use only the rows from date = '2025-06-01' (UTC).
Tables
following_behavior(date DATE, user_id INT, hashtag_id INT, source VARCHAR(50))
hashtag(hashtag_id INT, safety VARCHAR(20))
Hints
- Put a SELECT DISTINCT over (date, user_id, hashtag_id, source) in a CTE to deduplicate events before counting.
- Use a window function like RANK() ordered by follows_today DESC and source ASC to assign ranks with alphabetical tie-breaking.
Percent of violating hashtag follows from the hashtag page source
Using the same tables, write a SQL query to compute what percentage of today's follows from source = 'hashtag page' are on violating hashtags, for the UTC date 2025-06-01 (treated as "today").
Rules:
- First, deduplicate exact duplicate follow events: rows are considered duplicates if all of (date, user_id, hashtag_id, source) are identical. Count each such event only once.
- Restrict to deduplicated rows where date = '2025-06-01' and source = 'hashtag page'.
- Join to the hashtag table to get the safety label.
- If a follow's hashtag_id does not exist in the hashtag table, EXCLUDE that follow entirely from both the numerator and denominator.
Return a single row with:
- pct_violating: the percentage (0–100) of these follows whose hashtag has safety = 'violating', rounded to two decimal places.
Tables
following_behavior(date DATE, user_id INT, hashtag_id INT, source VARCHAR(50))
hashtag(hashtag_id INT, safety VARCHAR(20))
Hints
- Deduplicate in a CTE, then filter to date = '2025-06-01' and source = 'hashtag page' before joining.
- Use an INNER JOIN to the hashtag table so that missing hashtag_ids are excluded, and compute the percentage with SUM(CASE ...) over COUNT(*), then ROUND to two decimals.