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
  ('ref01', 'BOOKS' )
, ('ref02', 'ALBUMS')
, ('ref06', 'BOOKS' )
, ('ref04', 'BOOKS' )
, ('ref07', 'ALBUMS')
, ('ref10', 'TOYS'  )
, ('ref13', 'TOYS'  )
, ('ref09', 'ALBUMS')
, ('ref29', 'TOYS'  )
-- , ('ref02', 'ALBUMS')  -- error!
;

CREATE TABLE sales (product_code text, orders int);
INSERT INTO sales VALUES
  ('ref01', 15)
, ('ref02', 12)
, ('ref06', 20)
, ('ref04', 14)
, ('ref07', 11)
, ('ref10', 19)
, ('ref13',  3)
, ('ref09',  9)
, ('ref29',  5)
, ('ref02',  4)
;
9 rows affected

10 rows affected

-- Efficient query for Postgres 11+
SELECT product_code, orders
FROM  (
   SELECT product_code, sum(s.orders) AS orders
        , avg(sum(s.orders)) 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)
   GROUP  BY product_code, p.product_type
   ) sub
WHERE  avg_orders * 1.2 < orders
ORDER  BY product_code;  -- optional


product_code
orders


ref02
16

ref06
20

ref10
19


-- At your request, without CTE and window function (inefficient!)
SELECT product_code, ps.orders
FROM  (
   SELECT product_code, p.product_type, sum(s.orders) AS orders
   FROM   product p
   JOIN   sales   s USING (product_code)
   GROUP  BY product_code, p.product_type
   ) ps
JOIN   LATERAL (
   SELECT avg(orders) AS avg_orders
   FROM  (
      SELECT sum(s1.orders) AS orders
      FROM   product p1
      JOIN   sales   s1 USING (product_code)
      WHERE  p1.product_type =  ps.product_type
      AND    p1.product_code <> ps.product_code
      GROUP  BY product_code
      ) sub
   ) a ON a.avg_orders * 1.2 < ps.orders
ORDER  BY product_code;  -- optional


product_code
orders


ref02
16

ref06
20

ref10
19