with liquidation_tx as (

tx_id

from terra.msgs

where msg_value:execute_msg:liquidate_collateral is not null

and tx_status = 'SUCCEEDED'

),

liquidation_values as (select

tx_id,

To_date(msg.block_timestamp) as block_date,

msg.msg_value,

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

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

(msg.msg_value:coins[0]:amount / pow(10, 6) / (1 - msg.msg_value:execute_msg:submit_bid:premium_rate))* 3 / 2 as approx_borrow_amount

from terra.msgs msg

join liquidation_tx ltx

USING(tx_id)

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

daily_liquidations as (

block_date,SUM(approx_borrow_amount) as liquidated_borrows

from liquidation_values GROUP BY 1 )