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.
- 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)
)
- 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.
- 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
- 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
- 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
- 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.
Top comments (0)