Introduction
- In this article we will solve Average Post Hiatus (Part 1) SQL question which is asked by Facebook as per DataLemur website.
If you don’t know DatLemur then please do visit , it’s one of the best website to practice SQL questions and improve your SQL skills.
Question
- We have been given posts table that contains list of all the posts made by facebook users.
- We need to filter out each users who have made at-least 2 post in 2021 and days between first post and last post.
Solution
- We will group by user_id and get the max_date and min_date from post_date, once we have max_date and min_date we can calculate difference between these two days.
- We also have to filter only posts those are made in 2021. We added the condition in where clause.
- Now we can execute main query on subquery and get the the cases where difference between first_day and last_day is not 0. if its 0 then that means first post and last post is same day .
Final Solution
select * from (
select user_id, max(date(post_date)) - min(date(post_date)) days_between from posts
where post_date >='01/01/2021' and post_date<'01/01/2022'
group by user_id
) x where days_between > 0
Submission
- Our solution is accepted by platform.
Conclusion
- In this article we solved sql question asked by facebook. We learned how we can use subquery in sql and operate over timestamp field.
Bonus Tip
- If you want to upskill your SQL interview game, you can definitely check out this bestseller course