Meta-style Company ChallengeHardVerified answerSQLite live

Average Time Between Posts

For users who have posted at least twice, what is their average gap in days between consecutive posts?

  • CTEs
  • Window functions
  • Joins
  • Subqueries
  • Aggregation

Challenge brief

Understand the request

Feed Algorithm Team is modelling creator posting cadence to predict content droughts and improve feed freshness notifications.

Calculate average inter-post gap days for multi-post users using LAG() window function and two CTEs.

Return

  • username
  • total_posts
  • avg_gap_days (rounded to 1 decimal)

Constraints

  • Return users with at least two posts
  • Measure gaps between consecutive posts for the same user
  • Show the shortest average gap first

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

users

  • user_idINTEGER
  • usernameVARCHAR(50)

posts

  • post_idINTEGER
  • user_idINTEGER
  • created_atDATETIME

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) gives each row the previous post's timestamp. Subtract using julianday() to get the gap in days. Build two CTEs: one for raw gaps, one to average them per user.

Hint 2

CTE post_gaps: user_id, created_at, LAG(created_at) OVER (...) AS prev_post_date, julianday(created_at) - julianday(LAG(...)) AS gap_days FROM posts. CTE user_gap_stats: WHERE prev_post_date IS NOT NULL, GROUP BY user_id, AVG(gap_days), COUNT(*)+1. Main: JOIN users, WHERE total_posts >= 2.

Hint 3

Start with the tables that establish the result grain for question 9, select the required output aliases, and add the remaining joins, filters, aggregation, and ordering one clause at a time.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH post_gaps AS (SELECT user_id, created_at, LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_post_date, julianday(created_at) - julianday(LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at)) AS gap_days FROM posts), user_gap_stats AS (SELECT user_id, COUNT(*) + 1 AS total_posts, ROUND(AVG(gap_days), 1) AS avg_gap_days FROM post_gaps WHERE prev_post_date IS NOT NULL GROUP BY user_id) SELECT u.username, ugs.total_posts, ugs.avg_gap_days FROM user_gap_stats ugs INNER JOIN users u ON ugs.user_id = u.user_id WHERE ugs.total_posts >= 2 ORDER BY ugs.avg_gap_days ASC, u.username ASC;

Why this works

LAG returns NULL for each user's first post (no previous row). WHERE prev_post_date IS NOT NULL filters those out. COUNT(*)+1 compensates: 2 posts produce 1 gap row, so +1 gives total_posts = 2.

Success check

2 users — john_doe (1.2 day avg gap), jane_smith (1.9 day avg gap)

Expected result

Use this output to verify values, aliases, ordering, and row count.

usernametotal_postsavg_gap_days
john_doe21.2
jane_smith21.9

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.