Calculate Survey Response Rate and Quality Metric in SQL

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Meta.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Meta during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Quick Overview