Repository navigation
Expand file tree
/
Copy path15. Real Time Scenarios.sql
More file actions
72 lines (52 loc) · 1.67 KB
/
Copy path15. Real Time Scenarios.sql
File metadata and controls
72 lines (52 loc) · 1.67 KB
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
use ecom;
-- REAL TIME SCENARIOS
-- SCENARIO 1 : Finding the nth value
select * from dim_product;
-- We have to get 5th most expensive product we have in our company
select subquery.*
from
(select
*,
dense_rank() over (order by unit_price desc) as ranking
from dim_product) as subquery
where ranking = 5;
-- We have to get 5th most expensive product in each category our company
select subquery.*
from
(select
*,
dense_rank() over (partition by category order by unit_price desc) as ranking
from dim_product) as subquery
where ranking = 5;
-- SCENARIO 2 : Removing duplicates
create table customer(
id int,
name varchar(40),
email varchar(50)
);
insert into customer values (101,'athira','aa'),(201,'aswathi','bb'),(301,'anuvind','cc'),(201,'aswathi','bb');
select * from customer;
-- We have to do this using row_numbers().
-- Approach : We apply partitioning on Key column. Counting resets at each different key, and we can filter out all keys greater that 1.
select
subquery.*
from (select
*,
row_number() over (partition by id order by id) as dedup
from
customer) as subquery
where dedup = 1;
-- SCENARIO 3 : Lag and Lead (Window functions)
-- Lag and lead functions are used to get preceeding value and following value.
create table weather(
id int,
temp float);
insert into weather values (1,10),(2,12),(3,9),(4,15),(5,20),(7,12);
select * from weather;
-- Create a 2 column for each row, it should display temperature of the 2 day's prior (default value 0) and temperature of next day.
select
*,
lag(temp,2,0) over (order by id asc) as prev_day_temp,
lead(temp,1) over (order by id asc) as next_day_temp
from
weather;