Katkat
Back

Identify Repeat Advertisers from Consecutive Weeks

TikTokData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.

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.