with init AS (SELECT to_date(block_timestamp) AS day, blockchain, from_asset,

from_e8 / POWER(10, 8) AS from_amount,

WHEN from_asset LIKE '%/%' THEN REPLACE(from_asset, '/', '.')

ELSE from_asset

END AS asset_name

FROM thorchain.defi.fact_swaps_events),

prices AS (select to_date(block_timestamp) AS day, pool_name, avg(asset_usd) AS asset_usd

FROM thorchain.price.fact_prices GROUP BY day, pool_name),

rune_prices AS (select to_date(hour) AS day, avg(price) AS rune_usd

FROM thorchain.price.ez_prices_hourly GROUP BY day),

usd_vol AS (SELECT a.day, blockchain, asset_name, from_amount,

CASE

WHEN from_asset = 'THOR.RUNE' THEN rune_usd

WHEN from_asset = 'THOR.TOR' THEN 0

ELSE asset_usd

END AS from_asset_usd

FROM init AS a LEFT JOIN prices AS b ON a.day = b.day AND a.asset_name = b.pool_name

LEFT JOIN rune_prices AS c ON a.day = c.day),

liq_volume AS (SELECT to_date(block_timestamp) as day, pool_name,SPLIT_PART(pool_name, '.', 0) as chain,

rune_amount_usd, ASSET_AMOUNT_USD,

WHEN rune_amount_usd <> 0 AND asset_amount_usd <> 0 THEN 0

ELSE (rune_amount_usd + asset_amount_usd) / 2

END AS liq_volume_usd2

FROM thorchain.defi.fact_liquidity_actions

WHERE liq_volume_usd2 > 0),

swap_vol AS (SELECT day, blockchain, sum(from_amount * from_asset_usd) AS swap_volume_usd2 FROM usd_vol group by day, blockchain),