SQL группирует продукты и сообщения по

Вы, кажется, хотите:

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;

(Примечание: вместо этого вы можете использовать ROW_NUMBER, чтобы получить первые 5 записей без совпадений, а не RANK, которое возвращает первые 5 записей с совпадениями.)

Что для ваших образцов данных:

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;

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;

Выходы:

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

db‹›fiddle здесь

Что-то вроде этого (не проверено, так как вы не предоставили тестовые данные в пригодной для использования форме):

with
  agg (product_id, user_from, messages, total_messages_for_product) as (
    select product_id, user_from, count(*),
           sum(count(*)) over (partition by product_id)
    from   messages
    group  by product_id, user_from
  )
select p.category_id, a.product_id, a.total_messages_for_product,
       a.user_from, a.messages
from   products p join agg a on p.product_id = a.product_id
order  by product_id, user_from   --  if/as needed
;

Основная работа выполняется в подзапросе agg (используется только таблица messages). Обратите внимание на использование аналитической функции sum(), разделенной по продуктам, для получения общего количества сообщений по продуктам. Затем вы получаете category_id, присоединившись к таблице products.