[SQL] “Ranking most active guests” – AirBnB
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: