Write left-join queries with tricky filters
Problem Description
Assume the current date, 'today', is '2025-09-01'. You are tasked with writing several SQL queries based on the provided schema and data.
Constraints:
- Do NOT use window functions (e.g.,
RANK,ROW_NUMBER). - You MUST use
LEFT JOIN,COUNT(DISTINCT),GROUP BY, andHAVINGas appropriate.
Schema
- users:
user_id(INT PRIMARY KEY),name(TEXT),signup_dt(DATE),country(TEXT) - referrals:
referrer_user_id(INT),referred_user_id(INT),referral_dt(DATE) - orders:
order_id(INT PRIMARY KEY),user_id(INT),order_dt(DATE),amount(DECIMAL(10,2))
Tasks
A) Referrer Performance Metrics
For each referrer_user_id in the referrals table, calculate and return the following metrics:
referred_count: The total count of distinct users referred by this referrer.buyers_last_7d: The count of distinct referred users who placed at least one order between '2025-08-26' and '2025-09-01' (inclusive).revenue_last_7d: The sum of order amounts from orders placed by referred users within the '2025-08-26' to '2025-09-01' window (inclusive). The output should include referrers who have zero buyers or zero revenue in the specified period. Group the results solely byreferrer_user_id.
B) Referred Users with No Orders
List all users who have been referred (i.e., appear as referred_user_id in the referrals table) but have not placed any orders on or before '2025-09-01'.
The output should include referred_user_id and referrer_user_id. You must use a LEFT JOIN to the orders table and HAVING to enforce the zero-order condition.
C) Country-Level Referral Conversion For each country, among users who were referred, compute the following:
referred_users: The total count of distinct users referred in that country.buyers: The count of distinct referred users who placed at least one order on or before '2025-09-01'.conversion_rate: Calculated asbuyers * 1.0 / referred_users, rounded to 2 decimal places. The results should include countries wherebuyersis 0, but exclude countries wherereferred_usersis 0.
D) Handling Non-Existent Filter Values
The business requests metrics filtered specifically for country='UK'.
- Without assuming knowledge of the exact country codes in the data, write the precise SQL query you would run to identify all valid country values present in the
userstable. This is to prevent silently returning zero results due to a non-existent filter value. - Using the appropriate country value identified from the sample data (hint: 'GB' is used, not 'UK'), provide the corrected metrics query.
- Briefly explain in a SQL comment why a naive
WHERE country='UK'could silently return 0 results.
E) Initial Data Exploration
In an interview setting where the interviewer is the only one allowed to execute queries, write the first two exploratory SELECT statements you would ask them to run. The purpose of these queries is to understand the table structures, data types, and identify safe join keys before attempting to write the solutions for parts A-D.
AI interview prep
Core dilemma
The central engineering conflict is ensuring accurate aggregation and filtering of data across multiple joined tables, particularly when dealing with `LEFT JOIN`s and `NULL` values. This involves correctly applying date range conditions, handling distinct counts, and managing zero-value metrics versus `NULL`s, all while adhering to specific SQL construct constraints.
3 tradeoffs and 3 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Write left-join queries with tricky filters
Problem Description
Assume the current date, 'today', is '2025-09-01'. You are tasked with writing several SQL queries based on the provided schema and data.
Constraints:
- Do NOT use window functions (e.g.,
RANK,ROW_NUMBER). - You MUST use
LEFT JOIN,COUNT(DISTINCT),GROUP BY, andHAVINGas appropriate.
Schema
- users:
user_id(INT PRIMARY KEY),name(TEXT),signup_dt(DATE),country(TEXT) - referrals:
referrer_user_id(INT),referred_user_id(INT),referral_dt(DATE) - orders:
order_id(INT PRIMARY KEY),user_id(INT),order_dt(DATE),amount(DECIMAL(10,2))
Tasks
A) Referrer Performance Metrics
For each referrer_user_id in the referrals table, calculate and return the following metrics:
referred_count: The total count of distinct users referred by this referrer.buyers_last_7d: The count of distinct referred users who placed at least one order between '2025-08-26' and '2025-09-01' (inclusive).revenue_last_7d: The sum of order amounts from orders placed by referred users within the '2025-08-26' to '2025-09-01' window (inclusive). The output should include referrers who have zero buyers or zero revenue in the specified period. Group the results solely byreferrer_user_id.
B) Referred Users with No Orders
List all users who have been referred (i.e., appear as referred_user_id in the referrals table) but have not placed any orders on or before '2025-09-01'.
The output should include referred_user_id and referrer_user_id. You must use a LEFT JOIN to the orders table and HAVING to enforce the zero-order condition.
C) Country-Level Referral Conversion For each country, among users who were referred, compute the following:
referred_users: The total count of distinct users referred in that country.buyers: The count of distinct referred users who placed at least one order on or before '2025-09-01'.conversion_rate: Calculated asbuyers * 1.0 / referred_users, rounded to 2 decimal places. The results should include countries wherebuyersis 0, but exclude countries wherereferred_usersis 0.
D) Handling Non-Existent Filter Values
The business requests metrics filtered specifically for country='UK'.
- Without assuming knowledge of the exact country codes in the data, write the precise SQL query you would run to identify all valid country values present in the
userstable. This is to prevent silently returning zero results due to a non-existent filter value. - Using the appropriate country value identified from the sample data (hint: 'GB' is used, not 'UK'), provide the corrected metrics query.
- Briefly explain in a SQL comment why a naive
WHERE country='UK'could silently return 0 results.
E) Initial Data Exploration
In an interview setting where the interviewer is the only one allowed to execute queries, write the first two exploratory SELECT statements you would ask them to run. The purpose of these queries is to understand the table structures, data types, and identify safe join keys before attempting to write the solutions for parts A-D.