with swaps_priced AS (

SELECT

swap_program,

block_timestamp,

swapper,

swap_from_asset_id,

a.asset_name as from_asset,

swap_from_amount*a.price_usd as from_usd,

swap_to_asset_id,

b.asset_name as to_asset,

swap_to_amount*b.price_usd as to_usd,

pool_address,

tx_group_id,

case when swap_to_asset_id IN ('312769', '31566704', '2757561', '465865291') then to_usd end as to_stable,

case when swap_from_asset_id IN ('312769', '31566704', '2757561', '465865291') then from_usd end as from_stable

FROM algorand.defi.fact_swap

LEFT JOIN algorand.defi.ez_price_swap a ON a.asset_id = swap_from_asset_id AND a.block_hour = date_trunc('hour', block_timestamp)

LEFT JOIN algorand.defi.ez_price_swap b ON b.asset_id = swap_to_asset_id AND b.block_hour = date_trunc('hour', block_timestamp)

WHERE block_timestamp > '2022-05-01' AND block_timestamp < '2022-07-01'

AND from_usd > 0 AND to_usd > 0

AND from_usd/to_usd < 2

AND to_usd/from_usd < 2

SELECT

date(block_timestamp) as date,

swap_program,

count(distinct swapper) as "Swappers",

count(swapper) as "Swap Count",

sum(from_usd) as "Swap Volume (USD)",

sum(case when swap_to_asset_id IN ('312769', '31566704', '2757561', '465865291') then to_usd end) as to_stable_usd,

sum(case when swap_from_asset_id IN ('312769', '31566704', '2757561', '465865291') then from_usd end) as from_stable_usd,

sum(to_stable) as "Stablecoin Volume",

"Stablecoin Volume"/"Swap Volume (USD)"*100 as "Stablecoins Dominance %"

FROM swaps_priced

GROUP BY 1,2