Went with a self-join first, which worked but felt clunky when I looked back at it.
Clarify the table schema and what 'submitted ads in both weeks' means (e.g., at least one ad per week). Then write a query that filters for Week 1 and Week 2 submissions and finds advertisers present in both sets, using either INTERSECT, INNER JOIN, or GROUP BY with HAVING.
Pro tip: Mention that if the table has a week column, you can use a self-join or GROUP BY with HAVING COUNT(DISTINCT week) = 2; if not, you may need to infer weeks from dates. Also, discuss performance implications and indexing.
Identify columns like advertiser_id, week (or submission_date), and ad_id. Confirm how weeks are defined (e.g., week number, date range).
Ensure 'submitted ads in both weeks' means the advertiser has at least one ad in Week 1 and at least one in Week 2. Consider if duplicates matter.
Select an approach: INTERSECT (if supported), INNER JOIN on advertiser_id with week filters, or GROUP BY advertiser_id with HAVING COUNT(DISTINCT week) = 2.
Write the SQL, ensuring correct filtering and handling of NULLs. Test with sample data to confirm it returns only advertisers in both weeks.
Mention handling of advertisers with multiple submissions, missing weeks, and indexing for efficiency.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.