-
Notifications
You must be signed in to change notification settings - Fork 0
/
sales_analysis1.sql
41 lines (35 loc) · 1.06 KB
/
sales_analysis1.sql
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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
/*
Table: Product
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
| unit_price | int |
+--------------+---------+
product_id is the primary key 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 has no primary key, it can have repeated rows.
product_id is a foreign key to the Product table.
Each row of this table contains some information about one sale.
Write an SQL query that reports the best seller by total sales price, If there is a tie, report them all.
Return the result table in any order.
*/
SELECT seller_id FROM (
SELECT seller_id,
DENSE_RANK() OVER( ORDER BY sum(price) DESC) AS RNK
FROM Sales
GROUP BY seller_id
) AS temp
WHERE RNK=1;