Coding questions · ByteDance
Compare SQL counts, windows, and NULL semantics
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.
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.
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:
COUNT(*)COUNT(orders.amount)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).
Table users
| column | type | constraints |
|---|---|---|
| id | INT | PRIMARY KEY, NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| country | created_at | id |
|---|---|---|
| US | 2025-08-28 | 1 |
| US | 2025-08-30 | 2 |
| IN | 2025-08-29 | 3 |
| BR | 2025-08-31 | 4 |
| US | 2025-08-30 | 5 |
| IN | 2025-08-30 | 6 |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| amount | DECIMAL(10,2) | |
| created_at | DATE | NOT NULL |
Sample data:
| amount | created_at | order_id | user_id |
|---|---|---|---|
| 20 | 2025-08-31 | 101 | 1 |
| 10 | 2025-09-01 | 102 | 1 |
| NULL | 2025-09-01 | 103 | 2 |
| 15 | 2025-09-01 | 104 | 3 |
| NULL | 2025-09-01 | 106 | 6 |
| 5 | 2025-09-01 | 107 | 6 |
Expected output
One row per country that has at least one order (because of the inner join). Shows (1) total joined rows (orders) via COUNT(*), (2) number of non-NULL amounts via COUNT(amount), and (3) distinct purchasers via COUNT(DISTINCT user_id).
| count_amount | count_distinct_user_id | count_star | country |
|---|---|---|---|
| 2 | 2 | 3 | IN |
| 2 | 2 | 3 | US |
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).
Table users
| column | type | constraints |
|---|---|---|
| id | INT | PRIMARY KEY, NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| country | created_at | id |
|---|---|---|
| US | 2025-08-28 | 1 |
| US | 2025-08-30 | 2 |
| IN | 2025-08-29 | 3 |
| BR | 2025-08-31 | 4 |
| US | 2025-08-30 | 5 |
| IN | 2025-08-30 | 6 |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| amount | DECIMAL(10,2) | |
| created_at | DATE | NOT NULL |
Sample data:
| amount | created_at | order_id | user_id |
|---|---|---|---|
| 20 | 2025-08-31 | 101 | 1 |
| 10 | 2025-09-01 | 102 | 1 |
| NULL | 2025-09-01 | 103 | 2 |
| 15 | 2025-09-01 | 104 | 3 |
| NULL | 2025-09-01 | 106 | 6 |
| 5 | 2025-09-01 | 107 | 6 |
Expected output
Two result sets are expected: (A) top 2 per country with ROW_NUMBER (exactly 2 rows per country when at least 2 users exist), and (B) top 2 per country with RANK (may return more than 2 rows due to ties). In US, users 2 and 5 tie at 0 spend, so ROW_NUMBER returns only one of them as #2 (based on deterministic tie-break), while RANK returns both.
| country | method | pos | total_spend | user_id |
|---|---|---|---|---|
| BR | rank | 1 | 0.00 | 4 |
| IN | rank | 1 | 15.00 | 3 |
| IN | rank | 2 | 5.00 | 6 |
| US | rank | 1 | 30.00 | 1 |
| US | rank | 2 | 0.00 | 2 |
| US | rank | 2 | 0.00 | 5 |
| BR | row_number | 1 | 0.00 | 4 |
| IN | row_number | 1 | 15.00 | 3 |
| IN | row_number | 2 | 5.00 | 6 |
| US | row_number | 1 | 30.00 | 1 |
| US | row_number | 2 | 0.00 | 2 |
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_playevents 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:
- First, write an incorrect query that filters
orders.amount IS NULLin WHERE before aggregation. - 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.
Table users
| column | type | constraints |
|---|---|---|
| id | INT | PRIMARY KEY, NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| country | created_at | id |
|---|---|---|
| US | 2025-08-28 | 1 |
| US | 2025-08-30 | 2 |
| IN | 2025-08-29 | 3 |
| BR | 2025-08-31 | 4 |
| US | 2025-08-30 | 5 |
| IN | 2025-08-30 | 6 |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| amount | DECIMAL(10,2) | |
| created_at | DATE | NOT NULL |
Sample data:
| amount | created_at | order_id | user_id |
|---|---|---|---|
| 20 | 2025-08-31 | 101 | 1 |
| 10 | 2025-09-01 | 102 | 1 |
| NULL | 2025-09-01 | 103 | 2 |
| 15 | 2025-09-01 | 104 | 3 |
| NULL | 2025-09-01 | 106 | 6 |
| 5 | 2025-09-01 | 107 | 6 |
Table events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_name | VARCHAR(50) | NOT NULL |
| event_time | TIMESTAMP | NOT NULL |
Sample data:
| event_name | event_time | user_id |
|---|---|---|
| video_play | 2025-09-01 10:01:00 | 1 |
| like | 2025-09-01 10:02:00 | 1 |
| video_play | 2025-09-01 11:00:00 | 2 |
| video_play | 2025-09-01 11:05:00 | 2 |
| video_play | 2025-09-01 12:00:00 | 3 |
| video_play | 2025-09-01 12:05:00 | 3 |
| video_play | 2025-09-01 13:00:00 | 4 |
| video_play | 2025-09-01 14:00:00 | 5 |
| video_play | 2025-09-01 14:10:00 | 5 |
| video_play | 2025-09-01 15:00:00 | 6 |
| video_play | 2025-09-01 15:05:00 | 6 |
Expected output
The incorrect method returns an extra user (user_id=6) because filtering o.amount IS NULL in WHERE removes the non-NULL order row before aggregation, making the user appear to have no non-NULL amounts. The correct method keeps all order rows and uses HAVING to ensure zero non-NULL amounts.
| country | method | user_id |
|---|---|---|
| US | incorrect_where_filter | 2 |
| US | incorrect_where_filter | 5 |
| IN | incorrect_where_filter | 6 |
| US | correct_having | 2 |
| US | correct_having | 5 |
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.
Table users
| column | type | constraints |
|---|---|---|
| id | INT | PRIMARY KEY, NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| country | created_at | id |
|---|---|---|
| US | 2025-08-28 | 1 |
| US | 2025-08-30 | 2 |
| IN | 2025-08-29 | 3 |
| BR | 2025-08-31 | 4 |
| US | 2025-08-30 | 5 |
| IN | 2025-08-30 | 6 |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| amount | DECIMAL(10,2) | |
| created_at | DATE | NOT NULL |
Sample data:
| amount | created_at | order_id | user_id |
|---|---|---|---|
| 20 | 2025-08-31 | 101 | 1 |
| 10 | 2025-09-01 | 102 | 1 |
| NULL | 2025-09-01 | 103 | 2 |
| 15 | 2025-09-01 | 104 | 3 |
| NULL | 2025-09-01 | 106 | 6 |
| 5 | 2025-09-01 | 107 | 6 |
Table events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_name | VARCHAR(50) | NOT NULL |
| event_time | TIMESTAMP | NOT NULL |
Sample data:
| event_name | event_time | user_id |
|---|---|---|
| video_play | 2025-09-01 10:01:00 | 1 |
| like | 2025-09-01 10:02:00 | 1 |
| video_play | 2025-09-01 11:00:00 | 2 |
| video_play | 2025-09-01 11:05:00 | 2 |
| video_play | 2025-09-01 12:00:00 | 3 |
| video_play | 2025-09-01 12:05:00 | 3 |
| video_play | 2025-09-01 13:00:00 | 4 |
| video_play | 2025-09-01 14:00:00 | 5 |
| video_play | 2025-09-01 14:10:00 | 5 |
| video_play | 2025-09-01 15:00:00 | 6 |
| video_play | 2025-09-01 15:05:00 | 6 |
Expected output
One row per country among active users on 2025-09-01. total_spend sums non-NULL order amounts for active users, while avg_spend_per_active_user divides by the number of active users in the country (guarded by NULLIF).
| active_user_count | avg_spend_per_active_user | country | total_spend |
|---|---|---|---|
| 1 | 0 | BR | 0 |
| 2 | 10 | IN | 20 |
| 3 | 10 | US | 30 |
Hints
- Define active users first (distinct user_id from events on the date).
- COALESCE protects against NULL sums; NULLIF protects against dividing by 0.