Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

--How to calculate median for selling_eur in this table?

create table your_table(id0,id,listing_id,category,amount,original_eur,selling_eur,user_id,rounds_id) 
  as values
(0,10088,'m1-1579cd77-6863-469d-8798-9e1bef199d2f','Category 2',2,'nan'::float,350.11,'eu-central-1:6fcfad76-ef06-4ea6-9e14-9fa4a5d62b8f',31)
,(1,10089,'m1-2adfde90-de27-4048-aa3e-bc7e76c03b0c','Prime Seats',10,'nan',962.81,'eu-central-1:6fcfad76-ef06-4ea6-9e14-9fa4a5d62b8f',28)
,(2,10090,'m3-024c20ad-e21d-4bb3-966e-44c69e760b15','Category 1',4,200,750,'eu-central-1:33d3dbe4-6414-4949-ae21-f2bc8819ae4d',29)
,(3,10091,'m1-c612ad6c-d000-4bb5-8dc4-55e3b49d54cd','Category 2',4,150,450,'eu-central-1:fe695442-13a6-42e6-a015-046415bf369d',28)
,(4,10092,'m1-cd449fb2-6134-428b-946a-fc8a7bec30e7','Category 1',4,'nan',647,'eu-central-1:b926a781-4fc0-4f71-bd0f-69b50b0be68d',28)
,(5,10093,'m4-eg449fb2-6134-428b-946a-fc8a7bec30e7','Category 1',4,'nan',649,'eu-central-1:b926a781-4fc0-4f71-bd0f-69b50b0be68d',28);
select * from your_table order by selling_eur;

SELECT 6




id0
id
listing_id
category
amount
original_eur
selling_eur
user_id
rounds_id


0
10088
m1-1579cd77-6863-469d-8798-9e1bef199d2f
Category 2
2
NaN
350.11
eu-central-1:6fcfad76-ef06-4ea6-9e14-9fa4a5d62b8f
31

3
10091
m1-c612ad6c-d000-4bb5-8dc4-55e3b49d54cd
Category 2
4
150
450
eu-central-1:fe695442-13a6-42e6-a015-046415bf369d
28

4
10092
m1-cd449fb2-6134-428b-946a-fc8a7bec30e7
Category 1
4
NaN
647
eu-central-1:b926a781-4fc0-4f71-bd0f-69b50b0be68d
28

5
10093
m4-eg449fb2-6134-428b-946a-fc8a7bec30e7
Category 1
4
NaN
649
eu-central-1:b926a781-4fc0-4f71-bd0f-69b50b0be68d
28

2
10090
m3-024c20ad-e21d-4bb3-966e-44c69e760b15
Category 1
4
200
750
eu-central-1:33d3dbe4-6414-4949-ae21-f2bc8819ae4d
29

1
10089
m1-2adfde90-de27-4048-aa3e-bc7e76c03b0c
Prime Seats
10
NaN
962.81
eu-central-1:6fcfad76-ef06-4ea6-9e14-9fa4a5d62b8f
28



SELECT 6


select percentile_cont(.5)within group(order by selling_eur2)
from your_table cross join lateral
(select generate_series(1,amount),selling_eur as selling_eur2);


percentile_cont


699.5



SELECT 1


with t as(
select *
  ,lead(selling_eur,1,0)over(order by selling_eur) next_selling
  ,sum(amount)over(order by selling_eur)-amount+1 from_amount
  ,sum(amount)over(order by selling_eur) to_amount
  ,sum(amount)over() tot_amount
  ,(sum(amount)over()+1)/2 mean_amount
  ,sum(selling_eur*amount)over() tot_value
from your_table
)
select tot_value/tot_amount avg_value
   ,case when mean_amount= to_amount  and(tot_amount/2)=to_amount
            then (selling_eur+next_selling)/2.0
    else selling_eur 
    end as mean_value
  , tot_amount,mean_amount
  ,from_amount, to_amount 
from t 
--where ((tot_amount+1)/2) between from_amount and to_amount


avg_value
mean_value
tot_amount
mean_amount
from_amount
to_amount


725.4400000000000000
350.11
28
14
1
2

725.4400000000000000
450
28
14
3
6

725.4400000000000000
647
28
14
7
10

725.4400000000000000
699.5000000000000000
28
14
11
14

725.4400000000000000
750
28
14
15
18

725.4400000000000000
962.81
28
14
19
28



SELECT 6


with t as(
select *
  ,lead(selling_eur,1,0)over(order by selling_eur) next_selling
  ,sum(amount)over(order by selling_eur)-amount+1 from_amount
  ,sum(amount)over(order by selling_eur) to_amount
  ,sum(amount)over() tot_amount
  ,(sum(amount)over()+1)/2 mean_amount
  ,sum(selling_eur*amount)over() tot_value
from your_table
)
select tot_value/tot_amount avg_value
   ,case when mean_amount= to_amount and(tot_amount/2)=to_amount
       then (selling_eur+next_selling)/2.0
    else selling_eur 
    end as mean_value
from t 
where ((tot_amount+1)/2) between from_amount and to_amount


avg_value
mean_value


725.4400000000000000
699.5000000000000000



SELECT 1


select row_number()over(order by selling_eur2)
      ,selling_eur
      ,id
      ,left(listing_id,6) as listing_id
      ,category
      ,amount
      ,n
      ,original_eur
from your_table cross join lateral
(select generate_series(1,amount)n,selling_eur as selling_eur2)
order by selling_eur2;


row_number
selling_eur
id
listing_id
category
amount
n
original_eur


1
350.11
10088
m1-157
Category 2
2
1
NaN

2
350.11
10088
m1-157
Category 2
2
2
NaN

3
450
10091
m1-c61
Category 2
4
1
150

4
450
10091
m1-c61
Category 2
4
4
150

5
450
10091
m1-c61
Category 2
4
3
150

6
450
10091
m1-c61
Category 2
4
2
150

7
647
10092
m1-cd4
Category 1
4
1
NaN

8
647
10092
m1-cd4
Category 1
4
4
NaN

9
647
10092
m1-cd4
Category 1
4
3
NaN

10
647
10092
m1-cd4
Category 1
4
2
NaN

11
649
10093
m4-eg4
Category 1
4
4
NaN

12
649
10093
m4-eg4
Category 1
4
1
NaN

13
649
10093
m4-eg4
Category 1
4
2
NaN

14
649
10093
m4-eg4
Category 1
4
3
NaN

15
750
10090
m3-024
Category 1
4
3
200

16
750
10090
m3-024
Category 1
4
4
200

17
750
10090
m3-024
Category 1
4
1
200

18
750
10090
m3-024
Category 1
4
2
200

19
962.81
10089
m1-2ad
Prime Seats
10
7
NaN

20
962.81
10089
m1-2ad
Prime Seats
10
6
NaN

21
962.81
10089
m1-2ad
Prime Seats
10
5
NaN

22
962.81
10089
m1-2ad
Prime Seats
10
4
NaN

23
962.81
10089
m1-2ad
Prime Seats
10
3
NaN

24
962.81
10089
m1-2ad
Prime Seats
10
2
NaN

25
962.81
10089
m1-2ad
Prime Seats
10
1
NaN

26
962.81
10089
m1-2ad
Prime Seats
10
10
NaN

27
962.81
10089
m1-2ad
Prime Seats
10
9
NaN

28
962.81
10089
m1-2ad
Prime Seats
10
8
NaN



SELECT 28

id0	id	listing_id	category	amount	original_eur	selling_eur	user_id	rounds_id
0	10088	m1-1579cd77-6863-469d-8798-9e1bef199d2f	Category 2	2	NaN	350.11	eu-central-1:6fcfad76-ef06-4ea6-9e14-9fa4a5d62b8f	31
3	10091	m1-c612ad6c-d000-4bb5-8dc4-55e3b49d54cd	Category 2	4	150	450	eu-central-1:fe695442-13a6-42e6-a015-046415bf369d	28
4	10092	m1-cd449fb2-6134-428b-946a-fc8a7bec30e7	Category 1	4	NaN	647	eu-central-1:b926a781-4fc0-4f71-bd0f-69b50b0be68d	28
5	10093	m4-eg449fb2-6134-428b-946a-fc8a7bec30e7	Category 1	4	NaN	649	eu-central-1:b926a781-4fc0-4f71-bd0f-69b50b0be68d	28
2	10090	m3-024c20ad-e21d-4bb3-966e-44c69e760b15	Category 1	4	200	750	eu-central-1:33d3dbe4-6414-4949-ae21-f2bc8819ae4d	29
1	10089	m1-2adfde90-de27-4048-aa3e-bc7e76c03b0c	Prime Seats	10	NaN	962.81	eu-central-1:6fcfad76-ef06-4ea6-9e14-9fa4a5d62b8f	28

avg_value	mean_value	tot_amount	mean_amount	from_amount	to_amount
725.4400000000000000	350.11	28	14	1	2
725.4400000000000000	450	28	14	3	6
725.4400000000000000	647	28	14	7	10
725.4400000000000000	699.5000000000000000	28	14	11	14
725.4400000000000000	750	28	14	15	18
725.4400000000000000	962.81	28	14	19	28

row_number	selling_eur	id	listing_id	category	amount	n	original_eur
1	350.11	10088	m1-157	Category 2	2	1	NaN
2	350.11	10088	m1-157	Category 2	2	2	NaN
3	450	10091	m1-c61	Category 2	4	1	150
4	450	10091	m1-c61	Category 2	4	4	150
5	450	10091	m1-c61	Category 2	4	3	150
6	450	10091	m1-c61	Category 2	4	2	150
7	647	10092	m1-cd4	Category 1	4	1	NaN
8	647	10092	m1-cd4	Category 1	4	4	NaN
9	647	10092	m1-cd4	Category 1	4	3	NaN
10	647	10092	m1-cd4	Category 1	4	2	NaN
11	649	10093	m4-eg4	Category 1	4	4	NaN
12	649	10093	m4-eg4	Category 1	4	1	NaN
13	649	10093	m4-eg4	Category 1	4	2	NaN
14	649	10093	m4-eg4	Category 1	4	3	NaN
15	750	10090	m3-024	Category 1	4	3	200
16	750	10090	m3-024	Category 1	4	4	200
17	750	10090	m3-024	Category 1	4	1	200
18	750	10090	m3-024	Category 1	4	2	200
19	962.81	10089	m1-2ad	Prime Seats	10	7	NaN
20	962.81	10089	m1-2ad	Prime Seats	10	6	NaN
21	962.81	10089	m1-2ad	Prime Seats	10	5	NaN
22	962.81	10089	m1-2ad	Prime Seats	10	4	NaN
23	962.81	10089	m1-2ad	Prime Seats	10	3	NaN
24	962.81	10089	m1-2ad	Prime Seats	10	2	NaN
25	962.81	10089	m1-2ad	Prime Seats	10	1	NaN
26	962.81	10089	m1-2ad	Prime Seats	10	10	NaN
27	962.81	10089	m1-2ad	Prime Seats	10	9	NaN
28	962.81	10089	m1-2ad	Prime Seats	10	8	NaN