Calculate Daily Survey Response Rates by Country

Problem Statement

Meta frequently runs in-app micro-surveys to measure user sentiment and product satisfaction. You are given two tables tracking survey delivery and completion: survey_impressions and survey_responses.

Write a PostgreSQL query to calculate the daily survey response rate for each country. The response rate is defined as the number of distinct users who submitted a valid survey response divided by the number of distinct users who were shown a survey impression on that date.

Requirements:

1. A response is only considered valid if it joins back to an impression recorded in survey_impressions on that same date. Orphan responses (responses missing an impression record or referencing an impression from another day) must not be counted toward that date's response metric.
2. Dates should be extracted from impression_time in UTC (YYYY-MM-DD).
3. Calculate:
   • survey_date: Date of the impression (YYYY-MM-DD).
   • country: Country code.
   • impression_users: Count of distinct users who received an impression on that date in that country.
   • response_users: Count of distinct users who responded to an impression delivered on that date in that country.
   • response_rate: response_users / impression_users rounded to 4 decimal places. If a date/country combination has zero response users, return 0.0000.
4. Exclude country-date combinations where impression_users is 0.
5. Order results by survey_date descending, then by country ascending.

Schema

-- survey_impressions
+-----------------+-------------+
| Column Name     | Type        |
+-----------------+-------------+
| impression_id   | VARCHAR     |
| user_id         | VARCHAR     |
| survey_id       | VARCHAR     |
| country         | VARCHAR     |
| impression_time | TIMESTAMP   |
+-----------------+-------------+

-- survey_responses
+-----------------+-------------+
| Column Name     | Type        |
+-----------------+-------------+
| response_id     | VARCHAR     |
| impression_id   | VARCHAR     |
| user_id         | VARCHAR     |
| response_time   | TIMESTAMP   |
+-----------------+-------------+

Example

survey_impressions

+---------------+---------+-----------+---------+---------------------+
| impression_id | user_id | survey_id | country | impression_time     |
+---------------+---------+-----------+---------+---------------------+
| imp_1         | u1      | s1        | US      | 2024-03-01 10:00:00 |
| imp_2         | u1      | s1        | US      | 2024-03-01 14:00:00 |
| imp_3         | u2      | s1        | US      | 2024-03-01 11:30:00 |
| imp_4         | u3      | s1        | US      | 2024-03-01 18:00:00 |
| imp_5         | u4      | s1        | UK      | 2024-03-01 09:15:00 |
+---------------+---------+-----------+---------+---------------------+

survey_responses

+-------------+---------------+---------+---------------------+
| response_id | impression_id | user_id | response_time       |
+-------------+---------------+---------+---------------------+
| res_1       | imp_1         | u1      | 2024-03-01 10:05:00 |
| res_2       | imp_2         | u1      | 2024-03-01 14:02:00 |
| res_3       | imp_3         | u2      | 2024-03-01 11:35:00 |
| res_4       | imp_999       | u99     | 2024-03-01 12:00:00 | -- orphan
+-------------+---------------+---------+---------------------+

Output

+-------------+---------+------------------+----------------+---------------+
| survey_date | country | impression_users | response_users | response_rate |
+-------------+---------+------------------+----------------+---------------+
| 2024-03-01  | UK      | 1                | 0              | 0.0000        |
| 2024-03-01  | US      | 3                | 2              | 0.6667        |
+-------------+---------+------------------+----------------+---------------+

Explanation: For US on 2024-03-01, users u1, u2, and u3 saw impressions (3 distinct users). Users u1 and u2 responded (2 distinct users). User u1 responded twice, but only counts once. res_4 is an orphan record with no matching impression and is discarded. Rate is 2 / 3 = 0.6667.