Postgres 16

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

create table "table"(email text,quantity int);
insert into "table"values
  ('email1',-2),
  ('email2',-1),
  ('email3',0),
  ('email4',1),
  ('email5',1),
  ('email6',1),
  ('person1@domain1.com',2),
  ('person1@domain1.com',4),
  ('person2@domain1.com',3)
returning *;

CREATE TABLE




email
quantity


email1
-2

email2
-1

email3
0

email4
1

email5
1

email6
1

person1@domain1.com
2

person1@domain1.com
4

person2@domain1.com
3



INSERT 0 9


SELECT
    AVG(quantity) AS avg_quantity,
    email,
    (SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY quantity) FROM "table")
FROM "table" 
GROUP BY email
HAVING AVG(quantity) > (SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY quantity) FROM "table");


avg_quantity
email
percentile_disc


3.0000000000000000
person2@domain1.com
1

3.0000000000000000
person1@domain1.com
1



SELECT 2


with _(median) as materialized (
  SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY quantity) FROM "table")
SELECT AVG(quantity) AS avg_quantity,
       email,
       (SELECT median from _)
FROM "table" 
GROUP BY email
HAVING AVG(quantity) > (SELECT median from _);


avg_quantity
email
median


3.0000000000000000
person2@domain1.com
1

3.0000000000000000
person1@domain1.com
1



SELECT 2


SELECT AVG(quantity) AS avg_quantity,
       email,
       median
FROM "table"
,(SELECT PERCENTILE_DISC(0.5)WITHIN GROUP(ORDER BY quantity) FROM "table") 
  AS _(median)
GROUP BY email,median
HAVING AVG(quantity) > median;


avg_quantity
email
median


3.0000000000000000
person2@domain1.com
1

3.0000000000000000
person1@domain1.com
1



SELECT 2


/*
WITH tmp AS(
    SELECT
        AVG(quantity) AS avg_quantity,
        email   
    FROM "table"  
    GROUP BY email
    HAVING AVG(quantity) > (SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY quantity) FROM "table" )
)
SELECT tmp.*, PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY "table".quantity)  
from tmp, "table"  
*/

email	quantity
email1	-2
email2	-1
email3	0
email4	1
email5	1
email6	1
person1@domain1.com	2
person1@domain1.com	4
person2@domain1.com	3

avg_quantity	email	percentile_disc
3.0000000000000000	person2@domain1.com	1
3.0000000000000000	person1@domain1.com	1