Calculate Response Rate and Compare New vs. Existing User Scores

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.

Survey response rate and score comparison for new vs existing users

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.

Table survey_events

column	type	constraints
user_id	INTEGER
is_new_user	BOOLEAN
responded	BOOLEAN
survey_score	INTEGER
event_time	TIMESTAMP

Sample data:

event_time	is_new_user	responded	survey_score	user_id
2023-11-01 10:00:00	true	true	5	101
2023-11-01 10:05:00	false	false	NULL	102
2023-11-01 10:10:00	true	true	4	103
2023-11-01 10:20:00	false	true	3	104
2023-11-01 10:30:00	true	false	NULL	105

Expected output

A single result set with two rows. The first row (metric = 'response_rate') reports overall response rate across all events. The second row (metric = 'score_comparison_new_vs_existing') reports average survey scores and sample sizes for new vs existing users, the difference in means, and Welch’s t-test statistics where possible. When either group has fewer than 2 scored responses, t_stat, df, p_value, and significant_95pct are NULL and can_test = false.

can_test	df	diff_new_minus_existing	existing_avg	existing_n	metric	new_avg	new_n	p_value	response_rate	response_rate_pct	responses	significant_95pct	t_stat	total_events
NULL	NULL	NULL	NULL	NULL	response_rate	NULL	NULL	NULL	0.6	60	3	NULL	NULL	5
false	NULL	1.5	3	1	score_comparison_new_vs_existing	4.5	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL

Hints

1. Overall response rate can be computed as AVG(CASE WHEN responded THEN 1.0 ELSE 0.0 END).
2. Only include non-NULL survey_score rows (typically responded = true) when computing score averages and variances.
3. Use conditional aggregation by is_new_user to get count, mean, and variance for each group.
4. For significance, use Welch’s t-test: t = (mean1 - mean2) / sqrt(var1/n1 + var2/n2), with df from the Welch–Satterthwaite formula.
5. Require n1 >= 2 and n2 >= 2; otherwise return NULL for t-statistics and mark can_test = false.
6. Be careful with NULL handling; functions like VAR_SAMP/STDDEV_SAMP return NULL when the group size is less than 2.