Identify Repeat Advertisers from Consecutive Weeks
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
SUBMISSIONS
+--------------+-----------+-----------+
| advertiser_id| ad_id | week_id |
+--------------+-----------+-----------+
| A123 | 9001 | 1 |
| A123 | 9005 | 2 |
| B456 | 9010 | 1 |
| C789 | 9020 | 2 |
| B456 | 9012 | 2 |
+--------------+-----------+-----------+
##### Scenario
Company holds weekly advertiser submissions log and wants to know repeat advertisers across two consecutive weeks.
##### Question
Write an SQL query that returns the advertiser_id(s) that submitted ads in BOTH Week 1 and Week 2.
##### Hints
Filter by week_id, use INTERSECT, self-join, or GROUP BY HAVING COUNT(DISTINCT week_id)=2.
Quick Answer: This question evaluates a candidate's proficiency in manipulating relational data to identify recurring entities across consecutive time periods, focusing on SQL/Python data manipulation and temporal comparison of records.