Tweeter Features
You are a data engineer at "Tweeter", a social media site similar to Twitter. A data scientist on your team wants to experiment with training machine learning models on new features. They think the following features would be useful for each user:
- How many "short tweets" a user likes - A "short tweet" is 70 characters or less.
- How many "recent users" a user follows - A "recent user" is one that registered their account on or after Jan 1st, 2018.
Remember that the tweeters (i.e. users) are in the tweeters table and their tweets are in the tweets table.
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 |
There are a couple of junction tables that you'll need as well.
When a user likes a tweet, there is a row in the likes table.
likes
| column_name | type | reference |
|---|---|---|
user_id | INTEGER | tweeters.id |
tweet_id | INTEGER | tweets.id |
created_at | TIMESTAMPTZ | NULL |
Finally, when a user follows another user, there is a row in the follows table.
follows
| column_name | type | reference |
|---|---|---|
follower_id | INTEGER | tweeters.id |
followed_id | INTEGER | tweeters.id |
created_at | TIMESTAMPTZ | NULL |
Select the following for all tweeters:
- Their
username. - The number of "short tweets" (length <= 70 characters) they like, aliased to
short_tweets_liked. - The number of "recent users" (registered on or after Jan 1st, 2018) they follow, aliased to
short_tweets_liked.
