Compare SQL counts, windows, and NULL semantics
Company: ByteDance
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You’re given two tables.
users(id INT, country STRING, created_at DATE)
rows:
1 | US | 2025-08-28
2 | US | 2025-08-30
3 | IN | 2025-08-29
4 | BR | 2025-08-31
orders(order_id INT, user_id INT, amount DECIMAL, created_at DATE)
rows:
101 | 1 | 20.00 | 2025-08-31
102 | 1 | 10.00 | 2025-09-01
103 | 2 | NULL | 2025-09-01
104 | 3 | 15.00 | 2025-09-01
events(user_id INT, event_name STRING, event_time TIMESTAMP)
rows:
1 | video_play | 2025-09-01 10:01:00
1 | like | 2025-09-01 10:02:00
2 | video_play | 2025-09-01 11:00:00
3 | video_play | 2025-09-01 12:00:00
3 | video_play | 2025-09-01 12:05:00
4 | video_play | 2025-09-01 13:00:00
Answer all parts concisely and provide the exact SQL:
(a) Explain and demonstrate, using a single grouped query by users.country, the differences among COUNT(*), COUNT(amount), and COUNT(DISTINCT user_id) on orders joined to users. Why do NULLs matter? Show the three counts side-by-side and ensure the join does not double-count users with multiple orders.
(b) Compute, per country, the top 2 spenders by total order amount using ROW_NUMBER(). Then repeat using RANK() so that ties are included even if more than 2 users are returned. For country = 'US', list which user_ids appear under each method and why.
(c) Show a pitfall where filtering in WHERE vs HAVING changes results: return users with at least 2 video_play events on 2025-09-01 who have zero non-NULL orders. First, incorrectly filter orders.amount IS NULL in WHERE before aggregation; then correct it using HAVING. Explain the difference in row counts.
(d) Compute average spend per active user (active = at least one event of any type on 2025-09-01) by country, guarding against divide-by-zero and NULL amounts. Use COALESCE and NULLIF appropriately and justify your choices.
Overview: This question evaluates understanding of SQL aggregation and NULL semantics, window functions (ROW_NUMBER vs RANK), join behavior to avoid double-counting, and defensive handling of NULLs and divide-by-zero using COALESCE and NULLIF in a data manipulation context.
Read the full ByteDance Data Scientist interview experience this question came from
COUNT(*) vs COUNT(column) vs COUNT(DISTINCT) with NULLs (Grouped by country)
You are given `users` and `orders`.
Write ONE SQL query grouped by `users.country` (joining `orders` to `users`) that shows these three metrics side-by-side:
1) `COUNT(*)`
2) `COUNT(orders.amount)`
3) `COUNT(DISTINCT orders.user_id)`
Explain via the query output why NULLs matter (specifically for `COUNT(orders.amount)`). Make sure your query does not accidentally double-count users when producing the distinct user count (i.e., users with multiple orders should still count once in the distinct user metric).
Tables
users(id INT, country VARCHAR(2), created_at DATE)
orders(order_id INT, user_id INT, amount DECIMAL(10,2), created_at DATE)
Hints
- COUNT(column) ignores NULLs; COUNT(*) does not.
- COUNT(DISTINCT user_id) prevents counting the same user multiple times within a country.
Top-N spenders per country: ROW_NUMBER() vs RANK() (ties)
Using `users` and `orders`, compute each user's total spend (treat NULL amounts as 0 and users with no orders as 0).
Part 1: Per country, return the top 2 users by total spend using `ROW_NUMBER()`.
Part 2: Repeat using `RANK()` so that ties are included even if more than 2 users are returned.
For country = 'US', your results should make it clear which user_ids appear under each method and why (tie-handling difference).
Tables
users(id INT, country VARCHAR(2), created_at DATE)
orders(order_id INT, user_id INT, amount DECIMAL(10,2), created_at DATE)
Hints
- Use a per-user aggregation first, then apply window functions per country.
- ROW_NUMBER breaks ties by your ORDER BY; RANK assigns the same rank to ties and can return more than N rows.
WHERE vs HAVING pitfall with NULL filtering (video_play + no non-NULL orders)
Return users who:
- have at least 2 `video_play` events on 2025-09-01, and
- have zero non-NULL order amounts (i.e., no orders with a non-NULL `amount`; users with only NULL-amount orders or no orders should qualify).
Show the pitfall where filtering `orders.amount IS NULL` in a WHERE clause changes results:
1) First, write an incorrect query that filters `orders.amount IS NULL` in WHERE before aggregation.
2) Then, write the correct query that does NOT filter in WHERE, and instead uses HAVING/aggregation to enforce “zero non-NULL orders”.
Return both outputs in one result set with a `method` column so the difference in row counts is visible.
Tables
users(id INT, country VARCHAR(2), created_at DATE)
orders(order_id INT, user_id INT, amount DECIMAL(10,2), created_at DATE)
events(user_id INT, event_name VARCHAR(50), event_time TIMESTAMP)
Hints
- Filtering on the joined table in WHERE can remove rows before aggregation.
- COUNT(amount) counts only non-NULL amounts; use HAVING on aggregates to enforce “zero non-NULL amounts”.
Average spend per active user by country (COALESCE + NULLIF)
An "active user" is a user with at least one event of any type on 2025-09-01.
Compute, by country, the average spend per active user, where each user's spend is the sum of their order amounts (ignore NULL amounts, and treat users with no orders / all NULL order amounts as 0 spend).
Requirements:
- Use COALESCE to handle NULL amounts and/or missing order rows.
- Use NULLIF to guard against divide-by-zero.
- Return: `country`, `active_user_count`, `total_spend`, `avg_spend_per_active_user`.
Tables
users(id INT, country VARCHAR(2), created_at DATE)
orders(order_id INT, user_id INT, amount DECIMAL(10,2), created_at DATE)
events(user_id INT, event_name VARCHAR(50), event_time TIMESTAMP)
Hints
- Define active users first (distinct user_id from events on the date).
- COALESCE protects against NULL sums; NULLIF protects against dividing by 0.