Days Between Each User's Latest Two Posts
Zip● MediumFULLTIMEPHONE SCREENProblem statement
You are given a users table and a posts table from a forum. For every user who has at least one post, return that user's current post and the number of calendar days since the immediately previous post.
The current post is the user's latest post by creation_date. When dates tie, the larger post_id is later. The previous post is the next row in that same ordering.
Return user_id, display_name, current_creation_date, and days_since_previous_post. A user with exactly one post has a day difference of 0. Users with no posts are omitted.
Order rows by current_creation_date from newest to oldest, then by user_id ascending.
Table schema
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
users
| Column | Type | Nullable | Description |
|---|---|---|---|
| user_idPK | Integer | No | — |
| display_name | Text | No | — |
posts
| Column | Type | Nullable | Description |
|---|---|---|---|
| post_idPK | Integer | No | — |
| owner_user_id | Integer | No | — |
| creation_date | Date | No | — |
Foreign key: owner_user_id → users(user_id)
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| user_id | Integer | No | — |
| display_name | Text | No | — |
| current_creation_date | Date | No | — |
| days_since_previous_post | Integer | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
users.user_idandposts.post_idare unique.- Every
posts.owner_user_idreferences an existing user. creation_dateis a calendar date.- A larger
post_idis later when one user's creation dates tie. - Users with no posts are omitted; users with one post have a day difference of
0.