Calculate Daily Survey Response Rates by Country
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
The interview report preserved the survey tables and the request to calculate response rate, but not the exact grouping or output contract. The following is a self-contained practice reconstruction of that task.
You have two PostgreSQL tables:
```text
survey
------
user_id BIGINT NOT NULL
event_date DATE NOT NULL
survey_event TEXT NOT NULL -- 'impression' or 'response'
response INTEGER NULL -- 1 through 5 for a response; NULL for an impression
users
-----
user_id BIGINT PRIMARY KEY
reg_date DATE NOT NULL
country TEXT NOT NULL
```
Within a calendar date, a user has at most one `impression` row and at most one `response` row. Every `survey.user_id` has a matching `users` row. A response counts only when the same user also has an impression on the same date; ignore orphan response rows. All `response` rows contain an integer from 1 through 5.
Write one PostgreSQL query that returns one row for each `event_date` and `country` with at least one survey impression. Return:
- `survey_date`
- `country`
- `impressed_users`: number of distinct users with an impression that day
- `responding_users`: number of those impressed users who also responded that day
- `response_rate`: `responding_users / impressed_users` as a decimal between 0 and 1
Preserve numeric precision rather than formatting the rate as text or a percentage. Order the result by `survey_date` ascending and `country` ascending.
Quick Answer: Work through a PostgreSQL survey-analysis task that compares impressions and responses by date and country. It exercises careful joins, distinct-user counting, orphan-event handling, numeric rate calculation, output grain, and deterministic ordering.