Seller GMV From Delivered Items — SQL Joins Problem
Gross merchandise value (GMV) is what customers paid for goods they actually kept.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #100
Problem statement
Gross merchandise value (GMV) is what customers paid for goods they actually kept. Each order line belongs to one seller; only lines with item_status = delivered count — cancelled and returned lines add nothing.
For every seller with at least one delivered line, return seller_id, store_name and gmv, the sum of quantity * unit_price over their delivered lines. Sellers with no delivered line are left out. Return the rows in any order.
Tables
Table: sellers
| Column | Type |
|---|---|
| seller_id | int |
| store_name | varchar |
| city | varchar |
Primary key: seller_id.
One row per seller on the marketplace.
Table: order_items
| Column | Type |
|---|---|
| item_id | int |
| order_id | int |
| seller_id | int |
| quantity | int |
| unit_price | int |
| item_status | enum(delivered, cancelled, returned) |
Primary key: item_id.
One row per line of an order; unit_price in rupees. seller_id is always in sellers.
Examples
Example 1
sellers
| seller_id | store_name | city |
|---|---|---|
| 1 | UrbanKart | Mumbai |
| 2 | Lakshmi Textiles | Chennai |
| 3 | TechBazaar | Bengaluru |
| 4 | Desi Basket | Pune |
order_items
| item_id | order_id | seller_id | quantity | unit_price | item_status |
|---|---|---|---|---|---|
| 1 | 7001 | 1 | 2 | 499 | delivered |
| 2 | 7001 | 3 | 1 | 1999 | delivered |
| 3 | 7002 | 1 | 1 | 899 | returned |
| 4 | 7003 | 2 | 3 | 650 | delivered |
| 5 | 7004 | 4 | 1 | 320 | cancelled |
| 6 | 7005 | 3 | 2 | 1999 | delivered |
| 7 | 7006 | 1 | 1 | 1299 | delivered |
Output
| seller_id | store_name | gmv |
|---|---|---|
| 1 | UrbanKart | 2297 |
| 2 | Lakshmi Textiles | 1950 |
| 3 | TechBazaar | 5997 |
How to solve Seller GMV From Delivered Items
Join each order line to its seller, keep the delivered lines, and add up quantity * unit_price per seller. Filtering in WHERE — before GROUP BY — means a seller whose only lines were cancelled or returned has no rows left at all and drops out, which is what the statement asks for. Grouping by both seller_id and store_name keeps the query valid under MySQL's ONLY_FULL_GROUP_BY; the name is determined by the id anyway.
Two other shapes give the same answer. Aggregating order_items first in a derived table and joining the totals to sellers touches the sellers table once per seller rather than once per line. Conditional aggregation — summing a CASE that yields 0 for other statuses — keeps every line in the group and needs a HAVING to drop sellers with no delivered line, since their sum would be 0 rather than absent.
The cost is one pass over order_items plus a hash or index lookup into sellers per line.
Reference solution (MySQL)
SELECT s.seller_id, s.store_name, SUM(i.quantity * i.unit_price) AS gmv
FROM sellers s
JOIN order_items i ON i.seller_id = s.seller_id
WHERE i.item_status = 'delivered'
GROUP BY s.seller_id, s.store_nameAnother way
SELECT s.seller_id, s.store_name, g.gmv
FROM sellers s
JOIN (SELECT seller_id, SUM(quantity * unit_price) AS gmv FROM order_items WHERE item_status = 'delivered' GROUP BY seller_id) g
ON g.seller_id = s.seller_idAnother way
SELECT s.seller_id, s.store_name, SUM(CASE WHEN i.item_status = 'delivered' THEN i.quantity * i.unit_price ELSE 0 END) AS gmv
FROM sellers s JOIN order_items i ON i.seller_id = s.seller_id
GROUP BY s.seller_id, s.store_name
HAVING SUM(CASE WHEN i.item_status = 'delivered' THEN 1 ELSE 0 END) > 0← Catalogue Products Nobody Has Wishlisted · B2B Invoices With a Malformed GSTIN →