from near.core.fact_actions_events

where ACTION_NAME = 'FunctionCall'

and ACTION_DATA:"method_name" is not null

and block_timestamp::date >= '2023-01-01'

,

drops as (select DISTINCT tx_hash

from final

where receiver_id in ('contract.icebreak.near','v2.keypom.near','v1-4.keypom.near','hk-keypom.near','v1-3.keypom.near','testingkeypom','eth-toronto.keypom.near','nearcon.keypom.near','v1.keypom.near')

and method ilike '%claim%'

),

fnl as (select date(block_timestamp) as date, receiver_id as claimer, count(DISTINCT(tx_hash)) as redeem, sum(ACTION_DATA:"deposit"/pow(10,24)) as near_amounts

from near.core.fact_actions_events

where receiver_id not in ('contract.icebreak.near','v2.keypom.near','v1-4.keypom.near','hk-keypom.near','v1-3.keypom.near','testingkeypom','eth-toronto.keypom.near','nearcon.keypom.near','v1.keypom.near')

and block_timestamp::date >= '2023-01-01'

and tx_hash in (select tx_hash from drops)

group by 1,2)

select count(DISTINCT(claimer)) as claimers, avg(redeem) as avg_redeem, sum(redeem) as redeemed_link,

sum(near_amounts) as near, avg(near_amounts) as avg_near

from fnl

where claimer not in ('nft-v2.keypom.near','mint.sharddog.near','sharddog.near','social.near','nft-v2.keypom.near','near','stacksports.near','ssoperations.near','stacknft.near','dao-bot.keypom.near','build.sputnik-dao.near','keypom.near')