Sales Analysis I Problem


Description

LeetCode Problem 1082.

Table: Product

1
2
3
4
5
6
7
8
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
| unit_price   | int     |
+--------------+---------+
product_id is the primary key of this table.

Table: Sales

1
2
3
4
5
6
7
8
9
10
11
12
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+------ ------+---------+
This table has no primary key, it can have repeated rows.
product_id is a foreign key to Product table.

Write an SQL query that reports the best seller by total sales price. If there is a tie, report them all.

The query result format is in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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  |
+-----------+------------+----------+------------+----------+-------+

Result table:
+-------------+
| seller_id   |
+-------------+
| 1           |
| 3           |
+-------------+
Both sellers with id 1 and 3 sold products with the most total price of 2800.


MySQL Solution

1
2
3
4
5
6
7
8
9
select distinct seller_id
from Sales
group by seller_id
having sum(price) = (
    select sum(price) as max_price
    from Sales
    group by seller_id
    order by max_price desc
    limit 1)

The second select statement is to a subquery to calculate the maximum sales price. The first select statement is to pick sellers that have the best total sales price.




Related Posts

Game Play Analysis V Problem

LeetCode 1097. Write an SQL query that reports for each...

Unpopular Books Problem

LeetCode 1098. Write an SQL query that reports the books...

New Users Daily Count Problem

LeetCode 1107. Write an SQL query that reports for every...

Sales Analysis III Problem

LeetCode 1084. Write an SQL query that reports the products...

Sales Analysis II Problem

LeetCode 1083. Write an SQL query that reports the buyers...

Sales Analysis I Problem

LeetCode 1082. Write an SQL query that reports the best...