Katkat
Back

Calculate Survey Response Rate and Quality Metric in SQL

MetaData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.

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.