with asset_out as (with qualified as (with table1 as (with tab1 as (select pool_name, pool_address, PLATFORM from ethereum.core.dim_dex_liquidity_pools), tab2 as (select balance_date as day, user_address, amount_usd, symbol from flipside_prod_db.ethereum.erc20_balances) select symbol, platform, pool_name, pool_address, amount_usd, CASE when amount_usd>=100000 then 'Qualified Pools' when amount_usd<100000 then 'Non-Qualified Pools' end as pool_type from tab1 left join tab2 on tab1.pool_address=tab2.user_address where amount_usd is not null and day=current_date-1 and amount_usd<>0) select pool_address, pool_name, platform, pool_type, symbol, amount_usd from table1 where pool_type='Qualified Pools' and (symbol<>'SAITAMA' and symbol<>'$WEAPON' and symbol<>'ShibDoge') order by 6 desc), volume as (select block_timestamp::date as date, from_address, amount_usd from ethereum.core.ez_token_transfers where BLOCK_NUMBER>=14982790) select volume.date as day ------------------------------------------------------********************* -- , qualified.pool_name , qualified.platform as platform_ -- , qualified.symbol , sum(volume.amount_usd) as volume_asset_out from qualified left join volume on qualified.pool_address=volume.from_address group by 1,2 order by 1), ------------------------------------------------------------------------------------------------------------ asset_in as (with qualified0 as (with table1 as (with tab1 as (select pool_name, pool_address, PLATFORM from ethereum.core.dim_dex_liquidity_pools), tab2 as (select balance_date as day, user_address, amount_usd, symbol from flipside_prod_db.ethereum.erc20_balances) select symbol, platform, pool_name, pool_address, amount_usd, CASE when amount_usd>=100000 then 'Qualified Pools' when amount_usd<100000 then 'Non-Qualified Pools' end as pool_type from tab1 left join tab2 on tab1.pool_address=tab2.user_address where amount_usd is not null and day=current_date-1 and amount_usd<>0) select pool_address, pool_name, platform, pool_type, symbol, amount_usd from table1 where pool_type='Qualified Pools' and (symbol<>'SAITAMA' and symbol<>'$WEAPON' and symbol<>'ShibDoge') order by 6 desc), volume0 as (select block_timestamp::date as date, to_address, amount_usd from ethereum.core.ez_token_transfers where BLOCK_NUMBER>=14982790) select volume0.date as day-----------------------------------********************************** -- , qualified0.pool_name , qualified0.platform as platform__ -- , qualified0.symbol , sum(volume0.amount_usd) as volume_in from qualified0 left join volume0 on qualified0.pool_address=volume0.to_address group by 1,2 order by 1) -------------------------------------------------------------- select asset_out.day, (volume_asset_out+volume_in) as total_volume, platform_ from asset_out full outer join asset_in on asset_out.day=asset_in.day and asset_out.platform_=asset_in.platform__ where platform_ is not null order by 1