Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users (id, name) AS
SELECT 1, 'Eric' FROM DUAL;
1 rows affected

CREATE TABLE POST ( id, title, content, user_id) AS
SELECT 1, 'title1', 'Text...', 1 FROM DUAL UNION ALL
SELECT 2, 'title2', 'Text...', 1 FROM DUAL UNION ALL
SELECT 3, 'title3', 'Text...', 1 FROM DUAL UNION ALL
SELECT 4, 'title4', 'Text...', 5 FROM DUAL UNION ALL
SELECT 5, 'title5', 'Text...', 2 FROM DUAL UNION ALL
SELECT 6, 'title6', 'Text...', 1 FROM DUAL;
6 rows affected

CREATE TABLE ACTION_TYPE ( id, name ) AS
SELECT 1, 'updated'  FROM DUAL UNION ALL
SELECT 2, 'deleted'  FROM DUAL UNION ALL
SELECT 3, 'restored' FROM DUAL UNION ALL
SELECT 4, 'hided'    FROM DUAL;
4 rows affected

CREATE TABLE ACTION ( id, post_id, action_type_id, "DATE") AS
SELECT 1, 1, 1, DATE '2017-01-01' FROM DUAL UNION ALL
SELECT 2, 1, 1, DATE '2017-02-15' FROM DUAL UNION ALL
SELECT 3, 1, 3, DATE '2018-06-10' FROM DUAL UNION ALL
SELECT 4, 6, 1, DATE '2019-08-01' FROM DUAL UNION ALL
SELECT 5, 5, 2, DATE '2019-12-09' FROM DUAL UNION ALL
SELECT 6, 2, 3, DATE '2020-04-27' FROM DUAL UNION ALL
SELECT 7, 2, 1, DATE '2020-07-29' FROM DUAL UNION ALL
SELECT 8, 3, 2, DATE '2021-03-13' FROM DUAL;
8 rows affected

SELECT MAX(t.name) AS action_name,
       MAX(u.name) AS user_name,
       COUNT(*) AS number_posts
FROM   users u
       INNER JOIN post p
       ON (u.id = p.user_id)
       INNER JOIN (
         SELECT post_id,
                MAX(action_type_id) KEEP (DENSE_RANK LAST ORDER BY "DATE", id)
                  AS action_type_id
         FROM   action
         GROUP BY post_id
       ) a
       ON (p.id = a.post_id)
       INNER JOIN action_type t
       ON (t.id = a.action_type_id)
GROUP BY
      u.id,
      t.id


ACTION_NAME
USER_NAME
NUMBER_POSTS


restored
Eric
1

updated
Eric
2

deleted
Eric
1