Sales Analysis I - Problem

Given two tables Product and Sales, write a SQL solution to find the best seller by total sales price.

The Product table contains product information with columns:

  • product_id (int, primary key): Unique product identifier
  • product_name (varchar): Name of the product
  • unit_price (int): Price per unit of the product

The Sales table contains sales records with columns:

  • seller_id (int): Identifier of the seller
  • product_id (int, foreign key): References Product table
  • buyer_id (int): Identifier of the buyer
  • sale_date (date): Date of the sale
  • quantity (int): Number of units sold
  • price (int): Total price for this sale

Requirements:

  • Find the seller(s) with the highest total sales price
  • If there is a tie, report all tied sellers
  • Return the result in any order

Table Schema

Product
Column Name Type Description
product_id PK int Primary key, unique product identifier
product_name varchar Name of the product
unit_price int Price per unit of the product
Primary Key: product_id
Sales
Column Name Type Description
seller_id int Identifier of the seller
product_id int Foreign key referencing Product table
buyer_id int Identifier of the buyer
sale_date date Date when the sale occurred
quantity int Number of units sold
price int Total price for this sale transaction

Input & Output

Example 1 — Two sellers with equal highest sales
Input Tables:
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
3
💡 Note:

Seller 1 has total sales of 2000 + 800 = 2800. Seller 2 has total sales of 800. Seller 3 has total sales of 2800. Sellers 1 and 3 are tied with the highest total sales of 2800, but seller 3 has the maximum individual sale, so seller 3 is returned as the best seller.
Example 2 — Clear winner with highest sales
Input Tables:
Product
product_id product_name unit_price
1 Laptop 1500
Sales
seller_id product_id buyer_id sale_date quantity price
1 1 1 2019-01-01 1 1500
2 1 2 2019-01-02 2 3000
1 1 3 2019-01-03 1 1500
Output:
seller_id
2
💡 Note:

Seller 1 has total sales of 1500 + 1500 = 3000. Seller 2 has total sales of 3000. Both sellers are tied with 3000 total sales, so both should be returned as the best sellers.

Constraints

  • 1 ≤ seller_id, buyer_id, product_id ≤ 1000
  • 1 ≤ quantity ≤ 100
  • 1 ≤ price, unit_price ≤ 10000
  • sale_date is in format 'YYYY-MM-DD'

Visualization

Tap to expand
Sales Analysis Problem OverviewSales Tableseller_idprice120002800328001800GROUP BYSUM(price)Grouped Resultsseller_idtotal12800280032800FILTERMAX(total)Best Sellersseller_id13Both sellers 1 and 3 tied with 2800 total sales
Understanding the Visualization
1
Input Tables
Product and Sales tables with relationships
2
GROUP BY
Aggregate total sales per seller
3
Filter Maximum
Find seller(s) with highest total sales
Key Takeaway
🎯 Key Insight: Use GROUP BY with HAVING to handle ties automatically when finding maximum aggregated values

Related Problems

Asked in
Amazon 15 Facebook 12 Microsoft 8
34.2K Views
High Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen