Calculate Survey Response Rate and Quality Metric in SQL
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
survey_responses
+---------+----------+---------------------+---------------------+-------+
| user_id | survey_id| impression_ts | click_ts | score |
+---------+----------+---------------------+---------------------+-------+
| 101 | 555 | 2023-09-01 10:00:00 | 2023-09-01 10:05:12 | 4 |
| 102 | 555 | 2023-09-01 10:02:34 | NULL | NULL |
| 101 | 556 | 2023-09-02 09:15:20 | 2023-09-02 09:16:00 | 5 |
| 103 | 555 | 2023-09-01 10:10:00 | NULL | NULL |
| 104 | 557 | 2023-09-03 11:00:00 | 2023-09-03 11:02:45 | 3 |
+---------+----------+---------------------+---------------------+-------+
##### Scenario
SQL analysis of in-app survey effectiveness for the travel app.
##### Question
Write a query to compute the overall survey response rate, defined as total clicks divided by total impressions. Using the score column, calculate a survey-quality metric. Show two approaches:
(a) average every score available,
(b) average only the first score a user gave for each survey. Explain which you would choose and why.
##### Hints
NULL click_ts means no click; distinct user-survey pairs for first-score logic.
Overview: This question evaluates SQL data-manipulation competencies such as aggregation, NULL handling, deduplication (first-per-group), and computing engagement and quality metrics from event logs.
You work on a travel app and collect in-app survey responses in the survey_responses table. Each row is a survey impression to a user, with an optional click_ts and score if the user responded.
Write a query that:
1) Computes the overall survey response rate, defined as total clicks divided by total impressions (a click is a row with non-NULL click_ts).
2) Computes two survey-quality metrics using the score column:
(a) avg_score_all: the average of every non-NULL score.
(b) avg_score_first_per_user_survey: the average of only the first scored response that each user gave for each survey (earliest click_ts per (user_id, survey_id)).
Return a single row with these three metrics: response_rate, avg_score_all, avg_score_first_per_user_survey.
Tables
survey_responses(user_id INTEGER, survey_id INTEGER, impression_ts TIMESTAMP, click_ts TIMESTAMP, score INTEGER)
Hints
- Treat rows with non-NULL click_ts as clicks; every row is an impression.
- For the 'first per user-survey' score, deduplicate by (user_id, survey_id) and keep only the earliest click_ts (ROW_NUMBER() OVER PARTITION BY user_id, survey_id ORDER BY click_ts).