Explain a SQL query result
Company: TikTok
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Given two tables and a specific SQL query, precisely explain the expected result set: which rows are returned, what each column contains, how joins/filters/aggregations/window functions affect the output, how NULLs and duplicates are handled, and the final ordering. State any assumptions and edge cases.
Overview: This question evaluates proficiency in SQL result-set interpretation and reasoning about joins, filters, aggregations, window functions, NULL handling, duplicate rows, and ordering within the Data Manipulation (SQL/Python) domain for a Data Engineer role.
You are given two tables, `orders` and `payments`, and the SQL query below. Use the table definitions and sample data to precisely explain the expected result set:
- Which rows are returned (and which are filtered out).
- What each column in the output contains.
- How the join, GROUP BY, HAVING filter, and window function affect the output.
- How NULLs and duplicate rows (e.g., multiple payments per order) are handled.
- The final ordering of the rows.
State any assumptions and edge cases you identify based on the query and data.
Table: `orders`
- `order_id`: unique ID of an order.
- `customer_id`: ID of the customer who placed the order.
- `order_date`: date when the order was created.
- `status`: current status of the order.
- `total_amount`: total value of the order.
Table: `payments`
- `payment_id`: unique ID of a payment.
- `order_id`: ID of the order this payment belongs to.
- `payment_date`: date when the payment was made.
- `amount`: payment amount.
SQL query to explain:
SELECT
o.customer_id,
o.order_id,
o.order_date,
o.status,
o.total_amount,
SUM(p.amount) AS total_paid,
o.total_amount - SUM(p.amount) AS amount_due,
ROW_NUMBER() OVER (
PARTITION BY o.customer_id
ORDER BY o.order_date DESC, o.order_id DESC
) AS recent_order_rank
FROM orders o
LEFT JOIN payments p
ON o.order_id = p.order_id
WHERE o.status IN ('SHIPPED', 'DELIVERED')
GROUP BY
o.customer_id,
o.order_id,
o.order_date,
o.status,
o.total_amount
HAVING SUM(p.amount) IS NULL
OR SUM(p.amount) < o.total_amount
ORDER BY
o.customer_id,
recent_order_rank;
Tables
orders(order_id INT, customer_id INT, order_date DATE, status VARCHAR(20), total_amount DECIMAL(10,2))
payments(payment_id INT, order_id INT, payment_date DATE, amount DECIMAL(10,2))
Hints
- Start by applying the WHERE clause and LEFT JOIN to determine which base rows are included before aggregation.
- Think about how SUM(p.amount) behaves when there are multiple payments per order or no payments at all, and how the HAVING clause and window function use these aggregated rows.