SQL Queries and Analysis on Bad Advertisers

Quick Overview

This question evaluates SQL query writing, metric definition and normalization, ranking/aggregation techniques, and analytical reasoning for identifying problematic advertisers.

SQL Queries and Analysis on Bad Advertisers

Company: TikTok

Role: Data Scientist

Category: Analytics & Experimentation

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

Quick Answer: This question evaluates SQL query writing, metric definition and normalization, ranking/aggregation techniques, and analytical reasoning for identifying problematic advertisers.

|Home/Analytics & Experimentation/TikTok
TikTok logo
TikTok
Jun 29, 2025, 4:58 PM
mediumData ScientistTechnical ScreenAnalytics & Experimentation
12
0

Scenario

You are on the analytics team at TikTok and need to analyze the presence of bad content in ads and identify problematic advertisers.

Question 1

Propose a clear definition of a "top bad advertiser" and write an SQL query to identify them. Consider factors such as the number of reports an advertiser receives and appropriate normalization/ranking metrics.

Question 2

There is a positive correlation between ad page visits and reports on that ad being bad. Outline how you would investigate reasons behind this correlation. What hypotheses would you test?

Hints

  • Think about how to capture and rank bad advertisers using available data.
  • Reflect on why more page visits might lead to more reports (e.g., higher visibility, UI/reporting friction, confounding by exposure).
Loading comments...