How To Group Result By A Particular TimeFrame E.g Weeks Using PostgreSQL

We have a database of posts, comments and likes. The likes table has a field which states if the like is for a comment or for a post.

The task is to return the number of likes on comments and posts created in each week.

1. Let us create all 3 tables

create table likes(
    id serial primary key,
    post_id integer,
    comment_id integer,
    created_at timestamp
);

create table comments(
    id serial primary key,
    created_at timestamp,
    contents varchar(20)
);

create table posts(
    id serial primary key, 
    created_at timestamp,
    caption varchar(40)
)

1. Let us insert values into the 3 different tables

insert into likes(created_at,post_id, comment_id)
values('2015-09-11 07:38:03.774+01', 2, null),
('2013-07-29 23:15:00.764+01', null, 1),
('2016-05-24 04:06:32.237+01', 2, null),
('2016-11-30 11:17:04.954+01', 1, null),
('2015-04-18 18:48:01.439+01', null, 3);

insert into posts(created_at, caption)
values('2015-09-11 07:38:03.774+01', 'he'),
('2013-07-29 23:15:00.764+01', 'kie'),
('2016-05-24 04:06:32.237+01', 'foe'),
('2016-11-30 11:17:04.954+01', 'eijw'),
('2015-04-18 18:48:01.439+01', 'jne');

insert into comments(created_at, contents)
values('2015-09-11 07:38:03.774+01', 'ew'),
('2013-07-29 23:15:00.764+01', 'ie'),
('2016-02-24 04:06:32.237+01', 'eiw'),
('2016-07-30 11:17:04.954+01', '303'),
('2015-04-18 18:48:01.439+01', 'ij')

Then we will go into the hard part

First of all, you will realize that whenever we have data in the comment_id column of the likes table, the post_id field will have a default data of null. This is also done vice versa.

1. We will join the posts table to the likes table using a left join. This is to ensure that no likes record is left behind

left join posts on posts.id = likes.post_id

1. We will also join the comments tables to the likes table using the left join. This is to ensure that no likes record is left behind.

left join comments on comments.id = likes.comment_id

1. Then we will group the joined table using the week constraint.

select date_trunc('week', coalesce(posts.created_at, comments.created_at))as week from likes

1. We will then count the number likes and comments for each week

select count(comments.created_at) as liked_comment, count(posts.created_at) as liked_posts from likes

There you have it, the final result.

select date_trunc('week', coalesce(posts.created_at, comments.created_at))as week, count(comments.created_at) as liked_comment, count(posts.created_at) as liked_posts from likes
left join posts on posts.id = likes.post_id
left join comments on comments.id = likes.comment_id
group by week
order by week desc

If you have any questions, feel free to leave a comment and I will respond as fast as possible.