with tb1 as (select DISTINCT tx_hash

from near.core.fact_actions_events_function_call

where receiver_id = 'tg'

and method_name = 'create_accounts'

and block_timestamp::date >= '2024-01-01')

,

tb2 as ( select block_timestamp,

PROJECT_NAME,

ifnull(tx:actions[0]:Delegate:delegate_action:sender_id::string, TX_SIGNER) as user,

ifnull(tx:actions[0]:Delegate:delegate_action:receiver_id::string, TX_RECEIVER) as receiver

from near.core.fact_transactions a left outer join near.core.dim_address_labels b on a.tx_receiver = b.address

where TX_SUCCEEDED = 'TRUE'

select block_timestamp,

'here wallet' as project_name,

receiver_id as user,

'game.hot.tg' as receiver

from near.core.fact_receipts

where tx_hash in (select tx_hash from tb1)

and receiver_id != 'tg'

,

tb3 as ( select block_timestamp,

case when project_name ilike '%ref%' then 'Ref Finance'

when receiver ilike 'tg' then 'here wallet'

when receiver ilike '%usmeme.tg%' then 'Usmeme'

when receiver ilike '%claim.sweat%' then 'sweat'

when receiver ilike '%token.sweat%' then 'sweat'

when receiver ilike '%meteor%' or receiver ilike '%harvest-moon%' then 'Meteor'

when receiver ilike '%firedrop.hot.tg%' then 'here wallet'

when receiver ilike '%embr.playember_reserve.near%' then 'playember'

when receiver ilike '%sendercommunity%' then 'Sender Wallet' else project_name end as project,

user,

receiver

from tb2