Survey Response Rate per Impression and per Unique User in SQL

An app shows short in-app surveys to its users, and users who answer give a rating from 1 to 5. Every time a survey is shown and every time a user answers, one row is written to an event table. Compute the survey response rate in two ways: 1. **Impression level:** the number of responses divided by the number of survey impressions. 2. **Unique-user level:** the number of distinct users who responded at least once, divided by the number of distinct users who were shown at least one survey. The report asks for the impression-level rate first and the unique-user rate as a follow-up. This version asks for both in one query and fixes rounding and empty-table behavior. ### Input Table(s) **`survey`**: one row per survey event. The table has no primary key; identical rows are separate events. | Column | Type | Meaning | | --- | --- | --- | | `userid` | INTEGER | The user who was shown or answered the survey | | `date` | DATE | Calendar date of the event | | `survey_event` | TEXT | `impression` when the survey was shown, `response` when the user answered it | | `response` | INTEGER | The rating from 1 to 5 on `response` rows; NULL on `impression` rows | ### Output Contract Write one read-only PostgreSQL query (`SELECT`, optionally with `WITH`) that returns exactly one row with these columns, in this order: | Column | Type | Meaning | | --- | --- | --- | | `impression_response_rate` | numeric | Number of `response` rows divided by the number of `impression` rows, rounded to 4 decimal places | | `user_response_rate` | numeric | Number of distinct users with at least one `response` row divided by the number of distinct users with at least one `impression` row, rounded to 4 decimal places | The result always has exactly one row, so no `ORDER BY` is needed. Both rates are NULL when the table has no `impression` rows, including when the table is empty. ### Example `survey`: | userid | date | survey_event | response | | --- | --- | --- | --- | | 1 | 2025-05-01 | impression | NULL | | 1 | 2025-05-01 | response | 4 | | 1 | 2025-05-03 | impression | NULL | | 2 | 2025-05-02 | impression | NULL | | 2 | 2025-05-02 | impression | NULL | | 3 | 2025-05-02 | impression | NULL | | 3 | 2025-05-04 | response | 2 | | 4 | 2025-05-05 | impression | NULL | | 5 | 2025-05-03 | impression | NULL | | 5 | 2025-05-03 | response | 5 | Expected result: | impression_response_rate | user_response_rate | | --- | --- | | 0.4286 | 0.6000 | - There are 7 impression rows (users 1 and 2 have two each, users 3, 4 and 5 one each; user 2's two identical rows both count) and 3 response rows, so the impression-level rate is 3/7, which rounds to 0.4286. - Users 1, 2, 3, 4 and 5 were shown a survey, and users 1, 3 and 5 responded, so the unique-user rate is 3/5 = 0.6000. ### Constraints and Clarifications - `survey_event` is always exactly `impression` or `response`, in lowercase. - `response` is NULL on every `impression` row and an integer from 1 to 5 on every `response` row. `userid`, `date` and `survey_event` are never NULL. - Every response answers an impression shown to the same user, so a user never has more `response` rows than `impression` rows, and every user with a `response` row also has at least one `impression` row. Both rates lie between 0 and 1. - Identical rows are separate events, and each one counts toward the impression-level rate. - A user counts once in the unique-user rate, however many impressions or responses they have. - Both rates cover the whole table; `date` is not used for filtering. - Each rate is the exact ratio rounded to 4 decimal places, so 3/7 is reported as 0.4286, never as 0. ```hint Count the same kind of thing One rate compares rows with rows and the other compares people with people. Make sure each numerator counts the same kind of thing as its denominator. ```