Compute survey rates and bias-correct ratings
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates proficiency in data manipulation and analytics, specifically SQL window functions, joins, time-windowed de-duplication, aggregation of response and completion rates, and statistical reweighting to correct non-response bias.
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
Tables
users(user_id INT, country VARCHAR(2), 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)
Hints
- Deduplicate invites first: MIN(sent_at) per (user_id, survey_id) within the invite window.
- 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).
Tables
users(user_id INT, country VARCHAR(2), 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)
Hints
- Compute the invite distribution by country from deduped in-window invites; compute the response distribution by country from eligible complete responses.
- 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).