from optimism.core.fact_event_logs

where origin_to_address = lower('0x722e9bfc008358ac2d445a8d892cf7b62b550f3f')

and event_name = 'Transfer')

,

deposit_tx as ( select tx_hash

from optimism.core.fact_event_logs

where origin_to_address = '0x79bc8bd53244bc8a9c8c27509a2d573650a83373'

and event_name = 'Mint')

,

deposit as ( select date(block_timestamp) as date, origin_from_address as user, tx_hash, EVENT_INPUTS:value/pow(10,6) as amount

from optimism.core.fact_event_logs

where origin_to_address = '0x79bc8bd53244bc8a9c8c27509a2d573650a83373'

and EVENT_INPUTS:to = '0x79bc8bd53244bc8a9c8c27509a2d573650a83373'

and origin_from_address = EVENT_INPUTS:from

and event_name = 'Transfer'

and tx_hash in (select tx_hash from deposit_tx))

,

withdraw_tx as ( select tx_hash

from optimism.core.fact_event_logs

where origin_to_address = '0x79bc8bd53244bc8a9c8c27509a2d573650a83373'

and event_name = 'Withdraw')

,

withdraw as ( select date(block_timestamp) as date, origin_from_address as user, tx_hash, EVENT_INPUTS:value/pow(10,6) as amount

from optimism.core.fact_event_logs

where origin_to_address = '0x79bc8bd53244bc8a9c8c27509a2d573650a83373'

and EVENT_INPUTS:from = '0x79bc8bd53244bc8a9c8c27509a2d573650a83373'

and origin_from_address = EVENT_INPUTS:to

and event_name = 'Transfer'

and tx_hash in (select tx_hash from withdraw_tx))

select 'Rewards' as type, trunc(date,'week') as weekly, count(DISTINCT(tx_hash)) as total_tx, count(DISTINCT(user)) as total_user, sum(amount) as volume,

sum(total_tx) over (order by weekly asc) as cum_tx, sum(volume) over (order by weekly asc) as cum_volume,

avg(amount) as avg_amount, max(amount) as max_amount, min(amount) as min_amount, median(amount) as median_amount,

avg(avg_amount) over (order by weekly desc rows between 7 preceding and CURRENT row) as avg_7_days,

avg(avg_amount) over (order by weekly desc rows between 30 preceding and CURRENT row) as avg_30_days,