SQL-Practice-and-Projects/r.sql at main · Anjali2718/SQL-Practice-and-Projects · 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
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
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
-- window functions
-- q23: total sales of all orders
select
    orderid,
    sales,
    sum(sales) over () as total_sales
from orders;

-- q24: total sales per customer
select
    orderid,
    customerid,
    sales,
    sum(sales) over (partition by customerid) as total_by_customer
from orders;

-- q25: rank orders based on sales (highest first)
select
    orderid,
    sales,
    rank() over (order by sales desc) as sales_rank
from orders;

-- q26: highest sales order for each customer
select *
from (
    select
        *,
        row_number() over (partition by customerid order by sales desc) as rn
    from orders
) t
where rn = 1;

-- q27: running total of sales based on order id
select
    orderid,
    sales,
    sum(sales) over (order by orderid) as running_total
from orders;

-- q28: average quantity per product
select
    productid,
    avg(quantity) over (partition by productid) as avg_quantity
from orderdetails;

-- q29: difference between each sale and maximum sale
select
    orderid,
    sales,
    sales - max(sales) over () as diff_from_max
from orders;

-- ranking

-- q30: assign row numbers to orders
select
    orderid,
    row_number() over (order by orderid) as row_num
from orders;

-- q31: rank customers based on total sales
select
    customerid,
    rank() over (order by sum(sales) desc) as customer_rank
from orders
group by customerid;

-- q32: dense rank products based on total quantity sold
select
    productid,
    dense_rank() over (order by sum(quantity) desc) as product_rank
from orderdetails
group by productid;

-- cte (common table expressions)

-- q33: total sales per customer using cte
with cte as (
    select
        customerid,
        sum(sales) as total_sales
    from orders
    group by customerid
)
select *
from cte;

-- q34: rank customers based on total sales using cte
with cte as (
    select
        customerid,
        sum(sales) as total_sales
    from orders
    group by customerid
)
select
    *,
    rank() over (order by total_sales desc) as sales_rank
from cte;

-- q35: generate numbers from 1 to 20 using recursive cte
with nums as (
    select 1 as n
    union all
    select n + 1
    from nums
    where n < 20
)
select *
from nums;

-- q36: find last order date for each customer
with last_order as (
    select
        customerid,
        max(orderdate) as last_order
    from orders
    group by customerid
)
select *
from last_order;

-- =========================
-- window functions
-- =========================

-- q23: total sales of all orders
select
    orderid,
    sales,
    sum(sales) over () as total_sales
from orders;

-- q24: total sales per customer
select
    orderid,
    customerid,
    sales,
    sum(sales) over (partition by customerid) as total_by_customer
from orders;

-- q25: rank orders based on sales (highest first)
select
    orderid,
    sales,
    rank() over (order by sales desc) as sales_rank
from orders;

-- q26: highest sales order for each customer
select *
from (
    select
        *,
        row_number() over (partition by customerid order by sales desc) as rn
    from orders
) t
where rn = 1;

-- q27: running total of sales based on order id
select
    orderid,
    sales,
    sum(sales) over (order by orderid) as running_total
from orders;

-- q28: average quantity per product
select
    productid,
    avg(quantity) over (partition by productid) as avg_quantity
from orderdetails;

-- q29: difference between each sale and maximum sale
select
    orderid,
    sales,
    sales - max(sales) over () as diff_from_max
from orders;


-- =========================
-- ranking
-- =========================

-- q30: assign row numbers to orders
select
    orderid,
    row_number() over (order by orderid) as row_num
from orders;

-- q31: rank customers based on total sales
select
    customerid,
    rank() over (order by sum(sales) desc) as customer_rank
from orders
group by customerid;

-- q32: dense rank products based on total quantity sold
select
    productid,
    dense_rank() over (order by sum(quantity) desc) as product_rank
from orderdetails
group by productid;


-- =========================
-- cte (common table expressions)
-- =========================

-- q33: total sales per customer using cte
with cte as (
    select
        customerid,
        sum(sales) as total_sales
    from orders
    group by customerid
)
select *
from cte;

-- q34: rank customers based on total sales using cte
with cte as (
    select
        customerid,
        sum(sales) as total_sales
    from orders
    group by customerid
)
select
    *,
    rank() over (order by total_sales desc) as sales_rank
from cte;

-- q35: generate numbers from 1 to 20 using recursive cte
with nums as (
    select 1 as n
    union all
    select n + 1
    from nums
    where n < 20
)
select *
from nums;

-- q36: find last order date for each customer
with last_order as (
    select
        customerid,
        max(orderdate) as last_order
    from orders
    group by customerid
)
select *
from last_order;