Calculate survey response and quality metrics in SQL

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.