TikTok: Investigating Correlation Between Ad Visits and Reports

Quick Overview

This question evaluates a data scientist's competency in operational analytics, including defining and aggregating metrics in SQL and applying causal reasoning to interpret correlations between ad exposures and user reports.

Investigating Correlation Between Ad Visits and Reports

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 a social media platform. Recently, it has been observed that there is a positive correlation between the number of page visits to an advertisement and the reports labeling it as bad content. An investigation is warranted to understand this phenomenon better. ##### Question Define what constitutes a 'top bad advertiser' using SQL. What metrics and conditions would you apply to identify advertisers with high incidents of reported ads? Propose a method to investigate the positive correlation between ad page visits and the frequency of reports. Develop a hypothesis and outline the steps you would take to validate or refute it. ##### Hints Consider the appropriateness of correlation vs. causation. Analyze potential factors contributing to higher report rates, including ad content, engagement types, and user demographics. Describe the data fields and SQL queries you might use to break down and assess these patterns.

Overview: This question evaluates a data scientist's competency in operational analytics, including defining and aggregating metrics in SQL and applying causal reasoning to interpret correlations between ad exposures and user reports.

Identify Top Bad Advertisers

Using the tables below over the full data shown (2025-06-01 to 2025-06-05), return advertisers who qualify as 'top bad advertisers' defined as having at least 1,000 total visits and at least 5 total reports in the analysis window. For each such advertiser, output advertiser_id, advertiser_name, total_visits, total_reports, reports_per_1000_visits, and the Pearson correlation between daily ad visits and daily reports across all of their ads (corr_visits_reports). Order the result by reports_per_1000_visits DESC, then total_reports DESC.

Tables

advertisers(advertiser_id INTEGER, advertiser_name VARCHAR(100), industry VARCHAR(50))

ads(ad_id INTEGER, advertiser_id INTEGER, ad_title VARCHAR(200), category VARCHAR(50))

ad_visits(ad_id INTEGER, visit_date DATE, visits INTEGER)

ad_reports(report_id INTEGER, ad_id INTEGER, user_id INTEGER, report_ts TIMESTAMP, reason VARCHAR(50))

Hints

Aggregate reports by DATE(report_ts) and FULL JOIN to visits to include zero-report or zero-visit days.
Use CORR(visits, reports) to compute Pearson correlation at ad-day granularity per advertiser.

Reason-Level Normalized Report Rates

Using the same data window (2025-06-01 to 2025-06-05), for each advertiser and ad, break down reports by reason and return advertiser_id, advertiser_name, ad_id, ad_title, reason, total_visits, reports_for_reason, and reports_per_1000_visits for each reason. Order by advertiser_name, ad_id, and reports_per_1000_visits DESC.