select

PROJECT_NAME,

count (distinct TX_HASH) as num_txs,

count (distinct origin_from_address) as num_users

from optimism.core.fact_event_logs l , optimism.core.dim_labels b --on l.origin_to_address = b.address

where tx_status = 'SUCCESS' and block_timestamp::date>='2022-01-01' and block_timestamp::date<CURRENT_DATE

and l.origin_to_address = b.address

group by PROJECT_NAME

order by 2 DESC

limit 8

)

select date_trunc(week,block_timestamp):: date as date ,

case when b.PROJECT_NAME in (select PROJECT_NAME from a) then PROJECT_NAME else 'Others' end as PROJECT_NAME_,

count (distinct TX_HASH) as num_txs,

sum(num_txs) over(partition by PROJECT_NAME_ order by date rows between unbounded preceding and current row ) as cumulative_num_txs

,count (distinct origin_from_address) as num_users

,sum(num_users) over(partition by PROJECT_NAME_ order by date rows between unbounded preceding and current row ) as cumulative_num_users

from optimism.core.fact_event_logs l , optimism.core.dim_labels b --on l.origin_to_address = b.address

where tx_status = 'SUCCESS'

and block_timestamp::date>='2022-01-01' and block_timestamp::date<CURRENT_DATE

and l.origin_to_address = b.address

group by 1,2

order by 1