WITH timeframe AS (

SELECT date_day AS date

FROM crosschain.core.dim_dates

WHERE date_day >= TO_DATE('2023-08-31') AND date_day <= TO_DATE('2023-08-31')

),

assets AS (

SELECT $1 AS asset

FROM (VALUES

('ETH'),

('USDC'),

('USDT'),

('DAI'),

('LINK'),

('MATIC'),

('WETH'),

('WBTC'),

('BTC')

)

),

prices AS (

SELECT TRUNC(block_timestamp, 'hour') AS hour, pool_name,

avg(asset_usd) AS asset_usd

FROM thorchain.price.fact_prices

GROUP BY hour, pool_name

UNION

SELECT TRUNC(block_timestamp, 'hour') AS hour, 'THOR.RUNE' AS pool_name,

avg(rune_usd) AS asset_usd

FROM thorchain.price.fact_prices

GROUP BY hour

),

swaps_0 AS (

SELECT from_address AS trader, tx_id, min(block_timestamp) AS block_timestamp,

UPPER((ARRAY_AGG(from_asset) WITHIN GROUP (ORDER BY event_id))[0]) AS from_asset,

UPPER(SPLIT(memo, ':')[1]) AS to_asset_manual,