Table: Product
+--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | | unit_price | int | +--------------+---------+ product_id is the primary key (column with unique values) of this table. Each row of this table indicates the name and the price of each product.
Table: Sales
+-------------+---------+ | Column Name | Type | +-------------+---------+ | seller_id | int | | product_id | int | | buyer_id | int | | sale_date | date | | quantity | int | | price | int | +-------------+---------+ This table can have repeated rows. product_id is a foreign key (reference column) to the Product table. Each row of this table contains some information about one sale.
Write a solution that reports the best seller by total sales price, If there is a tie, report them all.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Product table: +------------+--------------+------------+ | product_id | product_name | unit_price | +------------+--------------+------------+ | 1 | S8 | 1000 | | 2 | G4 | 800 | | 3 | iPhone | 1400 | +------------+--------------+------------+ Sales table: +-----------+------------+----------+------------+----------+-------+ | 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 | | 3 | +-------------+ Explanation: Both sellers with id 1 and 3 sold products with the most total price of 2800.
Problem Overview: The database contains a Sales table where each row represents a transaction with a seller and the price of the product sold. The task is to return the seller_id of the seller (or sellers) whose total sales amount is the highest across all sellers.
Approach 1: Aggregation + Subquery (O(n) time, O(k) space)
Compute total sales per seller using GROUP BY seller_id and SUM(price). This produces one row per seller with their total revenue. Then compare each seller's total against the global maximum total using a subquery that calculates MAX(total_sales). Sellers whose totals match that maximum are returned.
This approach relies on core SQL aggregation and filtering logic. The database first scans the Sales table, aggregates rows per seller, and then evaluates the maximum aggregated value. Conceptually it performs one full table scan and one grouped aggregation, giving O(n) time complexity where n is the number of sales rows. The grouped results require O(k) space where k is the number of distinct sellers.
You'll frequently see this pattern in database interview problems: aggregate results first, then filter using a second query that computes the extreme value (MAX, MIN, etc.).
Approach 2: Window Function Ranking (O(n) time, O(k) space)
An alternative uses window functions. First compute each seller's total sales with SUM(price) grouped by seller_id. Then apply a window function such as RANK() or DENSE_RANK() ordered by total sales in descending order. Sellers with rank 1 are the ones with the highest total.
This method avoids a nested subquery and expresses the logic more declaratively. Window functions evaluate rankings across result rows without collapsing them further. Many modern SQL engines optimize this pattern efficiently, especially when analytic queries are common.
Window functions appear often in analytics-style SQL problems and more advanced window function queries. The runtime remains O(n) for scanning and aggregating sales rows, with O(k) memory for grouped seller totals.
Recommended for interviews: The aggregation + subquery approach is the most expected solution because it uses fundamental SQL operations: GROUP BY, SUM, and MAX. It demonstrates clear understanding of aggregation and filtering logic. The window function approach is cleaner in analytic workloads and shows stronger SQL fluency, but interviewers usually expect the aggregation pattern first.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Aggregation + Subquery | O(n) | O(k) | Standard SQL solution using GROUP BY and MAX. Most common interview answer. |
| Window Function Ranking | O(n) | O(k) | Cleaner analytic query when window functions like RANK or DENSE_RANK are available. |
LeetCode 1082: Sales Analysis I [SQL] • Frederik Müller • 3,729 views views
Watch 4 more video solutions →Practice Sales Analysis I with our built-in code editor and test cases.
Practice on FleetCode