Predict a Merchant Order Report Query's Output and Find Its One Error

A product analyst wants a report with one row per merchant showing how many completed orders the merchant has and the revenue from those orders. Every merchant in `merchants` must appear, and a merchant with no completed orders must show `0` in both columns. A slide shows the two input tables and the query the analyst wrote; there is no database to run it on. The report says the slide showed a simple SQL query next to a data table, asked what the result would look like, and said the query contained one error to find. It does not reproduce the query or the data, so the tables and query below are an illustrative stand-in of the same kind, built to contain exactly one error. **`merchants`** | merchant_id | plan | | --- | --- | | 1 | basic | | 2 | basic | | 3 | advanced | | 4 | basic | **`orders`** | order_id | merchant_id | amount | status | | --- | --- | --- | --- | | 101 | 1 | 40 | completed | | 102 | 1 | 25 | refunded | | 103 | 1 | 35 | completed | | 104 | 2 | 60 | refunded | | 105 | 3 | 80 | completed | | 106 | 3 | 20 | completed | ```sql SELECT m.merchant_id, m.plan, COUNT(o.order_id) AS completed_orders, COALESCE(SUM(o.amount), 0) AS completed_revenue FROM merchants AS m LEFT JOIN orders AS o ON o.merchant_id = m.merchant_id WHERE o.status = 'completed' GROUP BY m.merchant_id, m.plan ORDER BY m.merchant_id; ``` ### Constraints and Clarifications - The query runs in PostgreSQL without raising an error; the error is in what it returns, not in its syntax. - `merchant_id` is unique in `merchants`, `order_id` is unique in `orders`, every order belongs to a merchant in `merchants`, and no column contains NULL. - `amount` is an INTEGER number of whole dollars. Only orders whose `status` is exactly `'completed'` count toward either column; refunded orders do not. ### Clarifying Questions - Should a merchant with no orders at all appear in the report, or only merchants with at least one order? - For a merchant without completed orders, should the revenue column show `0` or NULL? - Is the error one that stops the query from running, or one that makes it return the wrong rows? ### Part 1 — Predict the output Without running it, write out exactly what the query returns for these tables: every row, every column and every value. ```hint Build the join first Write out the rows the join produces before any filtering, including what the `orders` columns hold for a merchant that has no orders. ``` #### What This Part Should Cover - The logical order in which the query's clauses are applied - What rows without a match in the join look like, and what the filter does to them - The exact result rows and values ### Part 2 — Find and fix the error Compare the result with what the analyst wanted. Identify the one error, explain why it changes the result, and give a corrected query together with its exact output. ```hint Check each requirement Go through the analyst's requirements one at a time, find the merchants the result gets wrong, then ask which clause removed them. ``` #### What This Part Should Cover - The clause that causes the error and the mechanism by which it does - A corrected query that keeps the rest of the logic intact - The corrected output, including why both columns come out as `0` rather than NULL or 1 ### What a Strong Answer Covers - Exact output tables for both the original and the corrected query - Correct reasoning about NULL comparisons in a filter applied after an outer join - A fix that works for both kinds of missing merchant: one with no orders and one whose orders are all non-completed - The effect of the error on metrics built from this report - A quick check that would have caught the error without reading the query closely ### Follow-up Questions - After the fix, what would merchants 2 and 4 show if `COUNT(*)` replaced `COUNT(o.order_id)`, and why? - Someone proposes keeping the `WHERE` clause and changing it to `WHERE o.status = 'completed' OR o.order_id IS NULL`. What does that query return for these tables? - A dashboard averages `completed_orders` per merchant by plan using this report. What does it show for the basic plan with the original query, and what should it show? - The report needs a third column with the number of products each merchant lists, from a `products` table. What goes wrong if you add another `LEFT JOIN` to this query, and how would you write it instead?