Calculate survey response and quality metrics in SQL

Compute survey response-rate and quality metrics from event data. Assume "today" = 2025-09-01, and compute over the last 7 days (2025-08-26 to 2025-09-01, inclusive).

Schema (invented):

• impressions(impression_id BIGINT, user_id BIGINT, survey_id INT, ts TIMESTAMP)
• clicks(click_id BIGINT, impression_id BIGINT, ts TIMESTAMP)
• surveys(survey_id INT, version VARCHAR, active_from DATE)
• survey_responses(response_id BIGINT, user_id BIGINT, survey_id INT, score INT, ts TIMESTAMP)

Sample data (minimal):

impressions
| impression_id | user_id | survey_id | ts |
| 1 | 101 | 10 | 2025-08-30 10:00:00 |
| 2 | 102 | 10 | 2025-08-30 10:05:00 |
| 3 | 101 | 11 | 2025-08-31 12:00:00 |
| 4 | 103 | 10 | 2025-09-01 09:00:00 |
| 5 | 104 | 10 | 2025-09-01 09:05:00 |

clicks
| click_id | impression_id | ts |
| 1 | 1 | 2025-08-30 10:01:00 |
| 2 | 1 | 2025-08-30 10:02:00 |
| 3 | 3 | 2025-08-31 12:05:00 |
| 4 | 5 | 2025-09-01 09:06:00 |

surveys
| survey_id | version | active_from |
| 10 | v1 | 2025-08-01 |
| 11 | v2 | 2025-08-25 |

survey_responses
| response_id | user_id | survey_id | score | ts |
| 1 | 101 | 10 | 4 | 2025-08-30 10:03:00 |
| 2 | 101 | 10 | 5 | 2025-08-30 10:10:00 |
| 3 | 101 | 11 | 3 | 2025-08-31 12:06:00 |
| 4 | 104 | 10 | 2 | 2025-09-01 09:07:00 |

Tasks (write precise SQL; assume ANSI SQL syntax):

1. Daily survey response rate by survey version: response_rate = unique_clicked_impressions / total_impressions per calendar date in the 7-day window. Count at most 1 click per impression. Join to surveys to report by version.
2. Overall response rate for the full 7-day window (single number) and the same metric broken out by survey version.
3. Quality metrics by survey version for the same window, using the provided score column in two ways: a) First-score-only: for each user+survey_id, take their chronologically first score in the window; average across those user+survey pairs. b) All-scores: average across all scores in the window (i.e., multiple scores from the same user count multiple times).
4. Ensure that scores are associated with impressions/clicks from the same window. If a response has no corresponding impression within the window, exclude it. If multiple impressions exist for the same user+survey_id, treat responses as eligible if they occur at or after the first click for that user+survey_id on that day.
5. Return: date, survey_version, total_impressions, unique_clicked_impressions, response_rate, avg_score_first_only, avg_score_all. State any assumptions you make explicitly (e.g., timezone, handling of missing joins).

Survey response and quality metrics over a 7-day window

You are given event-level data for a survey system. Using the schema and sample data below, write a single SQL query (ANSI SQL) that computes survey response-rate and quality metrics over the 7-day window FROM 2025-05-26 TO 2025-06-01 (inclusive).

Tables:

• impressions(impression_id BIGINT, user_id BIGINT, survey_id INT, ts TIMESTAMP)
• clicks(click_id BIGINT, impression_id BIGINT, ts TIMESTAMP)
• surveys(survey_id INT, version VARCHAR, active_from DATE)
• survey_responses(response_id BIGINT, user_id BIGINT, survey_id INT, score INT, ts TIMESTAMP)

Requirements:

1. Work only with events whose DATE(ts) is between '2025-05-26' and '2025-06-01' inclusive.
2. For each calendar date in this window and each survey version, compute:
   • total_impressions: number of impressions shown that day for that survey version.
   • unique_clicked_impressions: number of distinct impressions that received at least one click that day (count at most 1 click per impression).
   • response_rate: unique_clicked_impressions / total_impressions.
3. Compute two quality metrics using survey_responses.score for the same date and version: a) avg_score_first_only: For each user_id + survey_id pair, consider their chronologically first eligible response in the 7-day window; include that score only once (on the date it occurred) and average across those first responses. b) avg_score_all: Average across all eligible scores (multiple responses from the same user on the same survey count multiple times).
4. A survey response is eligible only if all of the following hold:
   • The response timestamp ts is within the 7-day window.
   • There is at least one impression within the 7-day window for the same user_id and survey_id.
   • There is at least one click on one of those impressions on the SAME calendar date as the response for that user_id + survey_id.
   • The response ts is greater than or equal to the timestamp of the first such click for that user_id + survey_id on that date. Responses that do not satisfy these conditions must be excluded from both quality metrics.
5. Join to surveys so that output is reported by survey version, not survey_id.
6. Output columns must be:
   • date (DATE) – the calendar date.
   • survey_version (VARCHAR) – the survey version.
   • total_impressions (INT).
   • unique_clicked_impressions (INT).
   • response_rate (DECIMAL or FLOAT).
   • avg_score_first_only (DECIMAL or FLOAT, NULL if no eligible first responses that day).
   • avg_score_all (DECIMAL or FLOAT, NULL if no eligible responses that day).

Assumptions to state in your solution:

• Assume all timestamps are in UTC, and "date" means the UTC calendar date (CAST(ts AS DATE)).
• If a date + version has impressions but no clicks, unique_clicked_impressions should be 0 and response_rate should be 0.
• If a date + version has impressions but no eligible responses, avg_score_first_only and avg_score_all should be NULL.

Write a single SQL query that produces the requested result.

Table impressions

column	type	constraints
impression_id	BIGINT	PRIMARY KEY
user_id	BIGINT	NOT NULL
survey_id	INT	NOT NULL
ts	TIMESTAMP	NOT NULL

Sample data:

impression_id	survey_id	ts	user_id
1	10	2025-05-30 10:00:00	101
2	10	2025-05-30 10:05:00	102
3	11	2025-05-31 12:00:00	101
4	10	2025-06-01 09:00:00	103
5	10	2025-06-01 09:05:00	104
6	10	2025-05-25 09:00:00	101

Table clicks

column	type	constraints
click_id	BIGINT	PRIMARY KEY
impression_id	BIGINT	NOT NULL
ts	TIMESTAMP	NOT NULL

Sample data:

click_id	impression_id	ts
1	1	2025-05-30 10:01:00
2	1	2025-05-30 10:02:00
3	3	2025-05-31 12:05:00
4	5	2025-06-01 09:06:00
5	6	2025-05-25 09:01:00

Table surveys

column	type	constraints
survey_id	INT	PRIMARY KEY
version	VARCHAR(10)	NOT NULL
active_from	DATE	NOT NULL

Sample data:

active_from	survey_id	version
2025-05-01	10	v1
2025-05-25	11	v2

Table survey_responses

column	type	constraints
response_id	BIGINT	PRIMARY KEY
user_id	BIGINT	NOT NULL
survey_id	INT	NOT NULL
score	INT	NOT NULL
ts	TIMESTAMP	NOT NULL

Sample data:

response_id	score	survey_id	ts	user_id
1	4	10	2025-05-30 10:03:00	101
2	5	10	2025-05-30 10:10:00	101
3	3	11	2025-05-31 12:06:00	101
4	2	10	2025-06-01 09:07:00	104
5	1	10	2025-05-29 08:00:00	106
6	5	10	2025-06-01 09:04:00	104

Expected output

One row per calendar date (within 2025-05-26 to 2025-06-01) and survey version that has impressions, with total impressions, distinct clicked impressions, response rate, and two average score metrics based on eligible responses only.

avg_score_all	avg_score_first_only	date	response_rate	survey_version	total_impressions	unique_clicked_impressions
4.5	4	2025-05-30	0.5	v1	2	1
3	3	2025-05-31	1	v2	1	1
2	2	2025-06-01	0.5	v1	2	1

Hints

1. Start by filtering impressions and clicks to the 7-day window in CTEs, and compute the first click per (user_id, survey_id, date).
2. Use a window function (ROW_NUMBER) over eligible responses to identify the first response per user+survey, then aggregate by date and survey version.