Write SQL for hashtag source and safety rates

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).

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).

Table following_behavior

column	type	constraints
date	DATE	NOT NULL
user_id	INT	NOT NULL
hashtag_id	INT	NOT NULL
source	VARCHAR(50)	NOT NULL

Sample data:

date	hashtag_id	source	user_id
2025-06-01	100	hashtag page	1
2025-06-01	100	hashtag page	1
2025-06-01	101	feed	1
2025-06-01	100	feed	2
2025-06-01	102	hashtag page	2
2025-06-01	103	hashtag page	3
2025-05-31	100	hashtag page	4
2025-06-01	104	feed	5
2025-06-01	105	hashtag page	6
2025-06-01	105	feed	6
2025-06-01	106	hashtag page	7
2025-06-01	107	feed	8
2025-06-01	108	hashtag page	9
2025-06-01	109	feed	10
2025-06-01	110	hashtag page	11
2025-06-01	999	hashtag page	12

Table hashtag

column	type	constraints
hashtag_id	INT	PRIMARY KEY, NOT NULL
safety	VARCHAR(20)	NOT NULL

Sample data:

hashtag_id	safety
100	safety
101	violating
102	safety
103	violating
104	safety
105	safety
106	violating
107	safety
108	violating
109	safety
110	safety

Expected output

One row per source showing the number of deduplicated follows on 2025-06-01 and the rank of the source by that count (1 = most follows).

follows_today	rank	source
8	1	hashtag page
6	2	feed

Hints

1. Put a SELECT DISTINCT over (date, user_id, hashtag_id, source) in a CTE to deduplicate events before counting.
2. 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.

Table following_behavior

column	type	constraints
date	DATE	NOT NULL
user_id	INT	NOT NULL
hashtag_id	INT	NOT NULL
source	VARCHAR(50)	NOT NULL

Sample data:

date	hashtag_id	source	user_id
2025-06-01	100	hashtag page	1
2025-06-01	100	hashtag page	1
2025-06-01	101	feed	1
2025-06-01	100	feed	2
2025-06-01	102	hashtag page	2
2025-06-01	103	hashtag page	3
2025-05-31	100	hashtag page	4
2025-06-01	104	feed	5
2025-06-01	105	hashtag page	6
2025-06-01	105	feed	6
2025-06-01	106	hashtag page	7
2025-06-01	107	feed	8
2025-06-01	108	hashtag page	9
2025-06-01	109	feed	10
2025-06-01	110	hashtag page	11
2025-06-01	999	hashtag page	12

Table hashtag

column	type	constraints
hashtag_id	INT	PRIMARY KEY, NOT NULL
safety	VARCHAR(20)	NOT NULL

Sample data:

hashtag_id	safety
100	safety
101	violating
102	safety
103	violating
104	safety
105	safety
106	violating
107	safety
108	violating
109	safety
110	safety

Expected output

A single row with the percentage (0–100) of deduplicated follows on 2025-06-01 from source = 'hashtag page' that are on violating hashtags, excluding follows whose hashtag_id is missing from the hashtag table.

pct_violating
42.86

Hints

1. Deduplicate in a CTE, then filter to date = '2025-06-01' and source = 'hashtag page' before joining.
2. 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.