Postgres: Subquery with GROUP BY I'm trying to optimize a query (instead of repeating it a lot of time), with this NOT FUNCTIONAL CODE above (since subqueries only return 1 column):

SELECT
    e.pageview_identifier,
    e.created_at,
    e.pageview_current_url,
    e.pageview_mobile,
    (
        SELECT event_type, COUNT(event_identifier)
        FROM events v
        WHERE
            v.company_identifier = e.company_identifier AND
            v.user_identifier = e.user_identifier AND
            v.pageview_identifier = e.pageview_identifier
        GROUP BY v.event_type
    )
FROM events e
WHERE
    company_identifier = 'xyz' AND
    user_identifier = '01CDQZVSJFBDA8W444JS2CS3BA' AND
    event_type = 'page:view';


Basically, I want to retrieve the columns as

pageview_identifier, created_at, ..., event_type_a_count, event_type_b_count, ...


A FUNCTIONAL code that works is:

SELECT
    e.pageview_identifier,
    e.created_at,
    e.pageview_current_url,
    e.pageview_mobile,
    (
        SELECT COUNT(event_identifier)
        FROM events v
        WHERE
            v.company_identifier = e.company_identifier AND
            v.user_identifier = e.user_identifier AND
            v.pageview_identifier = e.pageview_identifier AND
            v.event_type = 'mouse:move'
    ) as mouse_move_count
FROM events e
WHERE
    company_identifier = 'xyz' AND
    user_identifier = '01CDQZVSJFBDA8W444JS2CS3BA' AND
    event_type = 'page:view';


But in this case, I would need to repeat a lot of time this subquery for each kind of event_type.
Was solved using window functions, like:

SELECT e.*
FROM (SELECT e.pageview_identifier,
             e.created_at,
             e.pageview_current_url,
             e.pageview_mobile,
             e.event_type,
             COUNT(*) FILTER (WHERE e.event_type = 'mouse:move') OVER (PARTITION BY e.company_identifier, e.user_identifier, e.pageview_identifier) as cnt_mouse_move,
             ...
      FROM events e
      WHERE e.company_identifier = 'xyz' AND
          e.user_identifier = '01CDQZVSJFBDA8W444JS2CS3BA' 
     ) e
WHERE e.event_type = 'page:view';