sql/amazon_sales.sql at main · graemebalint/sql · GitHub

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
42
43
44
45
46
select product_id
	, discounted_price
	, actual_price, rating
	, round(((cast((actual_price-discounted_price) as numeric)/actual_price)*100)) as percent_savings
from amazon_sales;


-- ######


select category
	, round(cast(avg(rating) as numeric),1) as avg_rating
from amazon_sales
group by category
order by avg_rating desc;


-- ######


with temp as
	(select category
		, round(cast(avg(rating) as numeric),1) as avg_rating
	from amazon_sales
	group by category)

select category
	, avg_rating
from temp
where avg_rating in ((select max(avg_rating) from temp), (select min(avg_rating) from temp));

select rating
	, round(avg(((cast((actual_price-discounted_price) as numeric)/actual_price)*100))) as avg_percent_savings
from amazon_sales
group by rating
order by rating desc;


-- ######


select category
	, round(avg(((cast((actual_price-discounted_price) as numeric)/actual_price)*100))) as avg_percent_savings
from amazon_sales
group by category
order by avg_percent_savings desc;