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',4,'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)
;

SELECT 5


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


647



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
2
NaN

2
350.11
10088
m1-157
Category 2
2
1
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
4
NaN

8
647
10092
m1-cd4
Category 1
4
1
NaN

9
647
10092
m1-cd4
Category 1
4
2
NaN

10
647
10092
m1-cd4
Category 1
4
3
NaN

11
750
10090
m3-024
Category 1
4
1
200

12
750
10090
m3-024
Category 1
4
2
200

13
750
10090
m3-024
Category 1
4
3
200

14
750
10090
m3-024
Category 1
4
4
200

15
962.81
10089
m1-2ad
Prime Seats
4
1
NaN

16
962.81
10089
m1-2ad
Prime Seats
4
4
NaN

17
962.81
10089
m1-2ad
Prime Seats
4
3
NaN

18
962.81
10089
m1-2ad
Prime Seats
4
2
NaN



SELECT 18


select sum( selling_eur * amount ) / sum ( amount) as "average"
      ,( max(selling_eur)filter(where floor(middle_index)::int8 <@ int8range)
        +max(selling_eur)filter(where  ceil(middle_index)::int8 <@ int8range)
       )/2. as "median"
from(select *,int8range(sum(amount)over w1 -amount+1,
                        sum(amount)over w1,'[]')
             ,(1.+sum(amount)over())/2. as middle_index
     from your_table
     window w1 as (order by selling_eur) ) as a;


average
median


663.3033333333333333
647.0000000000000000



SELECT 1

row_number	selling_eur	id	listing_id	category	amount	n	original_eur
1	350.11	10088	m1-157	Category 2	2	2	NaN
2	350.11	10088	m1-157	Category 2	2	1	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	4	NaN
8	647	10092	m1-cd4	Category 1	4	1	NaN
9	647	10092	m1-cd4	Category 1	4	2	NaN
10	647	10092	m1-cd4	Category 1	4	3	NaN
11	750	10090	m3-024	Category 1	4	1	200
12	750	10090	m3-024	Category 1	4	2	200
13	750	10090	m3-024	Category 1	4	3	200
14	750	10090	m3-024	Category 1	4	4	200
15	962.81	10089	m1-2ad	Prime Seats	4	1	NaN
16	962.81	10089	m1-2ad	Prime Seats	4	4	NaN
17	962.81	10089	m1-2ad	Prime Seats	4	3	NaN
18	962.81	10089	m1-2ad	Prime Seats	4	2	NaN