select block_timestamp::date as date, tx_group_id, swapper, swap_from_asset_id, swap_from_amount, swap_to_asset_id, swap_to_amount

from flipside_prod_db.algorand.swaps

where block_timestamp::date between '2022-05-11' and '2022-05-12'

), prices as (

select block_hour::date as date, asset_id, asset_name, avg(price_usd) as price

from flipside_prod_db.algorand.prices_swap

where date between '2022-05-11' and '2022-05-12'

and (asset_id IN (select swap_from_asset_id from may_swaps) OR asset_id IN (select swap_to_asset_id from may_swaps))

group by 1,2,3

), May_swaps_usd as (

select m.date, tx_group_id, swapper,

swap_from_asset_id, p1.asset_name as swap_from_name,

swap_to_asset_id, p2.asset_name as swap_to_name,

swap_from_amount, swap_from_amount * p1.price as from_price_usd,

swap_to_amount, swap_to_amount * p2.price as to_price_usd

from MAY_swaps m

left join prices p1 on (m.date = p1.date and m.swap_from_asset_id = p1.asset_id)

left join prices p2 on (m.date = p2.date and m.swap_to_asset_id = p2.asset_id)

where Abs(from_price_usd-to_price_usd) < 100 -- to remove wrong and wired swaps

, swap_from as (

select

'FROM' as label,

swap_from_name as name,

count(distinct tx_group_id) as tx_count,

count(distinct swapper) as swappers_count,

sum((from_price_usd+to_price_usd)/2) as amount_usd

from May_swaps_usd

group by 1,2

order by 1

), swap_to as (

select

'TO' as label,

swap_to_name as name,