Calculate Response Rate and Compare User Survey Ratings

USERS

user_id | signup_date

10 | 2024-03-20

11 | 2024-04-01

12 | 2024-04-05

SURVEYS

survey_id | user_id | sent_at

1 | 10 | 2024-04-01

2 | 11 | 2024-04-02

3 | 12 | 2024-04-05

SURVEY_RESPONSES

survey_id | user_id | responded_at | rating

1 | 10 | 2024-04-01 10:02 | 4

3 | 12 | 2024-04-05 12:15 | 5

Scenario

Using Meta’s notification-survey data, write SQL to (a) compute the survey response rate and (b) test whether new users have a higher average survey rating than existing users.

Question

Write a query that returns overall response_rate = #responses / #surveys. State and handle your join choice when surveys lack a response. Write a query that compares mean rating between new users (<30 days since signup) and existing users, controlling aggregation level appropriately.

Hints

Think join type, denominator, NULL handling, aggregation grain, and division-by-zero safeguards.

Overall survey response rate

Compute the overall survey response rate as (# of surveys with a response) divided by (total # of surveys). Include surveys without responses in the denominator. Return one row with response_rate rounded to 4 decimals.

Table SURVEYS

column	type	constraints
survey_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL
sent_at	DATE	NOT NULL

Sample data:

sent_at	survey_id	user_id
2024-04-01	1	10
2024-04-02	2	11
2024-04-05	3	12

Table SURVEY_RESPONSES

column	type	constraints
survey_id	INTEGER	PRIMARY KEY, NOT NULL, FOREIGN KEY to SURVEYS(survey_id)
user_id	INTEGER	NOT NULL
responded_at	TIMESTAMP	NOT NULL
rating	INTEGER	NOT NULL, CHECK 1 <= rating <= 5

Sample data:

rating	responded_at	survey_id	user_id
4	2024-04-01 10:02	1	10
5	2024-04-05 12:15	3	12

Expected output

One row with the overall response_rate across all surveys. With the sample data: 2 responses out of 3 surveys = 0.6667.

response_rate
0.6667

Hints

1. LEFT JOIN from SURVEYS to SURVEY_RESPONSES so all surveys are counted.
2. Use NULLIF to avoid division by zero when there are no surveys.
3. ROUND the result to 4 decimal places.

Average rating by cohort (new <30 days vs existing)

For each user cohort at send time—'new' if the survey was sent within 30 days of signup, else 'existing'—compute the average survey rating and the number of responses. Include both cohorts even if one has no responses. Return user_cohort, avg_rating, and response_count.

Table USERS

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL

Sample data:

signup_date	user_id
2024-03-20	10
2024-04-01	11
2024-04-05	12

Table SURVEYS

column	type	constraints
survey_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY to USERS(user_id)
sent_at	DATE	NOT NULL

Sample data:

sent_at	survey_id	user_id
2024-04-01	1	10
2024-04-02	2	11
2024-04-05	3	12

Table SURVEY_RESPONSES

column	type	constraints
survey_id	INTEGER	PRIMARY KEY, NOT NULL, FOREIGN KEY to SURVEYS(survey_id)
user_id	INTEGER	NOT NULL, FOREIGN KEY to USERS(user_id)
responded_at	TIMESTAMP	NOT NULL
rating	INTEGER	NOT NULL, CHECK 1 <= rating <= 5

Sample data:

rating	responded_at	survey_id	user_id
4	2024-04-01 10:02	1	10
5	2024-04-05 12:15	3	12

Expected output

Average rating and response count by user cohort at send time. Include both 'new' and 'existing' cohorts even if one has no responses. With the sample data, all surveys are 'new', so 'existing' shows NULL avg_rating and 0 responses.

avg_rating	response_count	user_cohort
NULL	0	existing
4.5	2	new

Hints

1. Define cohort at send time using signup_date + INTERVAL '30 days' and a strict '<' comparison for '<30 days'.
2. LEFT JOIN responses so unrated surveys don't create extra rows.
3. Pre-enumerate 'new' and 'existing' to include missing cohorts.