Coding questions · TikTok

Compute and rank top bad advertisers

This question evaluates proficiency in SQL data manipulation and analytics, including time-window filtering, JOINs, aggregation and deduplication, rate calculations, and window-function–based ranking.

SQL on ad safety. Assume the following schema and sample rows. Use ANSI SQL. Today is 2025-09-01; interpret “last 7 days” as 2025-08-26 00:00:00 to 2025-09-01 23:59:59 UTC.

Tables:

advertisers(advertiser_id INT, name TEXT)
ads(ad_id INT, advertiser_id INT, created_at TIMESTAMP)
ad_page_visits(visit_id BIGINT, ad_id INT, user_id BIGINT, visited_at TIMESTAMP)
ad_reports(report_id BIGINT, ad_id INT, user_id BIGINT, reported_at TIMESTAMP, reason TEXT)

Sample data:

advertisers
+---------------+-----------+
| advertiser_id | name |
+---------------+-----------+
| 101 | Alpha Co |
| 102 | Beta LLC |
| 103 | Gamma Inc |
+---------------+-----------+

ads
+-------+---------------+---------------------+
| ad_id | advertiser_id | created_at |
+-------+---------------+---------------------+
| 1 | 101 | 2025-08-15 10:00:00 |
| 2 | 101 | 2025-08-20 11:00:00 |
| 3 | 102 | 2025-08-25 12:00:00 |
| 4 | 103 | 2025-08-28 09:00:00 |
+-------+---------------+---------------------+

ad_page_visits
+----------+------+---------+---------------------+
| visit_id | ad_id| user_id | visited_at |
+----------+------+---------+---------------------+
| 1 | 1 | 1001 | 2025-08-26 10:00:00 |
| 2 | 1 | 1002 | 2025-08-26 10:05:00 |
| 3 | 1 | 1003 | 2025-08-27 08:00:00 |
| 4 | 2 | 1001 | 2025-08-28 12:00:00 |
| 5 | 2 | 1004 | 2025-08-29 13:00:00 |
| 6 | 3 | 1002 | 2025-08-30 14:00:00 |
| 7 | 3 | 1005 | 2025-08-31 15:00:00 |
| 8 | 4 | 1006 | 2025-08-31 20:00:00 |
| 9 | 4 | 1002 | 2025-09-01 09:00:00 |
| 10 | 4 | 1007 | 2025-09-01 10:30:00 |
+----------+------+---------+---------------------+

ad_reports
+-----------+------+---------+---------------------+-----------+
| report_id | ad_id| user_id | reported_at | reason |
+-----------+------+---------+---------------------+-----------+
| 500 | 1 | 1002 | 2025-08-26 10:06:00 | misleading|
| 501 | 1 | 1003 | 2025-08-27 08:02:00 | offensive |
| 502 | 2 | 1004 | 2025-08-29 13:05:00 | spam |
| 503 | 3 | 1005 | 2025-08-31 15:05:00 | scam |
| 504 | 4 | 1006 | 2025-08-31 20:02:00 | offensive |
+-----------+------+---------+---------------------+-----------+

Tasks: A) Define “top bad advertiser” as one meeting all of: at least 1,000 page visits and at least 50 unique reporters in the last 7 days; rank advertisers by report_rate = total_reports / total_visits (descending). Write a single query that outputs: advertiser_id, total_visits, total_reports, unique_reporters, report_rate, rank, keeping only the top 5 ranks. Use window functions (RANK or DENSE_RANK) and deterministic tiebreakers: total_reports DESC, advertiser_id ASC. B) Modify the query so that if an advertiser has multiple ads, you also output the worst ad_id per advertiser (highest ad-level report_rate over the last 7 days, with at least 100 visits), breaking ties by total_reports DESC then ad_id ASC. C) Return a daily leaderboard (one row per advertiser per day in the last 7 days) with that day’s rank by report_rate, but only over advertisers having ≥200 visits that day. Ensure advertisers with zero reports still appear (rate = 0) and days with no visits are excluded. D) Explain in comments how your query avoids double-counting when a user both visits and reports in the same minute, and how it handles reports without a corresponding visit row within the window.

Compute and rank top bad advertisers over a 7-day window

You are given four tables: advertisers, ads, ad_page_visits, and ad_reports. Consider the fixed 7-day safety window from 2025-05-26 00:00:00 to 2025-06-01 23:59:59 UTC.

Define a "top bad advertiser" as one that meets all of the following in that window:

At least 1,000 ad page visits.
At least 50 unique users who filed at least one report against any of the advertiser's ads.

For each such advertiser, compute:

total_visits: total number of ad_page_visits in the window (across all their ads).
total_reports: total number of ad_reports in the window (across all their ads).
unique_reporters: number of distinct user_id values in ad_reports in the window (across all their ads).
report_rate: total_reports / total_visits (as a decimal).

Rank qualifying advertisers by report_rate in descending order, using a window function (RANK or DENSE_RANK). Use deterministic tie-breakers in this order: total_reports DESC, advertiser_id ASC. Return only advertisers whose rank is in the top 5.

Output columns (one row per qualifying advertiser): advertiser_id, total_visits, total_reports, unique_reporters, report_rate, rank.

Table `advertisers`

column	type	constraints
advertiser_id	INT	PRIMARY KEY
name	VARCHAR(255)	NOT NULL

Sample data:

advertiser_id	name
101	Alpha Co
102	Beta LLC
103	Gamma Inc

Table `ads`

column	type	constraints
ad_id	INT	PRIMARY KEY
advertiser_id	INT	NOT NULL
created_at	TIMESTAMP	NOT NULL

Sample data:

ad_id	advertiser_id	created_at
1	101	2025-05-15 10:00:00
2	101	2025-05-20 11:00:00
3	102	2025-05-25 12:00:00
4	103	2025-05-28 09:00:00

Table `ad_page_visits`

column	type	constraints
visit_id	BIGINT	PRIMARY KEY
ad_id	INT	NOT NULL
user_id	BIGINT	NOT NULL
visited_at	TIMESTAMP	NOT NULL

Sample data:

ad_id	user_id	visit_id	visited_at
1	1001	1	2025-05-26 10:00:00
1	1002	2	2025-05-26 10:05:00
1	1003	3	2025-05-27 08:00:00
2	1001	4	2025-05-28 12:00:00
2	1004	5	2025-05-29 13:00:00
3	1002	6	2025-05-30 14:00:00
3	1005	7	2025-05-31 15:00:00
4	1006	8	2025-05-31 20:00:00
4	1002	9	2025-06-01 09:00:00
4	1007	10	2025-06-01 10:30:00

Table `ad_reports`

column	type	constraints
report_id	BIGINT	PRIMARY KEY
ad_id	INT	NOT NULL
user_id	BIGINT	NOT NULL
reported_at	TIMESTAMP	NOT NULL
reason	VARCHAR(255)	NOT NULL

Sample data:

ad_id	reason	report_id	reported_at	user_id
1	misleading	500	2025-05-26 10:06:00	1002
1	offensive	501	2025-05-27 08:02:00	1003
2	spam	502	2025-05-29 13:05:00	1004
3	scam	503	2025-05-31 15:05:00	1005
4	offensive	504	2025-05-31 20:02:00	1006

Expected output

Top 5 ranks of advertisers whose ads collectively have at least 1,000 visits and at least 50 unique reporters between 2025-05-26 00:00:00 and 2025-06-01 23:59:59 UTC, ordered by report_rate DESC, then total_reports DESC, then advertiser_id ASC. With the small sample data provided, no advertiser meets the thresholds, so the result is empty.

Hints

Aggregate visits and reports separately at the advertiser level, then join the aggregates.
Use RANK() OVER with an ORDER BY that matches report_rate DESC, total_reports DESC, advertiser_id ASC.

Top bad advertisers with their worst-performing ad

Using the same tables and the same fixed 7-day safety window (2025-05-26 00:00:00 to 2025-06-01 23:59:59 UTC), extend the previous task.

A "top bad advertiser" is still defined as having at least 1,000 ad page visits and at least 50 unique reporters in that window.

In addition to the advertiser-level metrics and ranking from Part A, determine for each qualifying advertiser its "worst" ad_id in the same window, defined as:

The ad with the highest ad-level report_rate = ad_reports / ad_visits in the window.
Only consider ads that have at least 100 page visits in the window.
Break ties by ad-level total_reports DESC, then ad_id ASC.

Output columns for each qualifying advertiser:

advertiser_id
total_visits, total_reports, unique_reporters, report_rate (same definitions as Part A)
rank (same ranking as Part A)
worst_ad_id (the ad_id of the worst-performing ad for that advertiser, or NULL if none of its ads reach 100 visits in the window).

Return only advertisers whose rank is in the top 5.

Table `advertisers`

column	type	constraints
advertiser_id	INT	PRIMARY KEY
name	VARCHAR(255)	NOT NULL

Sample data:

advertiser_id	name
101	Alpha Co
102	Beta LLC
103	Gamma Inc

Table `ads`

column	type	constraints
ad_id	INT	PRIMARY KEY
advertiser_id	INT	NOT NULL
created_at	TIMESTAMP	NOT NULL

Sample data:

ad_id	advertiser_id	created_at
1	101	2025-05-15 10:00:00
2	101	2025-05-20 11:00:00
3	102	2025-05-25 12:00:00
4	103	2025-05-28 09:00:00

Table `ad_page_visits`

column	type	constraints
visit_id	BIGINT	PRIMARY KEY
ad_id	INT	NOT NULL
user_id	BIGINT	NOT NULL
visited_at	TIMESTAMP	NOT NULL

Sample data:

ad_id	user_id	visit_id	visited_at
1	1001	1	2025-05-26 10:00:00
1	1002	2	2025-05-26 10:05:00
1	1003	3	2025-05-27 08:00:00
2	1001	4	2025-05-28 12:00:00
2	1004	5	2025-05-29 13:00:00
3	1002	6	2025-05-30 14:00:00
3	1005	7	2025-05-31 15:00:00
4	1006	8	2025-05-31 20:00:00
4	1002	9	2025-06-01 09:00:00
4	1007	10	2025-06-01 10:30:00

Table `ad_reports`

column	type	constraints
report_id	BIGINT	PRIMARY KEY
ad_id	INT	NOT NULL
user_id	BIGINT	NOT NULL
reported_at	TIMESTAMP	NOT NULL
reason	VARCHAR(255)	NOT NULL

Sample data:

ad_id	reason	report_id	reported_at	user_id
1	misleading	500	2025-05-26 10:06:00	1002
1	offensive	501	2025-05-27 08:02:00	1003
2	spam	502	2025-05-29 13:05:00	1004
3	scam	503	2025-05-31 15:05:00	1005
4	offensive	504	2025-05-31 20:02:00	1006

Expected output

Top 5 ranks of advertisers meeting the "top bad advertiser" thresholds in the 7-day window, along with each advertiser's worst-performing ad_id (by ad-level report_rate, subject to at least 100 visits per ad). With the small sample data provided, no advertiser meets the thresholds, so the result is empty.

Hints

First solve the advertiser-level aggregation and ranking, then separately compute ad-level stats.
Use ROW_NUMBER() OVER (PARTITION BY advertiser_id ORDER BY ad_report_rate DESC, ad_reports DESC, ad_id ASC) to pick the worst ad per advertiser.

Daily safety leaderboard by advertiser

Using the same tables, build a daily leaderboard over the fixed 7-day window from 2025-05-26 00:00:00 to 2025-06-01 23:59:59 UTC.

For each calendar date within this range on which there were ad_page_visits, and for each advertiser that had at least 200 visits on that date:

Compute total_visits: number of ad_page_visits that occurred that day for that advertiser (across all ads).
Compute total_reports: number of ad_reports that occurred that day for that advertiser (across all ads).
Compute report_rate: total_reports / total_visits for that day.

Rank advertisers independently for each day by report_rate DESC, using tie-breakers: total_reports DESC, advertiser_id ASC.

Requirements:

Only advertisers with at least 200 visits on that day are included in that day's leaderboard.
Advertisers with zero reports on a day must still appear for that day, with total_reports = 0 and report_rate = 0.
Dates with no visits at all for any advertiser are excluded.

Output one row per advertiser per qualifying day with columns: visit_date, advertiser_id, total_visits, total_reports, report_rate, rank.

Table `advertisers`

column	type	constraints
advertiser_id	INT	PRIMARY KEY
name	VARCHAR(255)	NOT NULL

Sample data:

advertiser_id	name
101	Alpha Co
102	Beta LLC
103	Gamma Inc

Table `ads`

column	type	constraints
ad_id	INT	PRIMARY KEY
advertiser_id	INT	NOT NULL
created_at	TIMESTAMP	NOT NULL

Sample data:

ad_id	advertiser_id	created_at
1	101	2025-05-15 10:00:00
2	101	2025-05-20 11:00:00
3	102	2025-05-25 12:00:00
4	103	2025-05-28 09:00:00

Table `ad_page_visits`

column	type	constraints
visit_id	BIGINT	PRIMARY KEY
ad_id	INT	NOT NULL
user_id	BIGINT	NOT NULL
visited_at	TIMESTAMP	NOT NULL

Sample data:

ad_id	user_id	visit_id	visited_at
1	1001	1	2025-05-26 10:00:00
1	1002	2	2025-05-26 10:05:00
1	1003	3	2025-05-27 08:00:00
2	1001	4	2025-05-28 12:00:00
2	1004	5	2025-05-29 13:00:00
3	1002	6	2025-05-30 14:00:00
3	1005	7	2025-05-31 15:00:00
4	1006	8	2025-05-31 20:00:00
4	1002	9	2025-06-01 09:00:00
4	1007	10	2025-06-01 10:30:00

Table `ad_reports`

column	type	constraints
report_id	BIGINT	PRIMARY KEY
ad_id	INT	NOT NULL
user_id	BIGINT	NOT NULL
reported_at	TIMESTAMP	NOT NULL
reason	VARCHAR(255)	NOT NULL

Sample data:

ad_id	reason	report_id	reported_at	user_id
1	misleading	500	2025-05-26 10:06:00	1002
1	offensive	501	2025-05-27 08:02:00	1003
2	spam	502	2025-05-29 13:05:00	1004
3	scam	503	2025-05-31 15:05:00	1005
4	offensive	504	2025-05-31 20:02:00	1006

Expected output

For each date between 2025-05-26 and 2025-06-01 (inclusive) that has ad_page_visits, output a per-advertiser daily leaderboard row for advertisers with at least 200 visits on that date. With the sample data, no advertiser reaches 200 visits on any single day, so the result is empty.

Hints

Aggregate visits and reports per advertiser per DATE(visited_at) / DATE(reported_at) and then join on advertiser_id + date.
Filter to advertisers with total_visits >= 200 before applying a RANK() OVER (PARTITION BY visit_date ...).

Explain handling of double-counting and orphan reports in safety metrics

Using the same tables and the same 7-day window (2025-05-26 00:00:00 to 2025-06-01 23:59:59 UTC), write a query that computes advertiser-level safety metrics similar to Part A:

total_visits, total_reports, unique_reporters, report_rate, and a rank by report_rate with the same tie-breaking rules.

In this part you do NOT need to enforce any minimum thresholds (no 1,000-visit or 50-reporter filter is required). Include all advertisers in the output.

Add clear SQL comments explaining:

How your query avoids double-counting when a user both visits and reports in the same minute (or any time) on the same ad.
How your query handles reports that fall within the 7-day window but do not have a corresponding ad_page_visits row in that window (for the same ad or advertiser).

Output columns: advertiser_id, total_visits, total_reports, unique_reporters, report_rate, rank.

Table `advertisers`

column	type	constraints
advertiser_id	INT	PRIMARY KEY
name	VARCHAR(255)	NOT NULL

Sample data:

advertiser_id	name
101	Alpha Co
102	Beta LLC
103	Gamma Inc

Table `ads`

column	type	constraints
ad_id	INT	PRIMARY KEY
advertiser_id	INT	NOT NULL
created_at	TIMESTAMP	NOT NULL

Sample data:

ad_id	advertiser_id	created_at
1	101	2025-05-15 10:00:00
2	101	2025-05-20 11:00:00
3	102	2025-05-25 12:00:00
4	103	2025-05-28 09:00:00

Table `ad_page_visits`

column	type	constraints
visit_id	BIGINT	PRIMARY KEY
ad_id	INT	NOT NULL
user_id	BIGINT	NOT NULL
visited_at	TIMESTAMP	NOT NULL

Sample data:

ad_id	user_id	visit_id	visited_at
1	1001	1	2025-05-26 10:00:00
1	1002	2	2025-05-26 10:05:00
1	1003	3	2025-05-27 08:00:00
2	1001	4	2025-05-28 12:00:00
2	1004	5	2025-05-29 13:00:00
3	1002	6	2025-05-30 14:00:00
3	1005	7	2025-05-31 15:00:00
4	1006	8	2025-05-31 20:00:00
4	1002	9	2025-06-01 09:00:00
4	1007	10	2025-06-01 10:30:00

Table `ad_reports`

column	type	constraints
report_id	BIGINT	PRIMARY KEY
ad_id	INT	NOT NULL
user_id	BIGINT	NOT NULL
reported_at	TIMESTAMP	NOT NULL
reason	VARCHAR(255)	NOT NULL

Sample data:

ad_id	reason	report_id	reported_at	user_id
1	misleading	500	2025-05-26 10:06:00	1002
1	offensive	501	2025-05-27 08:02:00	1003
2	spam	502	2025-05-29 13:05:00	1004
3	scam	503	2025-05-31 15:05:00	1005
4	offensive	504	2025-05-31 20:02:00	1006

Expected output

All advertisers with their total_visits, total_reports, unique_reporters, report_rate, and rank over the 7-day window. With the sample data, all three advertisers have at least one visit; the ranking is determined by report_rate DESC, then total_reports DESC, then advertiser_id ASC.

advertiser_id	rank	report_rate	total_reports	total_visits	unique_reporters
101	1	0.6	3	5	3
102	2	0.5	1	2	1
103	3	0.3333	1	3	1

Hints

Explain in comments why you aggregate visits and reports separately before joining.
Base the final SELECT on pre-aggregated per-advertiser tables to avoid row multiplication between visits and reports.

Compute and rank top bad advertisers over a 7-day window

Table advertisers

Table ads

Table ad_page_visits

Table ad_reports

Expected output

Hints

Top bad advertisers with their worst-performing ad

Table advertisers

Table ads

Table ad_page_visits

Table ad_reports

Expected output

Hints

Daily safety leaderboard by advertiser

Table advertisers

Table ads

Table ad_page_visits

Table ad_reports

Expected output

Hints

Explain handling of double-counting and orphan reports in safety metrics

Table advertisers

Table ads

Table ad_page_visits

Table ad_reports

Expected output

Hints

Table `advertisers`

Table `ads`

Table `ad_page_visits`

Table `ad_reports`

Table `advertisers`

Table `ads`

Table `ad_page_visits`

Table `ad_reports`

Table `advertisers`

Table `ads`

Table `ad_page_visits`

Table `ad_reports`

Table `advertisers`

Table `ads`

Table `ad_page_visits`

Table `ad_reports`