sum(msg_value:execute_msg:provide_liquidity:assets[0]:amount/1e6) as anc,

msg_value:sender::string as sender

from terra.msgs

where msg_value:execute_msg:provide_liquidity::string is not null

and msg_value:contract::string = 'terra1gm5p3ner9x9xpwugn9sp6gvhd0lwrtkyrecdn3' and block_timestamp >= '2021-09-01' and tx_status = 'SUCCEEDED'

group by 1,3),

anc as (select dt, sender, anc as anc_supplied

from lpers

),

claim as (select date_trunc('month', block_timestamp) as dt,

event_attributes:"to"::string as claimer,

sum(event_attributes:"0_amount"/1e6) as amount_claimed

from terra.msg_events

where event_attributes:"0_contract_address"::string = 'terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s'

and event_type = 'wasm' and date(block_timestamp) >= '2021-09-01' and tx_status = 'SUCCEEDED'

and event_attributes:"0_action"::string = 'claim_rewards' and claimer in (select sender from lpers)

group by 1,2),

final as (select a.dt, a.sender, sum(a.anc_supplied) as ANC_Supplied, sum(b.amount_claimed) as ANC_Claimed

from anc a

left outer join claim b on a.sender = b.claimer

group by 1,2),

counts as (select dt, sender, ANC_supplied, case when ANC_claimed is null then 0 else ANC_claimed end as ANC_rewards_claimed, round((ANC_Claimed/ANC_supplied),2) as ratio_claimed,

case when ratio_claimed is null then 'Bought_ANC'

when ratio_claimed between 0.9 and 1 then 'Supply_All_Claimed_ANC'

when ratio_claimed < 0.9 then 'Claim+Bought_ANC'

when ratio_claimed > 1 then 'Supply_Some_Rewards_ANC'

else null end as LP_Attributes

from final)

select dt, lp_attributes, count(distinct sender) as COUNts

from counts