Calculate Response Rate and Compare User Survey Ratings
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: This question evaluates data manipulation and analytical skills, including correct SQL join selection, NULL handling, aggregation grain, denominator construction for response rates, and comparative statistics for mean rating differences between cohorts.
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.
Tables
SURVEYS(survey_id INTEGER, user_id INTEGER, sent_at DATE)
SURVEY_RESPONSES(survey_id INTEGER, user_id INTEGER, responded_at TIMESTAMP, rating INTEGER)
Hints
- LEFT JOIN from SURVEYS to SURVEY_RESPONSES so all surveys are counted.
- Use NULLIF to avoid division by zero when there are no surveys.
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.
Tables
USERS(user_id INTEGER, signup_date DATE)
SURVEYS(survey_id INTEGER, user_id INTEGER, sent_at DATE)
SURVEY_RESPONSES(survey_id INTEGER, user_id INTEGER, responded_at TIMESTAMP, rating INTEGER)
Hints
- Define cohort at send time using signup_date + INTERVAL '30 days' and a strict '<' comparison for '<30 days'.
- LEFT JOIN responses so unrated surveys don't create extra rows.
Community answers
Answer by sarem29
Your LEFT JOIN idea is fine, but the counting logic you suggested is wrong unless there is exactly one response row per survey. After a LEFT JOIN, COUNT(*) counts joined rows, not surveys, and COUNT(responded_at) counts response rows, not “surveys that received a response.”
Example: 3 surveys total (IDs 1,2,3). Survey 1 has 2 rows in survey_responses, surveys 2 and 3 have none. The true response rate is 1/3 = 0.3333 (only survey 1 responded). But your method on the joined table gives COUNT(*)=4 and COUNT(responded_at)=2, so 2/4 = 0.5000, which is wrong.
Response rate should be based on distinct survey_id in numerator and denominator.