Top Earning Market Stall
Problem
A farmers market records every purchase made at its stalls.
Table: stall
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| stall_id | int |
| produce | varchar |
| list_cents | int |
+-------------+---------+
stall_id is the primary key. Each row describes a stall and the produce it sells.
Table: purchase
+-------------+------+
| Column Name | Type |
+-------------+------+
| vendor_id | int |
| stall_id | int |
| shopper_id | int |
| sold_on | date |
| units | int |
| revenue | int |
+-------------+------+
This table may contain duplicate rows. Each row records one purchase made by a vendor.
Write a solution that reports the vendor(s) with the highest total revenue across all their purchases. If several vendors tie for the highest total, report all of them. Return the result in any order with a single column vendor_id.
Tables
Example rows — the live problem includes the full dataset.
| stall_id | produce | list_cents |
|---|---|---|
| 1 | Heirloom | 1000 |
| 2 | Honeycrisp | 800 |
| 3 | Cherries | 1400 |
| vendor_id | stall_id | shopper_id | sold_on | units | revenue |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 2021-01-21 | 2 | 2000 |
| 1 | 2 | 2 | 2021-02-17 | 1 | 800 |
| 2 | 2 | 3 | 2021-06-02 | 1 | 800 |
Expected output
Your answer should return 2 rows with the columns vendor_id.
Starter code (SQL)
SELECT *
FROM stall;Solve this SQL question free
Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.
Solution & explanation
Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.
Sign up free to unlock