Top Seller Total Revenue Report

Easy

An e-commerce analytics platform tracks catalog metadata in Product and transactional records in Sales. Product columns: product_id: unique product identifier product_name: product display name unit_price: catalog list price Sales columns: seller_id: seller identifier product_id: sold product identifier buyer_id: buyer identifier sale_date: sale event date quantity: item quantity for the sale row price: total revenue captured by that sale row Task: Compute each seller's total realized revenue using SUM(price). Return all seller_id values whose total revenue equals the global maximum. If multiple sellers tie for first place, include them all. Output requirements: Return exactly one column: seller_id Sort by seller_id in ascending order Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Product:
| product_id | product_name | unit_price |
|------------|--------------|------------|
| 1          | S8           | 1000       |
| 2          | G4           | 800        |
| 3          | iPhone       | 1400       |

Sales:
| seller_id | product_id | buyer_id | sale_date  | quantity | price |
|-----------|------------|----------|------------|----------|-------|
| 1         | 1          | 1        | 2019-01-21 | 2        | 2000  |
| 1         | 2          | 2        | 2019-02-17 | 1        | 800   |
| 2         | 2          | 3        | 2019-06-02 | 1        | 800   |
| 3         | 3          | 4        | 2019-05-13 | 2        | 2800  |

Output

[
  {"seller_id":1},
  {"seller_id":3}
]

Explanation

Seller 1 and seller 3 each have total revenue 2800, which is the maximum.

Example 2

Input

Product:
| product_id | product_name | unit_price |
|------------|--------------|------------|
| 10         | Cable        | 25         |
| 11         | Hub          | 65         |

Sales:
| seller_id | product_id | buyer_id | sale_date  | quantity | price |
|-----------|------------|----------|------------|----------|-------|
| 8         | 10         | 100      | 2021-03-01 | 4        | 100   |
| 9         | 11         | 101      | 2021-03-02 | 1        | 65    |
| 8         | 11         | 102      | 2021-03-04 | 2        | 130   |

Output

[
  {"seller_id":8}
]

Explanation

Seller 8 accumulates 230 revenue and is the sole top seller.

Example 3

Input

Product:
| product_id | product_name | unit_price |
|------------|--------------|------------|
| 3          | Sensor       | 50         |
| 4          | Mount        | 20         |

Sales:
| seller_id | product_id | buyer_id | sale_date | quantity | price |
|-----------|------------|----------|-----------|----------|-------|
| (no rows) |            |          |           |          |       |

Output

[]

Explanation

With no sales rows, there is no top seller and the result is empty.

Constraints

1 <= number of rows in Product <= 10,000
0 <= number of rows in Sales <= 10,000
1 <= product_id <= 1,000,000,000
1 <= seller_id, buyer_id <= 1,000,000,000
0 <= quantity <= 1,000,000,000
0 <= unit_price, price <= 1,000,000,000
Product.product_id is unique
Sales.product_id references Product.product_id
Sales may contain repeated rows
Compute seller totals using SUM(price)
Return exactly one column named seller_id
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with column seller_id

Product: | product_id | product_name | unit_price | |------------|--------------|------------| | 1 | S8 | 1000 | | 2 | G4 | 800 | | 3 | iPhone | 1400 | Sales: | seller_id | product_id | buyer_id | sale_date | quantity | price | |-----------|------------|----------|------------|----------|-------| | 1 | 1 | 1 | 2019-01-21 | 2 | 2000 | | 1 | 2 | 2 | 2019-02-17 | 1 | 800 | | 2 | 2 | 3 | 2019-06-02 | 1 | 800 | | 3 | 3 | 4 | 2019-05-13 | 2 | 2800 |

Product: | product_id | product_name | unit_price | |------------|--------------|------------| | 10 | Cable | 25 | | 11 | Hub | 65 | Sales: | seller_id | product_id | buyer_id | sale_date | quantity | price | |-----------|------------|----------|------------|----------|-------| | 8 | 10 | 100 | 2021-03-01 | 4 | 100 | | 9 | 11 | 101 | 2021-03-02 | 1 | 65 | | 8 | 11 | 102 | 2021-03-04 | 2 | 130 |

Product: | product_id | product_name | unit_price | |------------|--------------|------------| | 3 | Sensor | 50 | | 4 | Mount | 20 | Sales: | seller_id | product_id | buyer_id | sale_date | quantity | price | |-----------|------------|----------|-----------|----------|-------| | (no rows) | | | | | |