select date_trunc(day, BLOCK_HOUR) as BLOCK_DAY, ASSET_ID, ASSET_NAME, avg(PRICE_USD) as PRICE_USD

from

(select BLOCK_HOUR, ASSET_ID, ASSET_NAME, PRICE_USD from algorand.defi.ez_price_pool_balances

union all

select BLOCK_HOUR, ASSET_ID, ASSET_NAME, PRICE_USD from algorand.defi.ez_price_swap

union all

select BLOCK_HOUR, 0, 'ALGO', _ALGO_PRICE as PRICE_USD from algorand.defi.ez_price_pool_balances)

group by 1,2,3

),

ez_swaps as

(select BLOCK_TIMESTAMP, SWAP_PROGRAM, TX_GROUP_ID, SWAPPER, b.ASSET_NAME as ASSET_FROM, b.PRICE_USD as ASSET_FROM_PRICE, b.ASSET_ID as ASSET_ID_FROM, SWAP_FROM_AMOUNT,

ASSET_FROM_PRICE*SWAP_FROM_AMOUNT as SWAP_FROM_AMOUNT_USD,

C.ASSET_NAME as ASSET_TO, c.PRICE_USD as ASSET_TO_PRICE, c.ASSET_ID as ASSET_ID_TO, SWAP_TO_AMOUNT, ASSET_TO_PRICE*SWAP_TO_AMOUNT as SWAP_TO_AMOUNT_USD,

least(ifnull(SWAP_FROM_AMOUNT_USD,99999999999999),ifnull(SWAP_TO_AMOUNT_USD,99999999999999)) as SWAP_VOLUME_USD

from algorand.defi.fact_swap a

left join prices b

on b.ASSET_ID=a.SWAP_FROM_ASSET_ID and date_trunc(day, BLOCK_TIMESTAMP)=date_trunc(day, b.BLOCK_DAY)

left join prices c

on c.ASSET_ID=a.SWAP_TO_ASSET_ID and date_trunc(day, BLOCK_TIMESTAMP)=date_trunc(day, c.BLOCK_DAY)

where SWAP_VOLUME_USD<200000000),

swappers as (

select SWAP_PROGRAM, SWAPPER, CREATED_AT, ifnull(AMOUNT,0) as ALGO_Balance,

case

when ALGO_Balance<1 then '[0,1) $ALGO'

when ALGO_Balance>=1 and ALGO_Balance<10 then '[1 - 10) $ALGO'

when ALGO_Balance>=10 and ALGO_Balance<100 then '[10 - 100) $ALGO'

when ALGO_Balance>=100 and ALGO_Balance<1000 then '[100 - 1,000) $ALGO'

when ALGO_Balance>=1000 and ALGO_Balance<10000 then '[1,000 - 10,000) $ALGO'

else '[10,000 - ...) $ALGO' end as balance_tag

from ez_swaps

left join algorand.core.dim_account b

on b.ADDRESS=SWAPPER

left join algorand.core.fact_account_asset c