SELECT

TRUNC(block_timestamp, 'day') AS date,

AVG(price_usd) AS price_usd,

currency

FROM

terra.oracle_prices

GROUP BY

date,

currency

),

currency_swaps AS (

SELECT

TRUNC(block_timestamp, 'day') AS date,

SUM(msg_value:coins[0]:amount) / POW(10, 6) AS amount,

msg_value:coins[0]:denom AS denom,

msg_value:contract::string AS contract_address

FROM terra.msgs m

WHERE

msg_value:coins[0]:amount IS NOT NULL AND

msg_value:execute_msg:swap IS NOT NULL AND

tx_status = 'SUCCEEDED' AND

block_timestamp >= ADD_MONTHS(CURRENT_DATE, -6)

GROUP BY

date,

denom,

contract_address

),

currency_swaps_in_usd AS (

SELECT

c.date,

contract_address,

SUM(amount * p.price_usd) AS amount_usd

FROM