Calculate Response Rate and Compare New vs. Existing User Scores
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
survey_events
+---------+------------+-----------+--------------+---------------------+
| user_id | is_new_user| responded | survey_score | event_time |
+---------+------------+-----------+--------------+---------------------+
| 101 | true | true | 5 | 2023-11-01 10:00:00 |
| 102 | false | false | NULL | 2023-11-01 10:05:00 |
| 103 | true | true | 4 | 2023-11-01 10:10:00 |
| 104 | false | true | 3 | 2023-11-01 10:20:00 |
| 105 | true | false | NULL | 2023-11-01 10:30:00 |
+---------+------------+-----------+--------------+---------------------+
##### Scenario
Product team ran an in-app survey and stored results in a table; they need response rate and to know if new users provide better survey scores.
##### Question
Write SQL to calculate the overall survey response rate. Write SQL to compare average survey_score between new and existing users and test if the difference is significant.
##### Hints
Use conditional aggregation or CTEs; remember NULL handling.
Overview: This question evaluates data manipulation and statistical analysis skills for a data scientist, focusing on SQL aggregation, NULL handling, cohort comparison, and testing differences in survey scores.
You are given an in-app survey_events table with one row per user event. A user may or may not respond to the survey; if they respond, survey_score is populated.
Write SQL that produces a single result set with two rows:
1) An overall survey response rate row.
2) A comparison row of average survey_score between new users (is_new_user = true) and existing users (is_new_user = false), including a Welch’s t-test statistic and degrees of freedom when both groups have at least 2 scored responses. If either group has fewer than 2 scored responses, return NULL for the t-statistic, df, p-value, and significance flag, and set can_test = false.
The output should include these columns:
- metric (label for the row, e.g., 'response_rate' or 'score_comparison_new_vs_existing')
- total_events, responses, response_rate, response_rate_pct
- new_avg, new_n, existing_avg, existing_n, diff_new_minus_existing
- t_stat, df, p_value, significant_95pct, can_test
Only non-NULL survey_score values from responded users should be used for the score comparison and t-test.
Tables
survey_events(user_id INTEGER, is_new_user BOOLEAN, responded BOOLEAN, survey_score INTEGER, event_time TIMESTAMP)
Hints
- Overall response rate can be computed as AVG(CASE WHEN responded THEN 1.0 ELSE 0.0 END).
- Only include non-NULL survey_score rows (typically responded = true) when computing score averages and variances.