Analyze Hashtag Follow Behavior with SQL Queries
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
following_behavior
+------------+---------+-----------+---------------+
| date | user_id | hashtag_id| hashtag_source|
+------------+---------+-----------+---------------+
|2023-08-12 | 101 | 9001 | feed |
|2023-08-12 | 102 | 9002 | hashtag page |
|2023-08-12 | 103 | 9001 | feed |
|2023-08-12 | 104 | 9003 | hashtag page |
|2023-08-12 | 105 | 9002 | feed |
hashtag
+-----------+----------------+
| hashtag_id| hashtag_safety |
+-----------+----------------+
| 9001 | safety |
| 9002 | violating |
| 9003 | safety |
| 9004 | violating |
##### Scenario
Analyzing hashtag follow behaviors using SQL
##### Question
Which hashtag_source gained the most followers today? What percentage of hashtag followers coming from the 'hashtag page' follow hashtags that are marked as 'violating'?
##### Hints
Overview: This question evaluates a candidate's competency in data manipulation and analytical SQL techniques—specifically aggregation, filtering, joins, and calculation of proportions—to analyze hashtag follow behavior.
Using the tables below, write a SQL query that, for the date '2025-06-01':
1) Finds the hashtag_source that gained the most followers and the corresponding follower_count.
2) Computes the percentage of hashtag follows coming from the 'hashtag page' that are for hashtags marked as 'violating'.
Return these as two rows in a single result set with the columns: metric, hashtag_source, follower_count, percentage. The first row should have metric = 'top_source_today' for the top source and its follower_count (percentage should be NULL). The second row should have metric = 'hashtag_page_violating_pct' for the 'hashtag page' and the percentage of follows that are violating (follower_count should be NULL), rounded to two decimal places.
Tables
following_behavior(date DATE, user_id INTEGER, hashtag_id INTEGER, hashtag_source VARCHAR)
hashtag(hashtag_id INTEGER, hashtag_safety VARCHAR)
Hints
- Filter records to the specific date '2025-06-01' and group by hashtag_source to count followers per source.
- Join following_behavior to hashtag for rows from 'hashtag page' on '2025-06-01' and compute the percentage of follows to violating hashtags using SUM(CASE WHEN ...) / COUNT(*).