Write SQL filtering, grouping, CASE, UNION tasks
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: HR Screen
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.
Overview: 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.
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.
Tables
orders(order_id INT, user_id INT, channel VARCHAR(10), amount DECIMAL(10,2), created_at DATE, status VARCHAR(20))
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)).
Tables
orders(order_id INT, user_id INT, channel VARCHAR(10), amount DECIMAL(10,2), created_at DATE, status VARCHAR(20))
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.
Tables
orders(order_id INT, user_id INT, channel VARCHAR(10), amount DECIMAL(10,2), created_at DATE, status VARCHAR(20))
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.
Tables
orders(order_id INT, user_id INT, channel VARCHAR(10), amount DECIMAL(10,2), created_at DATE, status VARCHAR(20))
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.
Tables
orders(order_id INT, user_id INT, channel VARCHAR(10), amount DECIMAL(10,2), created_at DATE, status VARCHAR(20))
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.