to_asset, from_amount_usd,

IFF(to_asset like '%/%' or from_asset like '%/%', 1, 0) as is_synth,

IFF(to_asset like '%~%' or from_asset like '%~%', 1, 0) as is_trade,

FROM thorchain.defi.fact_swaps

WHERE tx_id NOT IN (SELECT DISTINCT tx_id FROM thorchain.defi.fact_refund_events)),

total_vol AS ( SELECT day, sum(from_amount_usd) as total_vol_usd FROM init group by day),

synth_vol AS ( SELECT day, sum(from_amount_usd) as synth_vol_usd FROM init WHERE is_synth = 1 group by day),

l1_vol AS ( SELECT day, sum(from_amount_usd) as l1_vol_usd FROM init WHERE is_synth = 0 and is_trade = 0 group by day),

trade_asset_vol AS ( SELECT day, sum(from_amount_usd) as trade_asset_vol_usd FROM init WHERE is_trade = 1 group by day),

volume AS (SELECT a.day,

COALESCE(synth_vol_usd, 0) as synth_vol_usd,

COALESCE(trade_asset_vol_usd, 0) as trade_asset_vol_usd,

COALESCE(l1_vol_usd, 0) as l1_vol_usd,

total_vol_usd

FROM total_vol as a LEFT JOIN synth_vol as b ON a.day = b.day LEFT JOIN l1_vol as c on a.day = c.day LEFT JOIN trade_asset_vol as d on a.day = d.day)

select *,

SUM(total_vol_usd) OVER(ORDER BY day ASC) AS total_vol_usd_cumulative

from volume

WHERE day IS NOT null

order by day asc