logo

Back to questions

Average Post Hiatus (Part 2)

Hard

You are given a table of Facebook posts from users who posted at least twice in 2021. Write a query to find the average number of days between each user’s posts during 2021.

Output the user id and the average number of the days between posts.

Assumptions:

  • A user can post several times a day.
  • When calculating the differences between dates, output the component of the time in the form of days. For example, 10 days or 5.5 days.

Table:

Column NameType
user_idinteger
post_idinteger
post_datetimestamp
post_contenttimestamp

Example Input:

user_idpost_idpost_datepost_content
15165259941507/10/2021 12:00:00Need a hug
15165299415607/15/2021 12:00:00Does anyone have an extra iPhone charger to sell?
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.5
66109321

Explanation: User 661903 had 2 days between the first and the second post; and 3 days between the second and the third post. Thus, the average is 2.5 days.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.