Latest Tweets
We'll write a query which is a basic necessity for Tweeter: the news feed!
Given a user ID, we need to generate their news feed, which is a list of recent tweets from users they follow.
Our growth analysts say that people don't like to see more than 2 tweets from the same user in their feed at any given time.
So, we need to select all the users someone follows, and their (at most) 2 most recent tweets.
For now, let's just write a query to do this for user ID 15.
tweeters
| column_name | type |
|---|---|
id | INTEGER |
username | VARCHAR |
email | VARCHAR |
registered_at | TIMESTAMPTZ |
tweets
| column_name | type | reference |
|---|---|---|
id | INTEGER | NULL |
body | TEXT | NULL |
user_id | INTEGER | tweeters.id |
created_at | TIMESTAMPTZ | NULL |
follows
| column_name | type | reference |
|---|---|---|
follower_id | INTEGER | tweeters.id |
followed_id | INTEGER | tweeters.id |
created_at | TIMESTAMPTZ | NULL |
Select all users that user 15 follows (all columns), as well as the (up to) 2 most recent tweets from each user.
Order the results by the newest first, oldest last.
