SELECT

date(block_timestamp) as block_date,

currency,

symbol,

avg(price_usd) as price_usd

FROM terra.oracle_prices

WHERE

block_timestamp >= CURRENT_DATE - 1

GROUP BY block_date, currency, symbol

),

pair_labels as (select

*

from terra.labels

where address_name like '% Pair'

),

clean_transactions as (SELECT

date(block_timestamp) as block_date,

address_name,

substring(address_name, charindex(' ', address_name), 100) as short_address_name,

tx_id,

msg_value:execute_msg:swap:offer_asset:amount / POW(10, 6) as offer_amount,

msg_value:execute_msg:swap:offer_asset:info:native_token:denom as original_denom,

upper(substring(msg_value:execute_msg:swap:offer_asset:info:native_token:denom, 2, 100)) as denom,

symbol,

price_usd,

msg_value:execute_msg:swap:offer_asset:amount / POW(10, 6) * price_usd as transaction_offer_usd

FROM terra.msgs m

inner join pair_labels l

on m.msg_value:contract::string = l.address

inner join avg_prices a

on block_date = a.block_date

and msg_value:execute_msg:swap:offer_asset:info:native_token:denom = a.currency

where msg_value:execute_msg:swap is not null

and m.tx_status = 'SUCCEEDED')