Compute and rank top bad advertisers
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: 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.
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.
Tables
advertisers(advertiser_id INT, name VARCHAR(255))
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 VARCHAR(255))
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.
Tables
advertisers(advertiser_id INT, name VARCHAR(255))
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 VARCHAR(255))
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.
Tables
advertisers(advertiser_id INT, name VARCHAR(255))
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 VARCHAR(255))
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:
1) How your query avoids double-counting when a user both visits and reports in the same minute (or any time) on the same ad.
2) 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.
Tables
advertisers(advertiser_id INT, name VARCHAR(255))
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 VARCHAR(255))
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.