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:
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.