SELECT

date_trunc(day,block_timestamp) as day,

sum(

case

when msg_value:execute_msg:ledger_proxy:msg:execute_order IS NOT NULL THEN (msg_value:execute_msg:ledger_proxy:msg:execute_order:order:order:taker_asset:amount::int)/1000000 -- post data change

when msg_value:execute_msg:execute_order IS NOT NULL THEN (msg_value:execute_msg:execute_order:order:order:taker_asset:amount::int)/1000000 -- pre-december data change

end

)

as re_luna_sale_amount

from terra.msgs

where msg_value:contract::string = 'terra1eek0ymmhyzja60830xhzm7k7jkrk99a60q2z2t'

and tx_status = 'SUCCEEDED'

and (msg_value:execute_msg:execute_order is not null or msg_value:execute_msg:ledger_proxy:msg:execute_order is not null)

group by day

),

kwsubinfo AS (

SELECT DISTINCT

tx_id,

event_attributes:"1_contract_address"::string AS contract

FROM terra.msg_events

WHERE event_attributes:"0_contract_address"::string = 'terra12v8vrgntasf37xpj282szqpdyad7dgmkgnq60j' --the contract address for knowhere

AND event_attributes:"0_action"::string = 'settle'

AND event_type = 'wasm'

),

kwsettle AS (

SELECT

DATE_TRUNC('day', block_timestamp) as day,

kwsubinfo.contract,

sum(

(nvl(event_attributes:"0_amount"[0]:amount::float,0)

+ nvl(event_attributes:"1_amount"[0]:amount::float,0)

+ nvl(event_attributes:"2_amount"[0]:amount::float,0)) / 1e6

) AS totalAmount