[SQL] “Ranking most active guests” – AirBnB

Photo by Digital Buggu on Pexels

This SQL interview question from AirBnB deals with the some users, or guests, and their correspondences with AirBnB hosts. Our goal here would be to find, sort and rank these guests, based on the total number of messages that they have exchanged on this platform.

Here is the solution to this puzzle:

SELECT DENSE_RANK() OVER (ORDER BY SUM(n_messages) DESC),
id_guest, 
SUM(n_messages) AS total
FROM airbnb_contacts
GROUP BY id_guest
ORDER BY total DESC

And one can find the full explanation of the puzzle and its solution here:

Related Images: