Average Post Hiatus (Part 1) — SQL Question Asked by Facebook

  • Post last modified:September 11, 2022
  • Reading time:2 mins read

Photo by Caspar Camille Rubin on Unsplash

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.

Leave a Reply