[Stratascratch - Medium] Ranking Most Active Guests (순위 윈도우 함수 RANK/DENSE_RANK, 집계함수 SUM)

Stratascratch - Airbnb interview question 1970

Problem

Difficulty : Medium

Rank guests based on the number of messages they've exchanged with the hosts.

• Guests with the same number of messages as other guests should have the same rank. Do not skip rankings if the preceding rankings are identical. : DENSE_RANK()
• Output the rank, guest id, and number of total messages they've sent.
• Order by the highest number of total messages first.

My Solution

Approach

1. 레코드 하나의 행이 UNIQUE id_guest 인지 확인 ⇒ 집계 그룹 확인중복된 id_guest 존재 ⇒ id_guest & id_host 대화 로그 레코드 한 행에 기록됨
2. SELECT COUNT(*) FROM airbnb_contacts -- 100 SELECT COUNT(DISTINCT id_guest) FROM airbnb_contacts -- 96 SELECT COUNT(DISTINCT id_guest, id_host) FROM airbnb_contacts -- 96
3. 순위 윈도우 함수 *DENSE_RANK()* 로 문제 조건에 맞는 순위 산출
4. 출력 필드 순서, 정렬 순서에 맞게 명령문 작성

Submission

-- Select the necessary fields we need : `id_guest` `n_messages`
-- Output the rank, guest id, and number of total messages they've sent. 
SELECT
    DENSE_RANK() OVER (ORDER BY SUM(n_messages) DESC) AS `ranking`,
    id_guest,
    SUM(n_messages) AS `sum_n_messages`
FROM airbnb_contacts
-- AGGREGATE sum_n_messages by id_guest
GROUP BY 2
-- Order by the highest number of total messages first.
ORDER BY 3 DESC

Lessons Learned

CTE/SubQuery 없이 구현할 수 없을까 고민하기