SQL at Work #3: Ranking Most Active Guests

This is a practice solution for StrataScratch question ID 10159.

Not all guests engage in the same way. Some might send just one message, while others may send many. This query ranks guests by the number of messages they’ve sent, making it easy to see who is most active. This ranking can help host or platform teams to improve response times, build trust with guests, or pinpoint those who may require a little extra help.

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

Something new I learned

Window functions like DENSE_RANK() are applied after GROUP BY is done. It means we can reference SUM(n_messages) directly in the ORDER BY clause without needing a CTE or subquery to pre-compute it.