Hottest Tweet

We want to show off the best Tweets we have! For this challenge, we'll select all users and for each one, their best tweet. We'll consider "best" to mean "most liked."

Users are in the tweeters table and tweets are in the tweets table.

Table Name:

tweeters

`column_name`	`type`
id	INTEGER
username	VARCHAR
email	VARCHAR
registered_at	TIMESTAMPTZ

Table Name:

tweets

`column_name`	`type`	`reference`
id	INTEGER	NULL
body	TEXT	NULL
user_id	INTEGER	tweeters.id
created_at	TIMESTAMPTZ	NULL

When a user likes a tweet, it creates a row in the likes table.

Table Name:

likes

`column_name`	`type`	`reference`
user_id	INTEGER	tweeters.id
tweet_id	INTEGER	tweets.id
created_at	TIMESTAMPTZ	NULL

Select all rows and columns of tweeters, and add a column, hottest_tweet, which contains the body of their tweet with the most likes. If there are multiple tweets tied for most liked, take the more recent one.

Next
WHERE IN

Try running a query…

	`id` `INTEGER`	`username` `VARCHAR`	`email` `VARCHAR`	`registered_at` `TIMESTAMPTZ`	`hottest_tweet` `TEXT`
1	1	deonte_murray45	deonte_murray57@hotmail.com	2021-08-02 08:54:42.061+00	parsing the monitor won't do anything, we need to index the primary SMS transmitter!
2	2	hilda.grant53	hilda_grant48@hotmail.com	2016-04-24 13:52:43.58+00	You can't copy the array without indexing the primary SMS card!
3	3	brisa_jenkins39	brisa_jenkins@hotmail.com	2014-06-12 05:12:17.099+00	You can't hack the bandwidth without quantifying the auxiliary SQL firewall!
4	4	dax1	dax.bode@gmail.com	2018-07-17 11:50:09.979+00	Try to input the SQL port, maybe it will back up the solid state bandwidth!
5	5	annabelle74	annabelle_erdman83@hotmail.com	2016-07-27 14:03:26.745+00	If we input the monitor, we can get to the IB transmitter through the primary HDD transmitter!
6	6	berry.yost15	berry_yost19@gmail.com	2018-04-13 13:24:45.756+00	We need to connect the cross-platform SMTP application!
7	7	ethan67	ethan_kuhn@gmail.com	2019-05-31 06:42:38.464+00	Try to program the SMS interface, maybe it will generate the cross-platform matrix!
8	8	trevion_schuppe15	trevion_schuppe83@gmail.com	2017-07-21 21:15:58.491+00	We need to navigate the wireless SQL panel!
9	9	enrico.durgan1	enrico45@gmail.com	2015-09-02 18:36:33.47+00	I'll back up the mobile AI circuit, that should array the EXE hard drive!
10	10	madge.wehner	madge_wehner@yahoo.com	2021-02-15 14:22:34.017+00	We need to back up the back-end AGP pixel!
11	11	judy.blick60	judy14@yahoo.com	2014-02-11 02:53:02.395+00	generating the system won't do anything, we need to quantify the optical SQL firewall!
12	12	marjorie.thompson	marjorie_thompson99@gmail.com	2015-03-29 01:28:47.113+00	If we connect the interface, we can get to the EXE capacitor through the bluetooth JBOD pixel!
13	13	elinore56	elinore11@hotmail.com	2015-12-05 12:43:36.332+00	Try to bypass the SAS feed, maybe it will bypass the 1080p application!
14	14	manuel_torp	manuel79@gmail.com	2015-05-16 11:41:28.533+00	Use the back-end PNG port, then you can index the cross-platform port!
15	15	ryley.denesik98	ryley36@gmail.com	2021-05-21 21:56:12.281+00	NULL
15 rows

You haven't solved this challenge yet!
Are you sure you want to reveal the answer?

SELECT
  *,
  (
    SELECT t.body
    FROM tweets t
    LEFT JOIN likes l
      ON l.tweet_id = t.id
    WHERE t.user_id = tweeters.id
    GROUP BY t.created_at, t.body
    ORDER BY count(l.*) DESC, t.created_at DESC
    LIMIT 1
  ) AS hottest_tweet
FROM tweeters;