Calculate and Compare Survey Response Rates for User Tenure
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Surveys
+--------+------------+--------------+----------+
| userid | date | survey_event | response |
+--------+------------+--------------+----------+
| 101 | 2023-01-10 | notif_popup | 5 |
| 102 | 2023-01-11 | notif_popup | 3 |
| 101 | 2023-01-12 | notif_modal | 4 |
+--------+------------+--------------+----------+
Users
+--------+------------+----------+
| userid | reg_date | country |
+--------+------------+----------+
| 101 | 2022-12-20 | US |
| 102 | 2023-01-05 | CA |
| 103 | 2023-01-07 | US |
+--------+------------+----------+
##### Scenario
Mobile app sends a post-notification survey to users; two tables (Surveys, Users) track responses and registration dates.
##### Question
Write SQL to compute the overall response rate to the survey. 2. Using SQL, determine whether newer users rate notifications higher than long-tenured users.
##### Hints
Join, aggregate, bucket users by tenure, compare average/median response.
Overview: This question evaluates proficiency in relational data manipulation and metric computation, including SQL joins, aggregation and cohort-based comparisons relevant to survey response analysis, and is categorized under Data Manipulation (SQL/Python) for a Data Scientist role.
You work on a mobile app that sends post-notification surveys. Using `Surveys` and `Users`, return one result set with columns `metric_type`, `tenure_bucket`, and `value` for: (1) overall response rate, defined as distinct survey respondents divided by total registered users; (2) average survey response by tenure bucket at survey time, where `new` means fewer than 14 days since registration and `long` means at least 14 days; (3) median survey response by the same tenure buckets; and (4) a binary metric `newer_users_rate_higher`, equal to 1 when the new-user average is strictly greater than the long-user average, else 0.
Tables
Users(userid INTEGER, reg_date DATE, country VARCHAR(2))
Surveys(userid INTEGER, date DATE, survey_event VARCHAR(20), response INTEGER)
Hints
- Join Surveys to Users on userid to compute tenure at survey time (survey date - registration date).
- Define tenure buckets with CASE, e.g., tenure < 14 days as 'new' and 'long' otherwise.