1082 Sales Analysis I

Description

Table: Product

• 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

• 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.

Problem Statement

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:

• Sales table:

Output:

Explanation:

• Both sellers with id 1 and 3 sold products with the most total price of 2800.

Solution

The problem essentially asks to find the sum of price column for each seller_id from the Sales table. Next, you need to find the maximum of this sum and return those seller_ids only.

Approach 1: Using GROUP BY clause

You can find the maximum sum using GROUP BY clause.

1SELECT SUM(price) AS price
2    FROM Sales
3    GROUP BY seller_id
4    ORDER BY price DESC
5    LIMIT 1;

2. Find the seller_id which has the sum of sales which is equal to the maximum sum.

 1SELECT seller_id
 2    FROM Sales
 3    GROUP BY seller_id
 4    HAVING SUM(price) = (
 5        SELECT SUM(price) AS price
 6            FROM Sales
 7            GROUP BY seller_id
 8            ORDER BY price DESC
 9            LIMIT 1
10    );

You can perfom the same using below query as well.

 1WITH sales_by_seller AS (
 2    SELECT seller_id, SUM(price) total_sales 
 3        FROM Sales 
 4        GROUP BY seller_id
 5)
 6SELECT seller_id 
 7    FROM sales_by_seller 
 8    WHERE total_sales = (
 9        SELECT MAX(total_sales) 
10            FROM sales_by_seller
11    );

In this case, you find sales by seller in the first view and then find the maximum of total_sales in the second view. Finally, you output seller_ids for which the total_sales is equal to the maximum total sales.

Approach 2: Using Window function

Again, you can use window function to get the results. In this case, first you need to find the sum of price for each seller_id and store it in a temporary view.

1WITH sales_by_sellers AS (
2    SELECT seller_id, SUM(price) total_sales
3        FROM Sales
4        GROUP BY seller_id
5) SELECT seller_id, total_sales
6    FROM sales_by_sellers;

Next, you need to assign rank based on total_sales. You need to find rank across the table sales_by_sellers. So, you do not partition by specific field but you can order the table by total_sales in descending order.

1SELECT seller_id, total_sales, RANK() OVER (ORDER BY total_sales DESC) rnk
2    FROM sales_by_sellers;

Next, you filter the rows for which rnk is 1. You can do this using WHERE clause.

1SELECT seller_id 
2    FROM ranked_sellers
3    WHERE rnk = 1;

The complete solution looks like this.

 1WITH sales_by_sellers AS (
 2    SELECT seller_id, SUM(price) total_sales
 3        FROM Sales
 4        GROUP BY seller_id
 5), ranked_sellers AS (
 6    SELECT seller_id, total_sales, RANK() OVER (ORDER BY total_sales DESC) rnk
 7    FROM sales_by_sellers
 8) SELECT seller_id 
 9    FROM ranked_sellers
10    WHERE rnk = 1;