Operations wants to reconcile the product catalogue against sales data, including products that have never been sold. Using the products and order_items tables, return product_id, product_name, and total_sold (0 if never ordered), ordered by product_id.
products
| column | type |
|---|---|
| id | INTEGER |
| name | TEXT |
| category | TEXT |
| price | NUMERIC |
order_items
| column | type |
|---|---|
| id | INTEGER |
| order_id | INTEGER |
| product_id | INTEGER |
| quantity | INTEGER |
| unit_price | NUMERIC |
products
| id | name | category | price |
|---|---|---|---|
| 1 | Widget | Tools | 9.99 |
| 2 | Gadget | Electronics | 149.99 |
| 3 | Gizmo | Electronics | 79.99 |
order_items
| id | order_id | product_id | quantity | unit_price |
|---|---|---|---|---|
| 1 | 101 | 1 | 2 | 9.99 |
| 2 | 102 | 1 | 3 | 9.99 |
| 3 | 103 | 2 | 3 | 149.99 |
| product_id | product_name | total_sold |
|---|---|---|
| 1 | Widget | 5 |
| 2 | Gadget | 3 |
| 3 | Gizmo | 0 |
Widget appeared in 2 order items with quantities 2 and 3 (total 5). Gadget appeared once with quantity 3. Gizmo has no order items so COALESCE(SUM(...), 0) returns 0.