Compute survey rates and bias-correct ratings

Today is 2025-09-01. Use the schema and sample data below to answer A and B with SQL (standard SQL; you may use CTEs and window functions). Assume timestamps are UTC and exclude test users. Schema:

• users(user_id INT, country STRING, is_test BOOLEAN)
• survey_invites(invite_id INT, user_id INT, survey_id INT, sent_at TIMESTAMP)
• survey_responses(response_id INT, user_id INT, survey_id INT, answered_at TIMESTAMP, is_complete BOOLEAN, rating INT)

Sample tables:

users
user_id | country | is_test
1 | US | false
2 | IN | false
3 | US | false
4 | BR | true
5 | IN | false

survey_invites
invite_id | user_id | survey_id | sent_at
100 | 1 | 10 | 2025-08-26 10:00:00
101 | 2 | 10 | 2025-08-27 09:00:00
102 | 3 | 10 | 2025-08-28 12:00:00
103 | 1 | 11 | 2025-08-20 08:00:00
104 | 5 | 10 | 2025-08-29 14:00:00
105 | 4 | 10 | 2025-08-28 15:00:00
106 | 2 | 11 | 2025-08-30 11:00:00
107 | 2 | 10 | 2025-08-27 10:00:00

survey_responses
response_id | user_id | survey_id | answered_at | is_complete | rating
200 | 1 | 10 | 2025-08-26 10:05:00 | true | 4
201 | 2 | 10 | 2025-08-30 09:20:00 | false | 3
202 | 2 | 10 | 2025-09-02 08:00:00 | true | 5
203 | 3 | 10 | 2025-08-28 13:00:00 | true | 2
204 | 5 | 10 | 2025-09-01 15:00:00 | true | 4
205 | 2 | 11 | 2025-08-30 12:00:00 | true | 5

A) For invites sent in the last 7 days (2025-08-25 through 2025-09-01 inclusive), compute per survey_id: response_rate and completion_rate. Rules: (i) Only non-test users; (ii) If a user receives multiple invites to the same survey, use the earliest invite in-window; (iii) Count a user as 'responded' if they have any response within 7 days after their earliest in-window invite and with answered_at <= 2025-09-01 23:59:59; (iv) 'Completed' requires is_complete = true under the same window. Return survey_id, invited_users, responded_users, completed_users, response_rate, completion_rate.

B) For survey_id = 10 over the same 7-day invite window, compute a country-reweighted average rating that matches the invite country distribution to correct non-response bias. Use only complete responses within 7 days after the user's earliest in-window invite and with answered_at <= 2025-09-01 23:59:59. Let w_country = (invite_share_country) / (response_share_country). Output weighted_avg_rating rounded to 3 decimals and a breakdown table with country, invites, responses, invite_share, response_share, weight, and weighted contribution.

Survey response rate and completion rate for last 7 days of invites

Assume the fixed 'current date' is 2025-06-01 (UTC). For invites sent from 2025-05-26 00:00:00 through 2025-06-01 23:59:59 (inclusive), compute per survey_id the response_rate and completion_rate.

Rules:

1. Exclude test users (users.is_test = true).
2. If a user receives multiple invites to the same survey within the invite window, use only the earliest invite within the window.
3. Count a user as 'responded' if they have ANY response for that survey where answered_at is between their earliest in-window invite time and 7 days after it (inclusive), AND answered_at <= 2025-06-01 23:59:59.
4. Count a user as 'completed' if they have ANY response meeting the responded window rules AND is_complete = true.

Return: survey_id, invited_users, responded_users, completed_users, response_rate, completion_rate.

• response_rate = responded_users / invited_users
• completion_rate = completed_users / invited_users

Table users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL
is_test	BOOLEAN	NOT NULL

Sample data:

country	is_test	user_id
US	false	1
IN	false	2
US	false	3
BR	true	4
IN	false	5

Table survey_invites

column	type	constraints
invite_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
survey_id	INT	NOT NULL
sent_at	TIMESTAMP	NOT NULL

Sample data:

invite_id	sent_at	survey_id	user_id
100	2025-05-26 10:00:00	10	1
101	2025-05-27 09:00:00	10	2
102	2025-05-28 12:00:00	10	3
103	2025-05-20 08:00:00	11	1
104	2025-05-29 14:00:00	10	5
105	2025-05-28 15:00:00	10	4
106	2025-05-30 11:00:00	11	2
107	2025-05-27 10:00:00	10	2

Table survey_responses

column	type	constraints
response_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
survey_id	INT	NOT NULL
answered_at	TIMESTAMP	NOT NULL
is_complete	BOOLEAN	NOT NULL
rating	INT	NOT NULL

Sample data:

answered_at	is_complete	rating	response_id	survey_id	user_id
2025-05-26 10:05:00	true	4	200	10	1
2025-05-30 09:20:00	false	3	201	10	2
2025-06-02 08:00:00	true	5	202	10	2
2025-05-28 13:00:00	true	2	203	10	3
2025-06-01 15:00:00	true	4	204	10	5
2025-05-30 12:00:00	true	5	205	11	2

Expected output

One row per survey_id that had at least one (deduped) in-window invite to a non-test user, with counts of invited/responded/completed users and the corresponding rates.

completed_users	completion_rate	invited_users	responded_users	response_rate	survey_id
3	0.75	4	4	1	10
1	1	1	1	1	11

Hints

1. Deduplicate invites first: MIN(sent_at) per (user_id, survey_id) within the invite window.
2. Use EXISTS / flags to turn 'any response within 7 days' into 0/1 per user before aggregating.

Country reweighted average rating to correct non-response bias (survey_id = 10)

Assume the fixed 'current date' is 2025-06-01 (UTC). For invites sent from 2025-05-26 00:00:00 through 2025-06-01 23:59:59 (inclusive), compute a country-reweighted average rating for survey_id = 10 that matches the invite country distribution.

Rules:

1. Exclude test users.
2. If a user receives multiple invites to survey_id = 10 within the invite window, use only the earliest invite within the window.
3. Use only COMPLETE responses (is_complete = true) within 7 days after the user's earliest in-window invite AND with answered_at <= 2025-06-01 23:59:59.
4. Compute weights per country: w_country = (invite_share_country) / (response_share_country), where shares are computed over the invite set and response set respectively.

Output a breakdown table with:

• country, invites, responses, invite_share, response_share, weight,
• weighted_contribution (defined as weight * sum_of_ratings_in_country), and also include weighted_avg_rating (rounded to 3 decimals) as a column (it may repeat on each row).

Table users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL
is_test	BOOLEAN	NOT NULL

Sample data:

country	is_test	user_id
US	false	1
IN	false	2
US	false	3
BR	true	4
IN	false	5

Table survey_invites

column	type	constraints
invite_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
survey_id	INT	NOT NULL
sent_at	TIMESTAMP	NOT NULL

Sample data:

invite_id	sent_at	survey_id	user_id
100	2025-05-26 10:00:00	10	1
101	2025-05-27 09:00:00	10	2
102	2025-05-28 12:00:00	10	3
103	2025-05-20 08:00:00	11	1
104	2025-05-29 14:00:00	10	5
105	2025-05-28 15:00:00	10	4
106	2025-05-30 11:00:00	11	2
107	2025-05-27 10:00:00	10	2

Table survey_responses

column	type	constraints
response_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
survey_id	INT	NOT NULL
answered_at	TIMESTAMP	NOT NULL
is_complete	BOOLEAN	NOT NULL
rating	INT	NOT NULL

Sample data:

answered_at	is_complete	rating	response_id	survey_id	user_id
2025-05-26 10:05:00	true	4	200	10	1
2025-05-30 09:20:00	false	3	201	10	2
2025-06-02 08:00:00	true	5	202	10	2
2025-05-28 13:00:00	true	2	203	10	3
2025-06-01 15:00:00	true	4	204	10	5
2025-05-30 12:00:00	true	5	205	11	2

Expected output

One row per country for survey_id = 10, showing invite and (eligible complete) response distributions, the computed reweighting factor, per-country weighted contribution, and the final weighted average rating (repeated on each row).

country	invite_share	invites	response_share	responses	weight	weighted_avg_rating	weighted_contribution
IN	0.5	2	0.333333	1	1.5	3.5	6
US	0.5	2	0.666667	2	0.75	3.5	4.5

Hints

1. Compute the invite distribution by country from deduped in-window invites; compute the response distribution by country from eligible complete responses.
2. Use w_country = invite_share / response_share, then compute weighted_avg = SUM(w * rating) / SUM(w) across response rows (or equivalently via country-level sums).