Skip to main content
Talentd

Sales Analysis I - Solution & Explanation

EasyPremiumFree on FleetCodeDatabase4 min readAsked at: Amazon
Practice this problem

Problem Statement

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.

Approach Overview

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.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Aggregation + SubqueryO(n)O(k)Standard SQL solution using GROUP BY and MAX. Most common interview answer.
Window Function RankingO(n)O(k)Cleaner analytic query when window functions like RANK or DENSE_RANK are available.

Video Solution

LeetCode 1082: Sales Analysis I [SQL] • Frederik Müller • 3,760 views views

Watch 4 more video solutions →

Frequently Asked Questions

Is Sales Analysis I easy or hard?
Sales Analysis I is classified as Easy. The problem mainly tests familiarity with SQL aggregation using GROUP BY and filtering results with MAX or ranking functions rather than complex database logic.
Sales Analysis I Python/Java solution
Sales Analysis I is a SQL database problem rather than an algorithm implemented in Python or Java. The expected solution uses SQL queries—typically MySQL—combining GROUP BY, SUM(price), and a MAX aggregation or window function.
How to solve Sales Analysis I in O(n)?
Use SQL aggregation to compute SUM(price) for each seller with GROUP BY seller_id. Then compare those totals with the maximum total sales using a subquery or a window function such as RANK(). Since the table is scanned once for aggregation, the overall complexity remains O(n).
What is the best approach for Sales Analysis I?
The most common solution aggregates total sales per seller using GROUP BY and SUM(price), then filters sellers whose totals equal the maximum total using a subquery. This runs in O(n) time with O(k) space where k is the number of sellers. It relies on standard SQL features and is the approach most interviewers expect.
Is Sales Analysis I asked at Google/Amazon/Meta?
This exact problem appears on LeetCode as a database practice question. Variations of aggregation queries—finding maximum totals, top sellers, or ranking groups—are common in SQL interview rounds at companies like Amazon and Meta, especially for data or backend roles.
What data structure is used in Sales Analysis I?
The solution relies on relational database operations rather than traditional data structures. SQL engines internally use hash aggregation or sorting during GROUP BY operations to compute totals per seller efficiently.
What is the time complexity of Sales Analysis I?
The query processes the Sales table once to compute aggregated totals per seller, giving O(n) time complexity where n is the number of sales records. Storing grouped totals requires O(k) space for k distinct sellers.

Ready to solve this problem?

Practice Sales Analysis I with our built-in code editor and test cases.

Practice on FleetCode
Previous

Sales Analysis II