cswork / libraryOpen practice workspace

Coding questions · TikTok

Identify Repeat Advertisers from Consecutive Weeks

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.

Repeat advertisers across two consecutive weeks

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.

Table SUBMISSIONS

columntypeconstraints
advertiser_idVARCHAR
ad_idINTEGER
week_idINTEGER

Sample data:

ad_idadvertiser_idweek_id
9001A1231
9005A1232
9010B4561
9020C7892
9012B4562

Expected output

All unique advertiser_id values that have at least one submission in both week 1 and week 2.

advertiser_id
A123
B456

Hints

  1. Filter rows to week_id IN (1, 2).
  2. Use INTERSECT to find common advertiser_id between week 1 and week 2.
  3. Alternatively, self-join week 1 to week 2 on advertiser_id.
  4. Or GROUP BY advertiser_id with HAVING COUNT(DISTINCT week_id) = 2.