DEV Community

Cover image for How To Group Result By A Particular TimeFrame E.g Weeks Using PostgreSQL
Joel Ndoh
Joel Ndoh

Posted on

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)
)
Enter fullscreen mode Exit fullscreen mode
  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')

Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  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
Enter fullscreen mode Exit fullscreen mode
  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

Enter fullscreen mode Exit fullscreen mode
  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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)