from terra.msgs

where msg_value:contract::string = 'terra1atch4d5t25csx7ranccl48udq94k57js6yh0vk' and tx_status = 'SUCCEEDED' and msg_value:execute_msg:claim:amount is not null

group by 1

order by 2 desc),

dump as (select msg_value:sender::string as claimer, sum(msg_value:execute_msg:send:amount/1e6) as amount_dump

from terra.msgs

where msg_value:contract::string = 'terra1nef5jf6c7js9x6gkntlehgywvjlpytm7pcgkn4' and msg_value:execute_msg:send:contract::string = 'terra106a00unep7pvwvcck4wylt4fffjhgkf9a0u6eu'

and msg_value:sender::string in (select claimer from raw) and tx_status = 'SUCCEEDED'

group by 1),

lp as (select msg_value:sender::string as claimer, sum(msg_value:execute_msg:provide_liquidity:assets[0]:amount/1e6) as amount_LOOP, sum(msg_value:execute_msg:provide_liquidity:assets[1]:amount/1e6) as amount_UST,

concat(amount_loop, ' $LOOP-', amount_ust, ' $UST') as LP_provide

from terra.msgs

where msg_value:contract::string = 'terra106a00unep7pvwvcck4wylt4fffjhgkf9a0u6eu' and msg_value:execute_msg:provide_liquidity:assets[0]:info:token:contract_addr::string = 'terra1nef5jf6c7js9x6gkntlehgywvjlpytm7pcgkn4'

and msg_value:sender::string in (select claimer from raw) and tx_status = 'SUCCEEDED'

group by 1),

transfer as (select msg_value:sender::string as claimer,

sum(msg_value:execute_msg:transfer:amount/1e6) as transfer

from terra.msgs

where msg_value:contract = 'terra1nef5jf6c7js9x6gkntlehgywvjlpytm7pcgkn4'

and msg_value:execute_msg:transfer is not null

and tx_status = 'SUCCEEDED'

group by 1),

buy as (select event_attributes:to::string as claimer, sum(event_attributes:return_amount/1e6) as buy

from terra.msg_events

where event_attributes:"0_action" = 'swap' and event_attributes:"1_contract_address" = 'terra1nef5jf6c7js9x6gkntlehgywvjlpytm7pcgkn4'

and event_attributes:ask_asset = 'terra1nef5jf6c7js9x6gkntlehgywvjlpytm7pcgkn4' and tx_status = 'SUCCEEDED' and event_type = 'from_contract'

group by 1)

select a.claimer, a.LOOP_claimed, b.amount_dump,

case when b.amount_dump > (a.LOOP_claimed/2) then '1' else null end as Dump_More_Than_Half,

case when b.amount_dump = a.LOOP_claimed then '1' else null end as Dump_All,