Coding questions · Meta
Calculate and Compare Survey Response Rates for User Tenure
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.
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.
Survey response rate and user tenure analysis
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.
Table Users
| column | type | constraints |
|---|---|---|
| userid | INTEGER | PRIMARY KEY, NOT NULL |
| reg_date | DATE | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
Sample data:
| country | reg_date | userid |
|---|---|---|
| US | 2022-12-20 | 101 |
| CA | 2023-01-05 | 102 |
| US | 2023-01-07 | 103 |
Table Surveys
| column | type | constraints |
|---|---|---|
| userid | INTEGER | NOT NULL, FOREIGN KEY REFERENCES Users(userid) |
| date | DATE | NOT NULL |
| survey_event | VARCHAR(20) | NOT NULL |
| response | INTEGER | NOT NULL, CHECK (response BETWEEN 1 AND 5) |
Sample data:
| date | response | survey_event | userid |
|---|---|---|---|
| 2023-01-10 | 5 | notif_popup | 101 |
| 2023-01-11 | 3 | notif_popup | 102 |
| 2023-01-12 | 4 | notif_modal | 101 |
Expected output
One result set with columns (metric_type, tenure_bucket, value). Includes: (1) overall response rate = distinct users with at least one survey response divided by total users; (2) average and median survey response by tenure bucket at the time of survey (here: 'new' if tenure < 14 days, 'long' otherwise); and (3) a binary flag indicating whether newer users rate higher than long-tenured users. On the sample data, overall response rate is 2/3 ≈ 0.6667; avg/median by tenure are: new = 3.0, long = 4.5; newer users do not rate higher (flag = 0).
| metric_type | tenure_bucket | value |
|---|---|---|
| avg_response_by_tenure | long | 4.5 |
| avg_response_by_tenure | new | 3 |
| median_response_by_tenure | long | 4.5 |
| median_response_by_tenure | new | 3 |
| newer_users_rate_higher | NULL | 0 |
| overall_response_rate | NULL | 0.6667 |
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.
- Overall response rate = count(distinct respondent userids) / count(users).
- Use AVG(response) for mean and PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY response) for median.