day-12.sql

-- SQL Advent Calendar - Day 12
-- Title: North Pole Network Most Active Users
-- Difficulty: hard
--
-- Question:
-- The North Pole Network wants to see who's the most active in the holiday chat each day. Write a query to count how many messages each user sent, then find the most active user(s) each day. If multiple users tie for first place, return all of them.
--
-- The North Pole Network wants to see who's the most active in the holiday chat each day. Write a query to count how many messages each user sent, then find the most active user(s) each day. If multiple users tie for first place, return all of them.
--

-- Table Schema:
-- Table: npn_users
--   user_id: INT
--   user_name: VARCHAR
--
-- Table: npn_messages
--   message_id: INT
--   sender_id: INT
--   sent_at: TIMESTAMP
--

-- My Solution:

With Daily_Message_Count AS (
  select DATE(m.sent_at) as message_date, u.user_id, count(m.message_id) as Message_count
  from npn_messages as m
  join npn_users as u on m.sender_id = u.user_id 
  group by 1,2
),
Rank_Users as(
  select user_id, message_date, Message_count ,
  rank() over(partition by message_date order by Message_count desc) as rnk
  from Daily_Message_Count
)
select user_id , message_date, Message_count
from Rank_Users
where rnk = 1;