Coding questions · Block (Square)

Write left-join queries with tricky filters

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.

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.

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.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL
signup_dt	DATE	NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	name	signup_dt	user_id
US	Alice	2025-05-20	1
CA	Bob	2025-05-22	2
CN	Chen	2025-05-25	3
US	Diana	2025-05-29	4
GB	Eva	2025-05-31	5

Table `referrals`

column	type	constraints
referrer_user_id	INT	NOT NULL
referred_user_id	INT	NOT NULL
referral_dt	DATE	NOT NULL

Sample data:

referral_dt	referred_user_id	referrer_user_id
2025-05-22	2	1
2025-05-25	3	1
2025-05-29	4	2
2025-05-31	5	99

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
order_dt	DATE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	order_dt	order_id	user_id
50	2025-05-30	101	2
25	2025-06-01	102	2
10	2025-06-01	103	3
20	2025-05-29	104	4

Expected output

One row per referrer_user_id that appears in referrals, showing referred_count, buyers_last_7d (distinct referred users with >=1 order in 2025-05-26..2025-06-01), and revenue_last_7d (sum of amounts in that window).

buyers_last_7d	referred_count	referrer_user_id	revenue_last_7d
2	2	1	85
1	1	2	20
0	1	99	0

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.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL
signup_dt	DATE	NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	name	signup_dt	user_id
US	Alice	2025-05-20	1
CA	Bob	2025-05-22	2
CN	Chen	2025-05-25	3
US	Diana	2025-05-29	4
GB	Eva	2025-05-31	5

Table `referrals`

column	type	constraints
referrer_user_id	INT	NOT NULL
referred_user_id	INT	NOT NULL
referral_dt	DATE	NOT NULL

Sample data:

referral_dt	referred_user_id	referrer_user_id
2025-05-22	2	1
2025-05-25	3	1
2025-05-29	4	2
2025-05-31	5	99

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
order_dt	DATE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	order_dt	order_id	user_id
50	2025-05-30	101	2
25	2025-06-01	102	2
10	2025-06-01	103	3
20	2025-05-29	104	4

Expected output

All (referred_user_id, referrer_user_id) pairs where the referred user has no orders with order_dt <= '2025-06-01'.

referred_user_id	referrer_user_id
5	99

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.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL
signup_dt	DATE	NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	name	signup_dt	user_id
US	Alice	2025-05-20	1
CA	Bob	2025-05-22	2
CN	Chen	2025-05-25	3
US	Diana	2025-05-29	4
GB	Eva	2025-05-31	5

Table `referrals`

column	type	constraints
referrer_user_id	INT	NOT NULL
referred_user_id	INT	NOT NULL
referral_dt	DATE	NOT NULL

Sample data:

referral_dt	referred_user_id	referrer_user_id
2025-05-22	2	1
2025-05-25	3	1
2025-05-29	4	2
2025-05-31	5	99

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
order_dt	DATE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	order_dt	order_id	user_id
50	2025-05-30	101	2
25	2025-06-01	102	2
10	2025-06-01	103	3
20	2025-05-29	104	4

Expected output

One row per country that has at least one referred user, showing referred_users, buyers, and conversion_rate (rounded to 2 decimals).

buyers	conversion_rate	country	referred_users
1	1	CA	1
1	1	CN	1
0	0	GB	1
1	1	US	1

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:

Write the exact SQL query you would run to surface the valid country values in users.
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.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL
signup_dt	DATE	NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	name	signup_dt	user_id
US	Alice	2025-05-20	1
CA	Bob	2025-05-22	2
CN	Chen	2025-05-25	3
US	Diana	2025-05-29	4
GB	Eva	2025-05-31	5

Table `referrals`

column	type	constraints
referrer_user_id	INT	NOT NULL
referred_user_id	INT	NOT NULL
referral_dt	DATE	NOT NULL

Sample data:

referral_dt	referred_user_id	referrer_user_id
2025-05-22	2	1
2025-05-25	3	1
2025-05-29	4	2
2025-05-31	5	99

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
order_dt	DATE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	order_dt	order_id	user_id
50	2025-05-30	101	2
25	2025-06-01	102	2
10	2025-06-01	103	3
20	2025-05-29	104	4

Expected output

A single combined output that includes (a) the distinct country codes present in users, and (b) the corrected referred-user conversion metrics for the correct UK/GB country code from the data.

buyers	conversion_rate	country	referred_users	result_set
NULL	NULL	CA	NULL	distinct_countries
NULL	NULL	CN	NULL	distinct_countries
NULL	NULL	GB	NULL	distinct_countries
NULL	NULL	US	NULL	distinct_countries
0	0	GB	1	gb_metrics

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.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL
signup_dt	DATE	NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	name	signup_dt	user_id
US	Alice	2025-05-20	1
CA	Bob	2025-05-22	2
CN	Chen	2025-05-25	3
US	Diana	2025-05-29	4
GB	Eva	2025-05-31	5

Table `referrals`

column	type	constraints
referrer_user_id	INT	NOT NULL
referred_user_id	INT	NOT NULL
referral_dt	DATE	NOT NULL

Sample data:

referral_dt	referred_user_id	referrer_user_id
2025-05-22	2	1
2025-05-25	3	1
2025-05-29	4	2
2025-05-31	5	99

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
order_dt	DATE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	order_dt	order_id	user_id
50	2025-05-30	101	2
25	2025-06-01	102	2
10	2025-06-01	103	3
20	2025-05-29	104	4

Expected output

A combined sample showing (1) a small preview of users and (2) a small preview of referrals (with a result_set label), which helps confirm join keys and cardinality assumptions.

country	name	referral_dt	referred_user_id	referrer_user_id	result_set	signup_dt	user_id
US	Alice	NULL	NULL	NULL	users_sample	2025-05-20	1
CA	Bob	NULL	NULL	NULL	users_sample	2025-05-22	2
CN	Chen	NULL	NULL	NULL	users_sample	2025-05-25	3
US	Diana	NULL	NULL	NULL	users_sample	2025-05-29	4
GB	Eva	NULL	NULL	NULL	users_sample	2025-05-31	5
NULL	NULL	2025-05-22	2	1	referrals_sample	NULL	NULL
NULL	NULL	2025-05-25	3	1	referrals_sample	NULL	NULL
NULL	NULL	2025-05-29	4	2	referrals_sample	NULL	NULL
NULL	NULL	2025-05-31	5	99	referrals_sample	NULL	NULL

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.

Referral funnel metrics per referrer (LEFT JOIN with date filter)

Table users

Table referrals

Table orders

Expected output

Hints

Referred users with zero orders (LEFT JOIN + HAVING)

Table users

Table referrals

Table orders

Expected output

Hints

Country-level conversion for referred users

Table users

Table referrals

Table orders

Expected output

Hints

Avoiding silent zeroes from invalid country filters (discover + correct)

Table users

Table referrals

Table orders

Expected output

Hints

First two exploratory SELECTs to validate shapes and join keys

Table users

Table referrals

Table orders

Expected output

Hints

Table `users`

Table `referrals`

Table `orders`

Table `users`

Table `referrals`

Table `orders`

Table `users`

Table `referrals`

Table `orders`

Table `users`

Table `referrals`

Table `orders`

Table `users`

Table `referrals`

Table `orders`