Compute feed ad frequency and retention in SQL
Overview
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.
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.