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

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)
;
9 rows affected

9 rows affected

-- Postgres 11+
SELECT product_code, orders
FROM  (
   SELECT s.*, avg(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)
   ) sub
WHERE  orders > avg_orders 
ORDER  BY product_code;  -- optional


product_code
orders


ref02
12

ref06
20

ref07
11

ref10
19


-- At your request, without window function
SELECT product_code, s.orders
FROM   product p
JOIN   sales s USING (product_code)
JOIN   LATERAL (
   SELECT avg(orders) 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.orders
ORDER  BY product_code;  -- optional


product_code
orders


ref02
12

ref06
20

ref07
11

ref10
19