Write left-join queries with tricky filters

Q: Write left-join queries with tricky filters

This is a Data Manipulation (SQL/Python) interview question from Block (Square) for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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.

Write left-join queries with tricky filters

Comments (0)