messenger sql

1.sql

WITH
  messenger AS (
  SELECT
    room_id,
    IF(sender_name='林熙哲', receiver_name, sender_name ) AS name,
    MIN(dt) AS first_day,
    COUNT(*) AS cnt
  FROM
    `facebook.messenger`
  where 
    receiver_name = '林熙哲'
  GROUP BY
    room_id, name
  ORDER BY
    cnt DESC
  LIMIT
    20 )
SELECT
  *,
  datetime_diff(
    current_datetime('Asia/Taipei'),first_day, day) AS days
FROM
  messenger

rank.sql

WITH
  messenger AS (
  SELECT
    format_datetime('%Y-%m', dt) AS month,
    IF(sender_name = '林熙哲', receiver_name, sender_name ) AS room_name,
    SUM(IF(sender_name = '林熙哲', 1, 0)) AS my_sent_cnt,
    SUM(IF(sender_name != '林熙哲',1,0)) AS friend_sent_cnt,
    COUNT(*) AS cnt
  FROM
    `facebook.messenger`
  GROUP BY
    month,
    room_name ),
  rank_table AS (
  SELECT
    *
  FROM (
    SELECT
      *,
      RANK() OVER (PARTITION BY month ORDER BY cnt DESC) AS rank
    FROM
      messenger )
  WHERE
    rank <= 10 )
SELECT
  month,
  max(IF(rank=1, room_name, "")) AS no1,
  max(IF(rank=2, room_name, "")) AS no2,
  max(IF(rank=3, room_name, "")) AS no3,
  max(IF(rank=4, room_name, "")) AS no4,
  max(IF(rank=5, room_name, "")) AS no5
FROM
  rank_table
group by month
ORDER BY
  month desc