-
Notifications
You must be signed in to change notification settings - Fork 0
/
big_sql_queries.txt
90 lines (50 loc) · 2.33 KB
/
big_sql_queries.txt
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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
Task 4:
SELECT count(orders_table.date_uuid), DATE_PART('MONTH' , dim_date_times.date_time)
FROM orders_table
INNER JOIN dim_date_times on orders_table.date_uuid = dim_date_times.date_uuid
GROUP BY DATE_PART('MONTH' , dim_date_times.date_time)
ORDER BY count(orders_table.date_uuid) DESC
Task 5:
SELECT dim_store_details.store_type,
sum(orders_table.product_quantity * dim_products.product_price) AS total_sales,
sum(orders_table.product_quantity * dim_products.product_price) * 100 /
sum(sum(orders_table.product_quantity * dim_products.product_price)) over () as percentage
FROM orders_table
INNER JOIN dim_products on orders_table.product_code = dim_products.product_code
INNER JOIN dim_store_details on orders_table.store_code = dim_store_details.store_code
GROUP BY dim_store_details.store_type
ORDER BY sum(orders_table.product_quantity * dim_products.product_price) DESC
Task 6:
SELECT
sum(orders_table.product_quantity * dim_products.product_price) AS total_sales,
DATE_PART('YEAR' , dim_date_times.date_time),
DATE_PART('MONTH' , dim_date_times.date_time)
FROM orders_table
INNER JOIN dim_products on orders_table.product_code = dim_products.product_code
INNER JOIN dim_date_times on orders_table.date_uuid = dim_date_times.date_uuid
GROUP BY DATE_PART('YEAR' , dim_date_times.date_time),
DATE_PART('MONTH' , dim_date_times.date_time)
ORDER BY sum(orders_table.product_quantity * dim_products.product_price) DESC
TASK 7:
SELECT
sum(orders_table.product_quantity * dim_products.product_price) AS total_sales,
dim_store_details.store_type,dim_store_details.country_code
FROM orders_table
INNER JOIN dim_products on orders_table.product_code = dim_products.product_code
INNER JOIN dim_store_details on orders_table.store_code = dim_store_details.store_code
WHERE dim_store_details.country_code = 'DE'
GROUP BY dim_store_details.store_type, dim_store_details.country_code
ORDER BY sum(orders_table.product_quantity * dim_products.product_price) DESC
Task 8:
select myYear,
avg(gap_between_sales) as avg_gap
from
(
select DATE_PART('YEAR' , dim_date_times.date_time) as myYear,
lead(dim_date_times.date_time,1) over ( ORDER BY dim_date_times.date_time)
- dim_date_times.date_time gap_between_sales
from orders_table
INNER JOIN dim_date_times on orders_table.date_uuid = dim_date_times.date_uuid
) mySubQuery
group by myYear
order by myYear DESC