from anchor.reward_claims

where block_timestamp between '2022-01-01' and '2022-01-31' and claim_1_amount > 0

group by 1),

claim as (select date_trunc('day', block_timestamp) as dt, sum(claim_1_amount) as claim_anc

from anchor.reward_claims

where block_timestamp between '2022-01-01' and '2022-01-31'

group by 1),

prov as (select date_trunc('day', block_timestamp) as dt, sum(event_attributes:assets[0]:amount/1e6) as provide_lp_anc

from terra.msg_events

WHERE event_attributes:"0_contract_address" = 'terra1qr2k6yjjd5p2kaewqvg93ag74k6gyjr7re37fs' AND event_attributes:"0_action" = 'provide_liquidity'

and event_type = 'from_contract' and block_timestamp between '2022-01-01' and '2022-01-31' and tx_status = 'SUCCEEDED'

and event_attributes:sender::string in (select sender from anc)

group by 1),

sold as (select date_trunc('day', block_timestamp) as dt ,sum(event_attributes:amount/1e6) as sold_anc

from terra.msg_events

where event_attributes:"1_action"::string = 'swap' and event_attributes:offer_asset::string = 'terra14z56l0fp2lsf86zy3hty2z47ezkhnthtr9yq76'

and block_timestamp between '2022-01-01' and '2022-01-31' and event_attributes:"1_contract_address"::string = 'terra1qr2k6yjjd5p2kaewqvg93ag74k6gyjr7re37fs'

and tx_status = 'SUCCEEDED' and event_attributes:sender::string in (select sender from anc)

group by 1),

final as (select a.dt, a.claim_anc, c.provide_lp_anc, d.sold_anc

from claim a

left join prov c on a.dt = c.dt

left join sold d on a.dt = d.dt),

stake as (select date_trunc('day', block_timestamp) as dt, sum(msg_value:execute_msg:send:amount/1e6) as stake_anc

from terra.msgs

where msg_value:sender::string in (select sender from anc) and msg_value:execute_msg:send:contract::string = 'terra1f32xyep306hhcxxxf7mlyh0ucggc00rm2s9da5'

and msg_value:contract::string = 'terra14z56l0fp2lsf86zy3hty2z47ezkhnthtr9yq76' and block_timestamp between '2022-01-01' and '2022-01-31'

group by 1)

select a.dt, a.claim_anc, a.provide_lp_anc, a.sold_anc, b.stake_anc