Daily Top 100 Ethereum Contracts

WITH
  contract_hour AS (
    SELECT
      contract_address,
      DATE_trunc('hour', block_time) datetime,
      COUNT(*) cnt
    FROM
      ethereum_mainnet.logs
    WHERE
      block_time >= NOW() - INTERVAL '10' YEAR
    GROUP BY
      2,
      1
  ),
  contract_hour_with_rank AS (
    SELECT
      h.contract_address,
      h.datetime,
      h.cnt AS activity_count,
      -- Rank() over (Partition BY DATETIME  
      --               ORDER BY DATETIME DESC ) AS Rank, 
      RANK() OVER (
        PARTITION BY h.datetime
        ORDER BY
          h.cnt DESC
      ) AS rnk
    FROM
      contract_hour h
  ),
  contract_hour_top_100 AS (
    SELECT
      *
    FROM
      contract_hour_with_rank
    WHERE
      rnk <= 100
  ) --- get contract info 
SELECT
  h.*,
  c.name,
  c.namespace
FROM
  contract_hour_top_100 h
  LEFT JOIN ethereum_mainnet.contracts c ON LOWER(h.contract_address) = LOWER(c.address)
ORDER BY
  h.datetime DESC,
  h.rnk ASC