with cname as (

select distinct contract_address, contract_name

from arbitrum.core.ez_decoded_event_logs

),

data as (

select

to_char(BLOCK_TIMESTAMP, 'YYYY-MM-DD') as time,

lb.contract_name,

count(DISTINCT ORIGIN_FROM_ADDRESS) AS Users,

count(DISTINCT TX_HASH) AS transactions,

count(DISTINCT TX_HASH) / count(DISTINCT ORIGIN_FROM_ADDRESS) AS "Average number of transactions per player"

from

arbitrum.core.fact_event_logs fl

left join cname lb

on lb.contract_address = fl.contract_address

where

BLOCK_TIMESTAMP < CURRENT_DATE

and

lb.contract_name is not null

GROUP by

to_char(BLOCK_TIMESTAMP, 'YYYY-MM-DD'),

lb.contract_name

select time,

RANK() OVER (PARTITION BY time ORDER BY Users DESC) AS Ranking,

contract_name,

Users,

transactions,

"Average number of transactions per player"

from data

ORDER BY

time DESC, Ranking