SQL Queries and Analysis on Bad Advertisers
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
##### Scenario
You are part of the analytics team at TikTok and are tasked with analyzing the presence of bad content on the platform, particularly focusing on adverts.
Question 1
Write an SQL query to define and identify what constitutes a 'top bad advertiser'. Consider factors such as the number of reports an advertiser receives and other relevant ranking metrics.
Question 2
Given the information that there is a positive correlation between ad page visits and reports on that ad being bad, outline your approach to investigate the reasons behind this correlation. What would be your hypothesis?
##### Hints
Consider how you would capture and rank bad advertisers using available data.
Reflect on potential reasons for the positive correlation (e.g., higher visibility leading to more scrutiny).
Overview: This question evaluates SQL query writing, metric definition and normalization, ranking/aggregation techniques, and analytical reasoning for identifying problematic advertisers.
Rank top bad advertisers
Using the provided tables, compute per-advertiser metrics: total_reports, total_visits, report_rate (reports/visits), distinct_reporters, avg_severity, and recent_reports_30d (reports in the last 30 days relative to 2025-08-31, i.e., between 2025-08-02 and 2025-08-31 inclusive). Return advertisers with at least one report and rank them as bad_advertiser_rank ordered by total_reports DESC, report_rate DESC, recent_reports_30d DESC, then avg_severity DESC.
Tables
advertisers(advertiser_id INTEGER, advertiser_name VARCHAR(100), industry VARCHAR(50), country VARCHAR(2), account_created_date DATE)
ads(ad_id INTEGER, advertiser_id INTEGER, ad_title VARCHAR(150), created_at TIMESTAMP, status VARCHAR(20))
ad_visits(visit_id BIGINT, ad_id INTEGER, user_id INTEGER, visited_at TIMESTAMP, source VARCHAR(20))
ad_reports(report_id BIGINT, ad_id INTEGER, reporter_user_id INTEGER, reason VARCHAR(100), severity INTEGER, reported_at TIMESTAMP, status VARCHAR(20))
Hints
- Aggregate reports and visits by advertiser using CTEs.
- Use NULLIF to avoid division by zero when computing report_rate.
Visits-Reports Correlation by Ad
At the ad level, compute the Pearson correlation between total visits and total reports.
Use the `ads`, `ad_visits`, and `ad_reports` tables. First aggregate each ad to two metrics: `total_visits` and `total_reports`, counting zero when an ad has no matching visit or report rows. Then return a single row with one column:
- `visits_reports_corr`: the Pearson correlation coefficient between ad-level `total_visits` and `total_reports`, rounded to 4 decimal places.
Include every ad from `ads` in the correlation.
Tables
ads(ad_id INTEGER, advertiser_id INTEGER, ad_title VARCHAR(150), created_at TIMESTAMP, status VARCHAR(20))
ad_visits(visit_id BIGINT, ad_id INTEGER, user_id INTEGER, visited_at TIMESTAMP, source VARCHAR(20))
ad_reports(report_id BIGINT, ad_id INTEGER, reporter_user_id INTEGER, reason VARCHAR(100), severity INTEGER, reported_at TIMESTAMP, status VARCHAR(20))
Hints
- Aggregate to one row per ad before computing correlation.
- Use COUNT(DISTINCT ...) with left joins so ads with no visits or reports stay in the data.