Seller GMV From Delivered Items

Easy
100.

Seller GMV From Delivered Items

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

ColumnType
seller_idint
store_namevarchar
cityvarchar

Primary key: seller_id.

One row per seller on the marketplace.

Table: order_items

ColumnTypeValues
item_idint
order_idint
seller_idint
quantityint
unit_priceint
item_statusenumdelivered, cancelled, returned

Primary key: item_id.

One row per line of an order; unit_price in rupees. seller_id is always in sellers.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

sellers

seller_idstore_namecity
1UrbanKartMumbai
2Lakshmi TextilesChennai
3TechBazaarBengaluru
4Desi BasketPune

order_items

item_idorder_idseller_idquantityunit_priceitem_status
1700112499delivered
27001311999delivered
3700211899returned
4700323650delivered
5700441320cancelled
67005321999delivered
77006111299delivered

Output

seller_idstore_namegmv
1UrbanKart2297
2Lakshmi Textiles1950
3TechBazaar5997
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
seller_idint
store_namevarchar
cityvarchar

Primary key: seller_id.

One row per seller on the marketplace.

Table: order_items

ColumnType
item_idint
order_idint
seller_idint
quantityint
unit_priceint
item_statusenum(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_idstore_namecity
1UrbanKartMumbai
2Lakshmi TextilesChennai
3TechBazaarBengaluru
4Desi BasketPune

order_items

item_idorder_idseller_idquantityunit_priceitem_status
1700112499delivered
27001311999delivered
3700211899returned
4700323650delivered
5700441320cancelled
67005321999delivered
77006111299delivered

Output

seller_idstore_namegmv
1UrbanKart2297
2Lakshmi Textiles1950
3TechBazaar5997

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_name

Another 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_id

Another 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 →