logo

Back to questions

Average Post Hiatus (Part 1) [Facebook SQL Interview Question]

Easy

Given a table of Facebook posts, for each user who posted at least twice in 2021, write a query to find the number of days between each user’s first post of the year and last post of the year in the year 2021. Output the user and number of the days between each user's first and last post.

p.s. If you've read the Ace the Data Science Interview and liked it, consider writing us a review?

Table:

Column NameType
user_idinteger
post_idinteger
post_datetimestamp
post_contenttext

Example Input:

user_idpost_idpost_datepost_content
15165259941507/10/2021 12:00:00Need a hug
66109362435607/29/2021 13:00:00Bed. Class 8-12. Work 12-3. Gym 3-5 or 6. Then class 6-10. Another day that's gonna fly by. I miss my girlfriend
00423978425407/04/2021 11:00:00Happy 4th of July!
66109344256007/08/2021 14:00:00Just going to cry myself to sleep after watching Marley and Me.
15165211176607/12/2021 19:00:00I'm so done with covid - need travelling ASAP!

Example Output:

user_iddays_between
1516522
66109321

The dataset you are querying against may have different input & output - this is just an example!

PostgreSQL 14