-
Notifications
You must be signed in to change notification settings - Fork 0
/
rank.sql
30 lines (28 loc) · 1.13 KB
/
rank.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
--- Rank : les deux meilleurs ratings en face de chaque modèle en les comparant au price avg
-- Data
select p.model_id modelId,
o.id offerId,
o.price,
o.seller,
r.score
from product p
join offer o on o.id = p.offer_id
join model m on p.model_id = m.id
join rating r on m.id = r.model_id
where p.model_id in (1, 2);
--Analytic
select modelId, offerId, price, seller, score, priceAvg, ratingRn
from (
select o.id offerId,
p.model_id modelId,
o.price,
o.seller,
r.score,
round(avg(o.price) over (partition by p.model_id), 2) priceAvg,
row_number() over (partition by p.model_id order by r.score desc) ratingRn
from product p
join offer o on o.id = p.offer_id
join model m on p.model_id = m.id
join rating r on m.id = r.model_id
where p.model_id in (1, 2)) subquery
where ratingRn <= 2;