-- forked from aishwaryar / TVL - Plain & Liquid @ https://flipsidecrypto.xyz/aishwaryar/q/6uy91ECqY8AZ/tvl-plain-liquid

with deposit_txs as (

SELECT

msg_value:sender::string as user_ls,

date(block_timestamp) as date,

tx_id,

(msg_value:coins[0]:amount::float)/POW(10,6) as ls_staked

FROM

terra.msgs

WHERE

msg_value:contract::string in ('terra1xacqx447msqp46qmv8k2sq6v5jh9fdj37az898')

and msg_value:execute_msg:deposit is not null

and tx_status = 'SUCCEEDED'

GROUP BY 1,2,3,4

),

validator_of_deposit as (

SELECT

case

when event_attributes:validator::string in ('terravaloper1v5hrqlv8dqgzvy0pwzqzg0gxy899rm4kdur03x') then 'terravaloper1j27nm2gjm0m4lsye8lspa46rax0rw4fge23nnr'

else event_attributes:validator::string

end as validator_address,

--tx_id,

sum(case when event_attributes:amount[0]:denom::string in ('uluna') then event_attributes:amount[0]:amount end)/POW(10,6) as luna_deposited

FROM

terra.msg_events

WHERE

tx_status = 'SUCCEEDED'

and event_type in ('delegate')

and tx_id in (SELECT tx_id FROM deposit_txs)

group by 1--,2

),

lunax_minted as (

SELECT