Postgres 14

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

CREATE TABLE product (product_code text, product_type text);
INSERT INTO product VALUES
  ('1', 'BOOK')	
, ('2', 'BOOK')
, ('3', 'BOOK')
, ('4', 'MOVIE')
, ('5', 'MUSIC')
, ('6', 'MUSIC')
, ('7', 'MUSIC')
, ('8', 'MUSIC')
, ('9', 'BOOK')
, ('10', 'MUSIC')
, ('11', 'BOOK')
, ('12', 'MUSIC')
, ('13', 'MOVIE')
, ('14', 'MOVIE')
, ('15', 'MOVIE')
, ('16', 'MOVIE')
, ('17', 'BOOK')
, ('18', 'MOVIE')
, ('19', 'MUSIC')
, ('20', 'MUSIC')
, ('21', 'MOVIE')
, ('22', 'MUSIC')
, ('23', 'MUSIC')
, ('24', 'MOVIE')
, ('25', 'MUSIC')
, ('26', 'BOOK')
, ('27', 'MUSIC')
, ('28', 'MOVIE')
, ('29', 'BOOK')
, ('30', 'BOOK')
;

CREATE TABLE sales (product_code text, qty int);
INSERT INTO sales VALUES
  ('1', 15)
, ('3', 12)
, ('5', 20)
, ('2', 14)
, ('12', 11)
, ('13', 19)
, ('16',  3)
, ('14',  9)
, ('15',  5)
, ('12',  5)
, ('1', 19)
, ('10',  3)
, ('13',  9)
, ('12',  5)
, ('29',  5)
, ('10', 19)
, ('13',  3)
, ('4',  9)
, ('3',  5)
, ('6',  5)
, ('15', 19)
, ('9',  3)
, ('1',  9)
, ('2',  5)
, ('7',  5)
, ('10', 19)
, ('8',  3)
, ('9',  9)
, ('8',  5)
, ('9',  5)
, ('6', 19)
, ('4',  3)
, ('11',  9)
, ('2',  5)
, ('8',  5)
, ('10', 19)
, ('7',  3)
, ('15',  9)
, ('8',  5)
, ('14',  5)
, ('6', 19)
, ('13',  3)
, ('1',  9)
, ('8',  5)
, ('8',  5)
;
30 rows affected

45 rows affected

-- Postgres 11+
SELECT product_code, s.qty
FROM   product p
JOIN   sales s USING (product_code)
JOIN   LATERAL (
   SELECT avg(qty) AS avg_orders
   FROM   product p1
   JOIN   sales s1 USING (product_code)
   WHERE  p1.product_type =  p.product_type
   AND    p1.product_code <> p.product_code
   ) a ON a.avg_orders < s.qty
ORDER  BY product_code;  -- optional


product_code
qty


1
19

1
9

1
15

1
9

10
19

10
19

10
19

11
9

12
11

13
9

13
19

14
9

15
19

15
9

2
14

3
12

4
9

5
20

6
19

6
19


-- At your request
SELECT product_code, qty
FROM  (
   SELECT s.*, avg(qty) OVER (PARTITION BY p.product_type ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) AS avg_orders
   FROM   product p
   JOIN   sales s USING (product_code)
   ) sub
WHERE  qty > avg_orders 
ORDER  BY product_code;  -- optional


product_code
qty


1
9

1
19

1
15

1
9

10
19

10
19

10
19

11
9

12
11

13
9

13
19

14
9

15
9

15
19

2
14

3
12

4
9

5
20

6
19

6
19

9
9

product_code	qty
1	19
1	9
1	15
1	9
10	19
10	19
10	19
11	9
12	11
13	9
13	19
14	9
15	19
15	9
2	14
3	12
4	9
5	20
6	19
6	19

product_code	qty
1	9
1	19
1	15
1	9
10	19
10	19
10	19
11	9
12	11
13	9
13	19
14	9
15	9
15	19
2	14
3	12
4	9
5	20
6	19
6	19
9	9

product_code	qty
1	19
1	9
1	15
1	9
10	19
10	19
10	19
11	9
12	11
13	9
13	19
14	9
15	19
15	9
2	14
3	12
4	9
5	20
6	19
6	19

product_code	qty
1	9
1	19
1	15
1	9
10	19
10	19
10	19
11	9
12	11
13	9
13	19
14	9
15	9
15	19
2	14
3	12
4	9
5	20
6	19
6	19
9	9

product_code	qty
1	19
1	9
1	15
1	9
10	19
10	19
10	19
11	9
12	11
13	9
13	19
14	9
15	19
15	9
2	14
3	12
4	9
5	20
6	19
6	19

product_code	qty
1	9
1	19
1	15
1	9
10	19
10	19
10	19
11	9
12	11
13	9
13	19
14	9
15	9
15	19
2	14
3	12
4	9
5	20
6	19
6	19
9	9