Analyze Hashtag Follow Behavior with SQL Queries

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'?

Hashtag source followers and violating percentage

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.

Table following_behavior

column	type	constraints
date	DATE	NOT NULL
user_id	INTEGER	NOT NULL
hashtag_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES hashtag(hashtag_id)
hashtag_source	VARCHAR	NOT NULL

Sample data:

date	hashtag_id	hashtag_source	user_id
2025-06-01	9001	feed	101
2025-06-01	9002	hashtag page	102
2025-06-01	9001	feed	103
2025-06-01	9003	hashtag page	104
2025-06-01	9002	feed	105

Table hashtag

column	type	constraints
hashtag_id	INTEGER	PRIMARY KEY, NOT NULL
hashtag_safety	VARCHAR	NOT NULL

Sample data:

hashtag_id	hashtag_safety
9001	safety
9002	violating
9003	safety
9004	violating

Expected output

For the date '2025-06-01', return two rows: (1) the hashtag_source with the highest number of follows and its follower_count; (2) the percentage of follows from 'hashtag page' that are for hashtags labeled 'violating', expressed as a percentage rounded to two decimals.

follower_count	hashtag_source	metric	percentage
3	feed	top_source_today	NULL
NULL	hashtag page	hashtag_page_violating_pct	50

Hints

1. Filter records to the specific date '2025-06-01' and group by hashtag_source to count followers per source.
2. 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(*).