-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathSQLQuery3.sql
161 lines (130 loc) · 8.2 KB
/
SQLQuery3.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
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
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
-- Question 1
select L.State from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_LOCATION] L on T.IDLocation = L.IDLocation
where year(T.[Date]) >= 2005
group by L.State
having sum([Quantity])>0
-- Question 2
select top 1 L.State from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_LOCATION] L on T.[IDLocation] = L.[IDLocation]
inner join [dbo].[DIM_MODEL] MO on T.[IDModel] = MO.[IDModel]
inner join [dbo].[DIM_MANUFACTURER] MA on MO.[IDManufacturer] = MA.[IDManufacturer]
where [Country] = 'US' and [Manufacturer_Name] = 'Samsung'
group by [State]
order by sum([Quantity]) desc
-- Question 3
select [State] , [ZipCode] , [Model_Name] , count(MO.[IDModel])[no. of transactions] from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_MODEL] MO on MO.[IDModel] = T.[IDModel]
inner join [dbo].[DIM_LOCATION] L on L.[IDLocation] = T.[IDLocation]
group by [State] , [ZipCode] , [Model_Name]
--Question 4
select top 1 MA.Manufacturer_Name , MO.Model_Name , MO.Unit_price from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_MODEL] MO on T.IDModel=MO.IDModel
inner join [dbo].[DIM_MANUFACTURER] MA on MA.IDManufacturer=MO.IDManufacturer
order by [Unit_price]
-- Question 5
select MO.Model_Name , AVG([Unit_price])[avg_price] from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_MODEL] MO on T.IDModel=MO.IDModel
inner join [dbo].[DIM_MANUFACTURER] MA on MA.IDManufacturer=MO.IDManufacturer
where MA.[Manufacturer_Name] in
(select top 5 MA.[Manufacturer_Name] from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_MODEL] MO on T.IDModel=MO.IDModel
inner join [dbo].[DIM_MANUFACTURER] MA on MA.IDManufacturer=MO.IDManufacturer
group by MA.[Manufacturer_Name]
order by sum(T.[Quantity]) desc)
group by MO.Model_Name
order by AVG([Unit_price])
-- Question 6
select C.Customer_Name , avg(T.[TotalPrice])[average] from [dbo].[DIM_CUSTOMER] C
inner join [dbo].[FACT_TRANSACTIONS] T on C.IDCustomer=T.IDCustomer
where year(T.[Date]) = 2009
group by C.Customer_Name
having avg(T.[TotalPrice]) > 500
-- Question 7
select [Model_Name] from [dbo].[DIM_MODEL]
where [Model_Name] in (
select top 5 MO.Model_Name from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_MODEL] MO on T.IDModel=MO.IDModel
where year(T.[Date]) = 2008
group by MO.Model_Name
order by sum(T.[Quantity]) desc) and [Model_Name] in (
select top 5 MO.Model_Name from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_MODEL] MO on T.IDModel=MO.IDModel
where year(T.[Date]) = 2009
group by MO.Model_Name
order by sum(T.[Quantity]) desc) and [Model_Name] in (
select top 5 MO.Model_Name from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_MODEL] MO on T.IDModel=MO.IDModel
where year(T.[Date]) = 2010
group by MO.Model_Name
order by sum(T.[Quantity]) desc)
-- Question 8
select year(T.[Date]) , MA.[Manufacturer_Name] , sum([TotalPrice])from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_MODEL] MO on T.[IDModel] = MO.[IDModel]
inner join [dbo].[DIM_MANUFACTURER] MA on MO.[IDManufacturer] = MA.[IDManufacturer]
where (year(T.[Date]) = 2009 and
MA.[Manufacturer_Name] <> (select top 1 MA.[Manufacturer_Name] from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_MODEL] MO on T.[IDModel] = MO.[IDModel]
inner join [dbo].[DIM_MANUFACTURER] MA on MO.[IDManufacturer] = MA.[IDManufacturer]
where year(T.[Date]) = 2009
group by MA.[Manufacturer_Name]
order by sum([TotalPrice]) desc) and
MA.[Manufacturer_Name] in (select top 2 MA.[Manufacturer_Name] from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_MODEL] MO on T.[IDModel] = MO.[IDModel]
inner join [dbo].[DIM_MANUFACTURER] MA on MO.[IDManufacturer] = MA.[IDManufacturer]
where year(T.[Date]) = 2009
group by MA.[Manufacturer_Name]
order by sum([TotalPrice]) desc)) or
(year(T.[Date]) = 2010 and
MA.[Manufacturer_Name] <> (select top 1 MA.[Manufacturer_Name] from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_MODEL] MO on T.[IDModel] = MO.[IDModel]
inner join [dbo].[DIM_MANUFACTURER] MA on MO.[IDManufacturer] = MA.[IDManufacturer]
where year(T.[Date]) = 2010
group by MA.[Manufacturer_Name]
order by sum([TotalPrice]) desc) and
MA.[Manufacturer_Name] in (select top 2 MA.[Manufacturer_Name] from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_MODEL] MO on T.[IDModel] = MO.[IDModel]
inner join [dbo].[DIM_MANUFACTURER] MA on MO.[IDManufacturer] = MA.[IDManufacturer]
where year(T.[Date]) = 2010
group by MA.[Manufacturer_Name]
order by sum([TotalPrice]) desc))
group by year(T.[Date]) , MA.[Manufacturer_Name]
order by year(T.[Date]) , sum([TotalPrice]) desc
-- Question 9
select distinct(MA.Manufacturer_Name) from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_MODEL] MO on T.[IDModel] = MO.[IDModel]
inner join [dbo].[DIM_MANUFACTURER] MA on MO.[IDManufacturer] = MA.[IDManufacturer]
where MA.Manufacturer_Name not in
(select distinct(MA.Manufacturer_Name) from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_MODEL] MO on T.[IDModel] = MO.[IDModel]
inner join [dbo].[DIM_MANUFACTURER] MA on MO.[IDManufacturer] = MA.[IDManufacturer]
where year([Date]) = 2009) and MA.Manufacturer_Name in
(select distinct(MA.Manufacturer_Name) from [dbo].[FACT_TRANSACTIONS] T
inner join [dbo].[DIM_MODEL] MO on T.[IDModel] = MO.[IDModel]
inner join [dbo].[DIM_MANUFACTURER] MA on MO.[IDManufacturer] = MA.[IDManufacturer]
where year([Date]) = 2010)
-- Question 10
SELECT T1.Customer_Name, T1.Year, T1.Avg_Price,T1.Avg_Qty,
CASE
WHEN T2.Year IS NOT NULL
THEN FORMAT(CONVERT(DECIMAL(8,2),(T1.Avg_Price-T2.Avg_Price))/CONVERT(DECIMAL(8,2),T2.Avg_Price),'p') ELSE NULL
END AS '% Change_year'
FROM
(SELECT C.Customer_Name, YEAR(T.DATE) AS YEAR, AVG(T.TotalPrice) AS Avg_Price, AVG(T.Quantity) AS Avg_Qty FROM FACT_TRANSACTIONS AS T
inner join DIM_CUSTOMER as C ON T.IDCustomer=C.IDCustomer
where T.IDCustomer in (select top 100 IDCustomer
from FACT_TRANSACTIONS
group by IDCustomer
order by SUM(TotalPrice) desc)
group by C.Customer_Name, YEAR(T.Date)
)T1
left join
(SELECT C.Customer_Name, YEAR(T.DATE) AS YEAR, AVG(T.TotalPrice) AS Avg_Price, AVG(T.Quantity) AS Avg_Qty FROM FACT_TRANSACTIONS AS T
inner join DIM_CUSTOMER as C ON T.IDCustomer=C.IDCustomer
where T.IDCustomer in (select top 100 IDCustomer
from FACT_TRANSACTIONS
group by IDCustomer
order by SUM(TotalPrice) desc)
group by C.Customer_Name, YEAR(T.Date)
)T2
on T1.Customer_Name=T2.Customer_Name and T2.YEAR=T1.YEAR-1