Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table "user" (id serial);

create table comment (user_id int, created_on date);

create table likes (user_id int, created_on date);
insert into "user" default values;
insert into "user" default values;
insert into "user" default values;

insert into comment select 1 + (random() * 2)::int, '2021-10-01'::date + (random()*100)::int from generate_series(1, 50) x(n);

insert into likes select 1 + (random() * 2)::int,  '2021-10-01'::date + (random()*100)::int from generate_series(1, 1000) x(n);
1 rows affected

1 rows affected

1 rows affected

50 rows affected

1000 rows affected

CREATE FUNCTION get_user_interaction_counts
(
   date_start date,
   date_end date
)
RETURNS TABLE(user_id int, like_count int, comment_count int) as $$
SELECT u.id, likes.likes, comments.comments
FROM "user" u
JOIN (SELECT user_id, count(*) as likes
    FROM likes l
    WHERE l.created_on > date_start AND l.created_on <= date_end
    GROUP BY l.user_id
) as likes ON u.id = likes.user_id
JOIN (
    SELECT user_id, count(*) as comments
    FROM comment c
    WHERE c.created_on > date_start AND c.created_on <= date_end
    GROUP BY c.user_id
) as comments ON u.id = comments.user_id

$$ LANGUAGE SQL;
select * from get_user_interaction_counts('2021-10-01', '2021-10-30')


user_id
like_count
comment_count


1
84
1

2
127
6

3
64
4


WITH parameters AS (
  SELECT '2021-10-01'::date as date_start
       , '2021-10-30'::date as date_end
)
SELECT u.id, likes.likes, comments.comments
FROM "user" u
JOIN (SELECT user_id, count(*) as likes
    FROM likes l
    JOIN parameters par
      ON l.created_on > par.date_start AND l.created_on <= par.date_end
    GROUP BY l.user_id
) as likes ON u.id = likes.user_id
JOIN (
    SELECT user_id, count(*) as comments
    FROM comment c
    JOIN parameters par
      ON c.created_on > par.date_start AND c.created_on <= par.date_end
    GROUP BY c.user_id
) as comments ON u.id = comments.user_id


id
likes
comments


1
84
1

2
127
6

3
64
4