Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE products ( product_id, creation_date, user_id, category_id ) AS
SELECT 'p1', DATE '2017-03-01', 'u1', 'c1' FROM DUAL UNION ALL
SELECT 'p2', DATE '2018-05-23', 'u1', 'c3' FROM DUAL UNION ALL
SELECT 'p3', DATE '2019-06-21', 'u2', 'c1' FROM DUAL;
3 rows affected

CREATE TABLE messages( message_id, creation_date, product_id, user_from ) AS
SELECT 'm1', DATE '2018-03-01', 'p1', 'u2' FROM DUAL UNION ALL
SELECT 'm2', DATE '2019-08-19', 'p1', 'u5' FROM DUAL UNION ALL
SELECT 'm3', DATE '2020-10-10', 'p3', 'u7' FROM DUAL;
3 rows affected

SELECT p.category_id,
       p.product_id,
       m.total_messages_for_product,
       m.user_from AS user_id,
       m.messages
FROM   products p
       INNER JOIN (
         SELECT product_id,
                user_from,
                COUNT(*) AS messages,
                SUM( COUNT(*) ) OVER ( PARTITION BY product_id )
                  AS total_messages_for_product,
                RANK() OVER (
                  PARTITION BY product_id ORDER BY COUNT(*) DESC
                ) AS messages_rank
         FROM   messages
         GROUP BY product_id, user_from
       ) m
       ON ( p.product_id = m.product_id )
WHERE  m.messages_rank <= 5;


CATEGORY_ID
PRODUCT_ID
TOTAL_MESSAGES_FOR_PRODUCT
USER_ID
MESSAGES


c1
p1
2
u5
1

c1
p1
2
u2
1

c1
p3
1
u7
1