Coding questions · Meta
Write SQL filtering, grouping, CASE, UNION tasks
This question evaluates proficiency with SQL filtering, aggregation, conditional logic, and set operations—specifically skills around WHERE filtering, GROUP BY/HAVING, CASE bucketing, FLOOR on aggregates, and UNION versus UNION ALL—within the Data Manipulation (SQL/Python) domain and emphasizes practical application of ANSI SQL.
Use the following schema and sample data to answer all parts. Assume standard ANSI SQL and that amounts are DECIMAL.
Table: orders
+----------+---------+---------+---------+------------+----------+
| order_id | user_id | channel | amount | created_at | status |
+----------+---------+---------+---------+------------+----------+
| 1 | 1 | web | 19.99 | 2025-08-29 | paid |
| 2 | 1 | store | 10.00 | 2025-09-01 | paid |
| 3 | 2 | web | 5.00 | 2025-09-01 | paid |
| 4 | 2 | store | 5.00 | 2025-09-01 | pending |
| 5 | 3 | web | 100.49 | 2025-09-01 | paid |
| 6 | 3 | web | 3.01 | 2025-09-02 | refunded |
+----------+---------+---------+---------+------------+----------+
a) Filter with WHERE: Return order_id for all orders on 2025-09-01 that are paid and have amount >= 10. Only use a row-level filter (WHERE). List the result set.
b) Round down totals: For each user_id, compute the total paid amount across all dates and round down to whole dollars. Return columns (user_id, floor_paid_total) using FLOOR on the aggregated sum, not on individual rows. Explain why FLOOR(SUM(amount)) differs from SUM(FLOOR(amount)).
c) CASE WHEN bucketing: Add a column value_tier per order: amount < 10 -> 'low', 10 <= amount < 100 -> 'mid', amount >= 100 -> 'high'. For orders on 2025-09-01 only, return value_tier and count(*) ordered by tier. Be explicit about inclusive/exclusive bounds.
d) Aggregate with GROUP BY and HAVING: For each user_id, count paid orders with created_at <= '2025-09-01' and return only users with at least 2 such orders. Provide the exact SQL and the resulting rows.
e) UNION vs UNION ALL efficiency and correctness: From the same orders table, construct two queries that list user_id who ordered on the web and user_id who ordered in store (created_at = '2025-09-01' in both subqueries). First combine them with UNION and report the row count; then with UNION ALL and report the row count. Which operator is typically more efficient and why? When would UNION be required despite the performance difference? Identify the double-counting risk if you used UNION ALL to count unique users across channels.
Filter paid orders by date and amount
Using the orders table, return order_id for all orders on '2025-09-01' that are paid and have amount >= 10. Use only a row-level filter with WHERE (no HAVING), and list the resulting rows.
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| channel | VARCHAR(10) | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| created_at | DATE | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
Sample data:
| amount | channel | created_at | order_id | status | user_id |
|---|---|---|---|---|---|
| 19.99 | web | 2025-08-29 | 1 | paid | 1 |
| 10 | store | 2025-09-01 | 2 | paid | 1 |
| 5 | web | 2025-09-01 | 3 | paid | 2 |
| 5 | store | 2025-09-01 | 4 | pending | 2 |
| 100.49 | web | 2025-09-01 | 5 | paid | 3 |
| 3.01 | web | 2025-09-02 | 6 | refunded | 3 |
Expected output
A single-column list of order_id values for orders created on '2025-09-01' with status = 'paid' and amount >= 10.00.
| order_id |
|---|
| 2 |
| 5 |
Hints
- Filter by date, status, and amount in a single WHERE clause.
- All conditions should be combined with AND.
Round down total paid amount per user
Using the orders table, for each user_id compute the total paid amount across all dates (status = 'paid') and round the total down to whole dollars. Return columns (user_id, floor_paid_total) where floor_paid_total = FLOOR(SUM(amount)), not SUM(FLOOR(amount)). Also be prepared to explain conceptually why FLOOR(SUM(amount)) can differ from SUM(FLOOR(amount)).
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| channel | VARCHAR(10) | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| created_at | DATE | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
Sample data:
| amount | channel | created_at | order_id | status | user_id |
|---|---|---|---|---|---|
| 19.99 | web | 2025-08-29 | 1 | paid | 1 |
| 10 | store | 2025-09-01 | 2 | paid | 1 |
| 5 | web | 2025-09-01 | 3 | paid | 2 |
| 5 | store | 2025-09-01 | 4 | pending | 2 |
| 100.49 | web | 2025-09-01 | 5 | paid | 3 |
| 3.01 | web | 2025-09-02 | 6 | refunded | 3 |
Expected output
One row per user with total paid amount summed first and then rounded down using FLOOR. For this data: user 1 has 19.99 + 10.00 = 29.99 -> floor 29; user 2 has 5.00 -> floor 5; user 3 has 100.49 -> floor 100. FLOOR(SUM(amount)) differs from SUM(FLOOR(amount)) because the former rounds only the final total (keeping cents until the end), while the latter rounds each row before summing, potentially losing cents multiple times.
| floor_paid_total | user_id |
|---|---|
| 29 | 1 |
| 5 | 2 |
| 100 | 3 |
Hints
- Filter to paid orders with a WHERE clause before aggregating.
- Apply FLOOR to the SUM(amount), not to each individual amount.
Bucket order amounts into tiers with CASE
Using the orders table, define a value_tier per order based on amount with these rules: amount < 10 -> 'low'; 10 <= amount < 100 -> 'mid'; amount >= 100 -> 'high'. Consider orders on '2025-09-01' only, and return value_tier and count(*) as order_count, grouped by tier and ordered by value_tier. Be explicit in your CASE expression about the inclusive (>=) and exclusive (<) boundaries.
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| channel | VARCHAR(10) | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| created_at | DATE | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
Sample data:
| amount | channel | created_at | order_id | status | user_id |
|---|---|---|---|---|---|
| 19.99 | web | 2025-08-29 | 1 | paid | 1 |
| 10 | store | 2025-09-01 | 2 | paid | 1 |
| 5 | web | 2025-09-01 | 3 | paid | 2 |
| 5 | store | 2025-09-01 | 4 | pending | 2 |
| 100.49 | web | 2025-09-01 | 5 | paid | 3 |
| 3.01 | web | 2025-09-02 | 6 | refunded | 3 |
Expected output
For orders on '2025-09-01', bucket each by amount into 'low' (<10), 'mid' (>=10 and <100), or 'high' (>=100), then count orders per bucket. On that date: amounts 5.00, 5.00 -> 'low'; 10.00 -> 'mid'; 100.49 -> 'high'.
| order_count | value_tier |
|---|---|
| 1 | high |
| 2 | low |
| 1 | mid |
Hints
- Use a CASE expression to map numeric ranges to text tiers.
- Ensure the ranges cover all amounts without gaps or overlaps.
Use GROUP BY and HAVING to filter users by paid order count
Using the orders table, for each user_id count how many paid orders (status = 'paid') they have with created_at <= '2025-09-01'. Return only users with at least 2 such paid orders. Provide the SQL and the resulting rows.
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| channel | VARCHAR(10) | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| created_at | DATE | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
Sample data:
| amount | channel | created_at | order_id | status | user_id |
|---|---|---|---|---|---|
| 19.99 | web | 2025-08-29 | 1 | paid | 1 |
| 10 | store | 2025-09-01 | 2 | paid | 1 |
| 5 | web | 2025-09-01 | 3 | paid | 2 |
| 5 | store | 2025-09-01 | 4 | pending | 2 |
| 100.49 | web | 2025-09-01 | 5 | paid | 3 |
| 3.01 | web | 2025-09-02 | 6 | refunded | 3 |
Expected output
One row per user who has at least 2 paid orders on or before '2025-09-01'. Here, user 1 has two paid orders (order_id 1 and 2); users 2 and 3 have only one each.
| paid_order_count | user_id |
|---|---|
| 2 | 1 |
Hints
- Filter to paid orders and the date range in the WHERE clause.
- Use HAVING to keep only groups (users) whose COUNT(*) is at least 2.
Compare UNION vs UNION ALL for users across channels
Using the orders table, construct two queries that list user_id who ordered on the web and user_id who ordered in store, restricted to created_at = '2025-09-01' in both subqueries. First, combine them with UNION and report the resulting row count. Then, combine them with UNION ALL and report the resulting row count. Which operator is typically more efficient and why? When is UNION required despite the performance difference? Explain the double-counting risk if you use UNION ALL to count unique users across channels.
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| channel | VARCHAR(10) | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| created_at | DATE | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
Sample data:
| amount | channel | created_at | order_id | status | user_id |
|---|---|---|---|---|---|
| 19.99 | web | 2025-08-29 | 1 | paid | 1 |
| 10 | store | 2025-09-01 | 2 | paid | 1 |
| 5 | web | 2025-09-01 | 3 | paid | 2 |
| 5 | store | 2025-09-01 | 4 | pending | 2 |
| 100.49 | web | 2025-09-01 | 5 | paid | 3 |
| 3.01 | web | 2025-09-02 | 6 | refunded | 3 |
Expected output
On '2025-09-01', web orders are from users {2, 3}; store orders are from users {1, 2}. UNION of these user_id sets returns distinct users {1, 2, 3}, so the UNION row count is 3. UNION ALL keeps duplicates (user 2 appears twice), so the UNION ALL row count is 4. UNION ALL is typically more efficient because it avoids the extra sort/hash needed to remove duplicates. You must use UNION when you need duplicates removed (e.g., to count unique users). Using UNION ALL when counting unique users can double-count users who order in both channels.
| operator | user_count |
|---|---|
| UNION | 3 |
| UNION ALL | 4 |
Hints
- Start by writing two simple SELECT user_id queries filtered by channel and date.
- Remember that UNION removes duplicates while UNION ALL does not, which affects both performance and counts.