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.
Overview: 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.
Given a weekly advertiser submissions log, write an SQL query that returns the advertiser_id values that submitted at least one ad in both week 1 and week 2.
Tables
SUBMISSIONS(advertiser_id VARCHAR, ad_id INTEGER, week_id INTEGER)
Hints
- Filter rows to week_id IN (1, 2).
- Use INTERSECT to find common advertiser_id between week 1 and week 2.