-
Notifications
You must be signed in to change notification settings - Fork 0
/
Question4
35 lines (32 loc) · 1.07 KB
/
Question4
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
--Question 4
--Get the top 5 least and most profitable product over all time
SELECT Product_ID, Product_Name,Total_Sales, Total_Cost,Total_Gross_Profit,
CONCAT("Highest ", RANK() OVER (ORDER BY Total_Gross_Profit DESC)) as Rank_Gross_Profit
FROM
(
SELECT id AS Product_ID,
name as Product_Name,
ROUND (SUM (retail_price),2) AS Total_Sales,
ROUND (SUM (cost),2) as Total_Cost,
ROUND (SUM (retail_price-cost),2) AS Total_Gross_Profit,
FROM `bigquery-public-data.thelook_ecommerce.products`
GROUP BY 1,2
ORDER BY 5 DESC
LIMIT 5
) GPMax
UNION ALL
SELECT Product_ID, Product_Name,Total_Sales, Total_Cost,Total_Gross_Profit,
CONCAT("Lowest ", RANK() OVER (ORDER BY Total_Gross_Profit ASC)) as Rank_Gross_Profit
FROM
(
SELECT id AS Product_ID,
name as Product_Name,
ROUND (SUM (retail_price),2) AS Total_Sales,
ROUND (SUM (cost),2) as Total_Cost,
ROUND (SUM (retail_price-cost),2) AS Total_Gross_Profit,
FROM `bigquery-public-data.thelook_ecommerce.products`
GROUP BY 1,2
ORDER BY 5 ASC
LIMIT 5
) GPMin
ORDER BY Total_Gross_Profit DESC;