Sales Analysis I - Practice Coding Problems

Sales Analysis I - Problem

Database Easy

Sales Analysis I: Finding the Top-Performing Sellers

You're working as a data analyst for an e-commerce platform! Given two database tables, your mission is to identify which sellers generated the highest total sales revenue.

📊 Available Data:
• Product table: Contains product information (ID, name, unit price)
• Sales table: Contains transaction records (seller, product, buyer, date, quantity, price)

🎯 Your Goal:
Write a SQL query to find the seller(s) with the maximum total sales amount. If multiple sellers tie for the top spot, include all of them in your results!

Note: The total sales for a seller is calculated by summing up the price field from all their transactions in the Sales table.

Input & Output

example_1.sql — Basic Example

$ 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 | | 3 |

💡 Note: Seller 1 total: $2000 + $800 = $2800, Seller 2 total: $800, Seller 3 total: $2800. Both sellers 1 and 3 have the maximum total of $2800, but the expected output shows seller 3, indicating there might be a tie-breaking rule or this is a simplified example.

example_2.sql — Multiple Winners

$ Input: Sales: | seller_id | product_id | buyer_id | sale_date | quantity | price | | 1 | 1 | 1 | 2019-01-21 | 1 | 1000 | | 2 | 2 | 2 | 2019-02-17 | 1 | 1000 | | 3 | 3 | 3 | 2019-03-10 | 1 | 500 |

› Output: | seller_id | | 1 | | 2 |

💡 Note: Sellers 1 and 2 both have $1000 in total sales, which is the maximum. Seller 3 has only $500. Both winners are returned as they tie for first place.

example_3.sql — Single Transaction Each

$ Input: Sales: | seller_id | product_id | buyer_id | sale_date | quantity | price | | 1 | 1 | 1 | 2019-01-01 | 1 | 500 | | 2 | 2 | 2 | 2019-01-02 | 1 | 750 | | 3 | 3 | 3 | 2019-01-03 | 1 | 600 |

› Output: | seller_id | | 2 |

💡 Note: Each seller has exactly one transaction. Seller 2 has the highest single transaction value of $750, making them the winner.

Constraints

1 ≤ Number of products ≤ 1000
1 ≤ Number of sales transactions ≤ 10⁵
All prices and quantities are positive integers
Sale dates are valid dates in YYYY-MM-DD format
product_id in Sales table exists in Product table (foreign key constraint)

Visualization

Tap to expand

Understanding the Visualization

Collect Sales Data

Gather all sales transactions from each seller

Calculate Totals

Sum up the total revenue generated by each seller

Rank Performance

Order sellers by their total sales (highest first)

Crown Champions

Select all sellers tied for the #1 position

Key Takeaway

🎯 Key Insight: Use window functions like RANK() to efficiently identify maximum values while automatically handling ties, making your SQL both elegant and performant!

Asked in

a Amazon 45 ⊞ Microsoft 35 G Google 28 f Meta 22

The optimal solution uses SQL window functions, specifically RANK() OVER (ORDER BY SUM(price) DESC) to efficiently identify the seller(s) with maximum total sales. This approach handles ties automatically and requires only a single query execution, making it both elegant and performant for large datasets.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Queries)	O(n)	O(k)	Calculate each seller's total separately, then find maximum manually
Window Function (Optimal)	O(n log n)	O(k)	Use window functions to calculate totals and identify maximum in one elegant query

Brute Force (Multiple Queries) — Algorithm Steps

Calculate total sales for each seller using GROUP BY
Manually identify the maximum total from results
Query again to find all sellers with the maximum total
Return the seller IDs

Visualization

Tap to expand

Step-by-Step Walkthrough

Group by Seller

Separate all sales records by seller_id

Calculate Totals

Sum up prices for each seller individually

Find Maximum

Identify the highest total among all sellers

Filter Results

Return all sellers matching the maximum total

Code -

solution.c — C

// Brute Force SQL Solution
#include <stdio.h>
#include <string.h>

const char* findBestSellers() {
    // Static string with nested subquery approach
    static const char query[] = 
        "SELECT seller_id "
        "FROM Sales "
        "GROUP BY seller_id "
        "HAVING SUM(price) = ("
        "    SELECT MAX(total_sales) FROM ("
        "        SELECT SUM(price) as total_sales "
        "        FROM Sales "
        "        GROUP BY seller_id"
        "    ) AS seller_totals"
        ")";
    
    return query;
}

int main() {
    printf("%s\n", findBestSellers());
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single scan through sales data for aggregation, but requires multiple queries

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique sellers for storing intermediate results

✓ Linear Space

82.4K Views

High Frequency

~15 min Avg. Time

1.8K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Multiple Queries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler