Compute feed ad frequency and retention in SQL
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Assume today is 2025-09-01.
Schema and tiny samples:
feed_impressions(impression_id, user_id, impression_time, content_type, feed_position, session_id)
100 | 1 | 2025-08-31 09:00 | organic | 1 | s1
101 | 1 | 2025-08-31 09:01 | ad | 2 | s1
102 | 1 | 2025-08-31 09:02 | organic | 3 | s1
103 | 1 | 2025-08-31 09:03 | ad | 4 | s1
200 | 3 | 2025-08-31 10:00 | organic | 1 | s2
201 | 3 | 2025-08-31 10:01 | ad | 2 | s2
feed_clicks(impression_id, click_time)
101 | 2025-08-31 09:01:10
103 | 2025-08-31 09:03:05
201 | 2025-08-31 10:01:05
sessions(session_id, user_id, session_start, session_end)
s1 | 1 | 2025-08-31 09:00 | 2025-08-31 09:10
s2 | 3 | 2025-08-31 10:00 | 2025-08-31 10:05
user_activity(user_id, event_date)
1 | 2025-08-25
1 | 2025-09-01
3 | 2025-08-25
3 | 2025-08-29
Tasks:
(a) For each session, compute ad_frequency = ads/(ads+organic) considering positions ≥2 only, and flag sessions where ad_frequency > 0.30.
(b) Compute ad CTR by position bin (1–2, 3–5, 6+) using impressions joined to clicks, and also compute a user-fixed-effect adjusted CTR by subtracting each user’s mean CTR.
(c) For the last 7 days (2025-08-26 to 2025-09-01), compute D1 and D7 retention for cohorts of users who saw ≥1 ad vs saw 0 ads, where cohorts are defined by each user’s first activity date in that window. Return a single result table with cohort_date, cohort_type (saw_ad/zero_ad), d1_retention, d7_retention.
Quick Answer: This question evaluates SQL-based product analytics skills, specifically session-level aggregation for ad frequency, impression-to-click joins for CTR by feed position bins, cohort-based retention computation, and user-fixed-effect adjustments; it is categorized under Data Manipulation (SQL/Python) for a Data Scientist role.