block_timestamp,

block_id,

date(block_timestamp) as block_date,

tx_id,

tx_status,

msg_value:execute_msg:liquidate_collateral:borrower::string as borrower

from terra.msgs

where msg_value:execute_msg:liquidate_collateral is not null

and tx_status = 'SUCCEEDED'

),

liquidation_values as (select

m.block_timestamp,

m.block_id,

date(m.block_timestamp) as block_date,

m.tx_id,

m.tx_status,

m.msg_value,

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

m.msg_value:execute_msg:submit_bid:premium_rate as premium_rate,

(m.msg_value:coins[0]:amount / pow(10, 6) / (1 - m.msg_value:execute_msg:submit_bid:premium_rate)) / 2 as approximate_borrow_value

from terra.msgs m

inner join liquidations l

on l.tx_id = m.tx_id

where m.msg_value:coins[0]:amount is not null)

sum(approximate_borrow_value) as total_borrowing_liquidated

from liquidation_values;