Write left-join queries with tricky filters
Company: Block (Square)
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Assume 'today' = '2025-09-01'. Do NOT use window functions (e.g., RANK). Use LEFT JOIN, COUNT(DISTINCT), GROUP BY, HAVING as 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))
Small ASCII samples:
users
+---------+-------+------------+---------+
| user_id | name | signup_dt | country |
+---------+-------+------------+---------+
| 1 | Alice | 2025-08-20 | US |
| 2 | Bob | 2025-08-22 | CA |
| 3 | Chen | 2025-08-25 | CN |
| 4 | Diana | 2025-08-29 | US |
| 5 | Eva | 2025-08-31 | GB |
+---------+-------+------------+---------+
referrals
+------------------+------------------+------------+
| referrer_user_id | referred_user_id | referral_dt|
+------------------+------------------+------------+
| 1 | 2 | 2025-08-22 |
| 1 | 3 | 2025-08-25 |
| 2 | 4 | 2025-08-29 |
| 99 | 5 | 2025-08-31 |
+------------------+------------------+------------+
orders
+----------+---------+------------+--------+
| order_id | user_id | order_dt | amount |
+----------+---------+------------+--------+
| 101 | 2 | 2025-08-30 | 50.00 |
| 102 | 2 | 2025-09-01 | 25.00 |
| 103 | 3 | 2025-09-01 | 10.00 |
| 104 | 4 | 2025-08-29 | 20.00 |
+----------+---------+------------+--------+
Answer the following (write exact SQL for each):
A) For each referrer_user_id in referrals, return: referred_count (COUNT(DISTINCT referred_user_id)), buyers_last_7d (COUNT DISTINCT of referred users who placed an order between '2025-08-26' and '2025-09-01' inclusive), and revenue_last_7d (SUM of order amounts in that window). Include referrers with zero buyers/revenue. Group only by referrer_user_id.
B) List all users who were referred (i.e., appear as referred_user_id) but have zero orders on or before '2025-09-01'. Output: referred_user_id, referrer_user_id. Use a LEFT JOIN to orders and HAVING to enforce zero.
C) For each country, among users who were referred, compute: referred_users, buyers (users with at least one order on or before '2025-09-01'), and conversion_rate = buyers*1.0/referred_users rounded to 2 decimals. Include countries with buyers=0 but exclude countries with referred_users=0.
D) The business asks for metrics filtered to country='UK'. Without assuming the code list, first show the exact query you would run to surface valid values (so you avoid returning 0 due to a non-existent filter), then provide the corrected metrics query using the appropriate value from the data (hint: sample data uses 'GB', not 'UK'). Briefly explain in a SQL comment why a naive WHERE country='UK' can silently return 0.
E) Given an interview setting where the interviewer may be the only person allowed to execute queries, write the first two exploratory SELECTs you would ask them to run so you can understand table shape and safe join keys before attempting parts A–D.
Overview: This question evaluates SQL data-manipulation skills including LEFT JOIN semantics, aggregation functions like COUNT(DISTINCT) and SUM, GROUP BY/HAVING logic, and date-window filtering for referral and order analytics.
Referral funnel metrics per referrer (LEFT JOIN with date filter)
Assume “today” = '2025-06-01'. Do NOT use window functions.
For each referrer_user_id that appears in the referrals table, return exactly one row with:
- referred_count = COUNT(DISTINCT referred_user_id)
- buyers_last_7d = COUNT(DISTINCT referred_user_id) who placed at least one order between '2025-05-26' and '2025-06-01' (inclusive)
- revenue_last_7d = SUM(order.amount) for orders in that same date window
Requirements:
- Use LEFT JOIN so referrers with zero buyers/revenue still appear.
- Group only by referrer_user_id.
- Use COUNT(DISTINCT), GROUP BY, and date predicates appropriately.
Tables
users(user_id INT, name VARCHAR(50), signup_dt DATE, country VARCHAR(2))
referrals(referrer_user_id INT, referred_user_id INT, referral_dt DATE)
orders(order_id INT, user_id INT, order_dt DATE, amount DECIMAL(10,2))
Hints
- Put the date filter for orders in the LEFT JOIN condition, not the WHERE clause.
- Use COUNT(DISTINCT CASE WHEN ... THEN referred_user_id END) to count distinct buyers.
Referred users with zero orders (LEFT JOIN + HAVING)
Assume “today” = '2025-06-01'. Do NOT use window functions.
List all users who were referred (i.e., appear as referrals.referred_user_id) but have zero orders on or before '2025-06-01'.
Output columns:
- referred_user_id
- referrer_user_id
Requirements:
- Use a LEFT JOIN to orders.
- Use GROUP BY and HAVING to enforce the “zero orders” condition.
Tables
users(user_id INT, name VARCHAR(50), signup_dt DATE, country VARCHAR(2))
referrals(referrer_user_id INT, referred_user_id INT, referral_dt DATE)
orders(order_id INT, user_id INT, order_dt DATE, amount DECIMAL(10,2))
Hints
- Apply the date predicate in the JOIN so users with no orders remain NULL-extended.
- Use HAVING COUNT(o.order_id) = 0 after grouping.
Country-level conversion for referred users
Assume “today” = '2025-06-01'. Do NOT use window functions.
For each country, among users who were referred (i.e., users whose user_id appears as referrals.referred_user_id), compute:
- referred_users = COUNT(DISTINCT referred users)
- buyers = COUNT(DISTINCT referred users who have at least one order on or before '2025-06-01')
- conversion_rate = buyers * 1.0 / referred_users, rounded to 2 decimals
Requirements:
- Include countries with buyers = 0.
- Exclude countries with referred_users = 0.
Tables
users(user_id INT, name VARCHAR(50), signup_dt DATE, country VARCHAR(2))
referrals(referrer_user_id INT, referred_user_id INT, referral_dt DATE)
orders(order_id INT, user_id INT, order_dt DATE, amount DECIMAL(10,2))
Hints
- Start from referrals and join to users to ensure you only consider referred users.
- Use LEFT JOIN to orders so countries with 0 buyers still remain.
Avoiding silent zeroes from invalid country filters (discover + correct)
Assume “today” = '2025-06-01'. Do NOT use window functions.
The business asks for referred-user conversion metrics filtered to country = 'UK'. However, you should not assume which country codes exist.
Task:
1) Write the exact SQL query you would run to surface the valid country values in users.
2) Then write the corrected metrics query using the appropriate value from the data (hint: the sample data uses 'GB', not 'UK').
Metrics definition (for the corrected country code only): among users who were referred, compute referred_users, buyers (>=1 order on or before '2025-06-01'), and conversion_rate rounded to 2 decimals.
Include a brief explanation as a SQL comment describing why a naive WHERE country='UK' can silently return 0.
Tables
users(user_id INT, name VARCHAR(50), signup_dt DATE, country VARCHAR(2))
referrals(referrer_user_id INT, referred_user_id INT, referral_dt DATE)
orders(order_id INT, user_id INT, order_dt DATE, amount DECIMAL(10,2))
Hints
- First discover valid filter values via SELECT DISTINCT country FROM users.
- A wrong filter value usually produces an empty set, not an error.
First two exploratory SELECTs to validate shapes and join keys
Before solving parts involving referrals and orders, you want to understand table shape and safe join keys.
Write the first two exploratory SELECT queries you would ask an interviewer (who runs queries for you) to execute.
Requirements:
- The two queries should help validate column names/types and the join keys you plan to use (e.g., users.user_id, referrals.referred_user_id, orders.user_id).
- Use simple SELECT statements (no window functions).
Note: For this exercise, return the two exploratory result sets combined into one output using UNION ALL with a result_set label.
Tables
users(user_id INT, name VARCHAR(50), signup_dt DATE, country VARCHAR(2))
referrals(referrer_user_id INT, referred_user_id INT, referral_dt DATE)
orders(order_id INT, user_id INT, order_dt DATE, amount DECIMAL(10,2))
Hints
- A good first step is to preview a few rows from the tables you will join.
- Include the join key columns in your preview output so you can sanity-check relationships.