select

distinct(tx_id) as tx_id

, msg_value

from terra.msgs

where substring(msg_value:execute_msg::string, 3, 20) = 'liquidate_collateral'

and tx_status = 'SUCCEEDED'

),

liq_tx as (

select

date_trunc('day', block_timestamp) as date

, ltx.tx_id

, ltx.msg_value:execute_msg:liquidate_collateral:borrower as borrower

, m.msg_value:coins[0]:amount / POW(10, 6) as liquidated_amount

, substring(m.msg_value:coins[0]:denom, 2, 4) as denom

--, m.msg_value:execute_msg:borrow_stable:borrow_amount /POW(10,6) as borrowed_amount --

from terra.msgs m

inner join unique_liq_tx ltx

on ltx.tx_id = m.tx_id

where m.msg_value:execute_msg:submit_bid is not null

--and m.msg_value:execute_msg:borrow_stable IS NOT NULL

order by date asc, liquidated_amount desc

),

borrow as (

select

date_trunc('day', BLOCK_TIMESTAMP) as date

, sum(msg_value:execute_msg:borrow_stable:borrow_amount /POW(10,6)) as borrow_amount

from terra.msgs

where msg_value:execute_msg:borrow_stable is not null

and tx_status = 'SUCCEEDED'

group by 1

select tx.date

, tx.denom

, count(tx.tx_id) as tx_count