select

date_trunc('day', block_timestamp) as block_day

, contract_address

, case

when contract_address = '0xd784927ff2f95ba542bfc824c8a8a98f3495f6b5' then 'incentives_controller'

when contract_address = '0xa1116930326d21fb917d5a27f1e9943a9595fb47' then 'balancer_pool'

when contract_address = '0x4da27a545c0c5b758a6ba100e3a049001de870f5' then 'stkAAVE'

end as contract_name

, case

when contract_address = '0xd784927ff2f95ba542bfc824c8a8a98f3495f6b5' then 'incentives_controller'

when contract_address = '0xa1116930326d21fb917d5a27f1e9943a9595fb47' then 'ecosystem_reserve'

when contract_address = '0x4da27a545c0c5b758a6ba100e3a049001de870f5' then 'ecosystem_reserve'

end as from_address

, sum(event_inputs:amount) / 1e18 as amount

from ethereum.core.fact_event_logs

where event_name = 'RewardsClaimed'

and date_trunc('day', block_timestamp) >= '2022-04-01'

and contract_address in ('0xd784927ff2f95ba542bfc824c8a8a98f3495f6b5', '0xa1116930326d21fb917d5a27f1e9943a9595fb47', '0x4da27a545c0c5b758a6ba100e3a049001de870f5')

and tx_status = 'SUCCESS'

group by block_day, contract_address, contract_name

)

, staging as (

select

block_day

, from_address

, case when contract_name = 'balancer_pool' then amount else 0 end as balancer_claims

, case when contract_name = 'incentives_controller' then amount else 0 end as incentives_claims

, case when contract_name = 'stkAAVE' then amount else 0 end as stkaave_claims

from claims

)

select

block_day

, 'mainnet' as network