Explain a SQL query result

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.

Explain the Result of a Join with Aggregation and Window Function

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;

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
order_date	DATE	NOT NULL
status	VARCHAR(20)	NOT NULL
total_amount	DECIMAL(10,2)	NOT NULL

Sample data:

customer_id	order_date	order_id	status	total_amount
101	2025-05-01	1	SHIPPED	100
101	2025-05-10	2	DELIVERED	150
102	2025-05-05	3	PENDING	200
102	2025-05-20	4	DELIVERED	80
103	2025-05-15	5	SHIPPED	50
103	2025-05-18	6	CANCELLED	120

Table payments

column	type	constraints
payment_id	INT	PRIMARY KEY, NOT NULL
order_id	INT	NOT NULL
payment_date	DATE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	order_id	payment_date	payment_id
60	1	2025-05-02	1
40	1	2025-05-03	2
100	2	2025-05-11	3
25	2	2025-05-12	7
80	4	2025-05-21	4
20	5	2025-05-16	5
120	6	2025-05-19	6

Expected output

The query returns one row per SHIPPED or DELIVERED order that is not fully paid (either no payments or the sum of payments is less than the order's total_amount). For each such order, it shows the customer, order details, total payments applied to that order, the remaining amount due, and a row number ranking that order by recency within that customer (most recent order gets rank 1). Multiple payments per order are summed together. Orders with statuses other than SHIPPED/DELIVERED are excluded, as are fully paid orders. In the sample data, this results in two partially paid orders: order 2 for customer 101 and order 5 for customer 103. The result is ordered by customer_id and then by the recent_order_rank.

amount_due	customer_id	order_date	order_id	recent_order_rank	status	total_amount	total_paid
25	101	2025-05-10	2	1	DELIVERED	150	125
30	103	2025-05-15	5	1	SHIPPED	50	20

Hints

1. Start by applying the WHERE clause and LEFT JOIN to determine which base rows are included before aggregation.
2. 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.