WITH base AS (SELECT swap.SWAP_PROGRAM, swap.APP_ID, swap.BLOCK_TIMESTAMP, swap.TX_GROUP_ID, swap.SWAPPER, swap.POOL_ADDRESS, pool.ADDRESS_NAME, pool.LABEL_TYPE, SWAP_TO_ASSET_ID, asset_to.ASSET_NAME TO_NAME, SWAP_TO_AMOUNT, (SWAP_TO_AMOUNT*price.PRICE_USD) SWAP_TO_USD, asset_to.TOTAL_SUPPLY TO_SUPPLY, SWAP_FROM_ASSET_ID, asset_from.ASSET_NAME FROM_NAME, SWAP_FROM_AMOUNT, asset_from.TOTAL_SUPPLY FROM_SUPPLY FROM flipside_prod_db.algorand.swaps swap LEFT JOIN flipside_prod_db.algorand.asset asset_from ON asset_from.ASSET_ID = swap.SWAP_FROM_ASSET_ID LEFT JOIN flipside_prod_db.algorand.asset asset_to ON asset_to.ASSET_ID = swap.SWAP_TO_ASSET_ID LEFT JOIN flipside_prod_db.algorand.labels pool ON swap.POOL_ADDRESS = pool.ADDRESS LEFT JOIN flipside_prod_db.algorand.prices_swap price ON swap.SWAP_TO_ASSET_ID = price.ASSET_ID AND DATE_TRUNC('hours', swap.BLOCK_TIMESTAMP) = price.BLOCK_HOUR WHERE DATE_TRUNC('months', swap.BLOCK_TIMESTAMP) IN('2022-05-01', '2022-06-01') -- LIMIT 500 ) SELECT "BINS", COUNT(*) FROM (SELECT CASE WHEN SUM_SWAP_USD < 1 THEN '< $1' WHEN SUM_SWAP_USD < 10 THEN '< $10' WHEN SUM_SWAP_USD < 100 THEN '< $100' WHEN SUM_SWAP_USD < 1000 THEN '< $1000' WHEN SUM_SWAP_USD < 10000 THEN '< $10000' WHEN SUM_SWAP_USD >= 10000 THEN '>= $10000' END AS "BINS" FROM (SELECT SWAPPER, SUM(SWAP_TO_USD) SUM_SWAP_USD FROM base WHERE SWAP_TO_USD > 0 GROUP BY 1)) GROUP BY 1 -- SELECT swap.*, (SWAP_FROM_AMOUNT*price.PRICE_USD) SWAP_FROM_USD -- FROM flipside_prod_db.algorand.swaps swap -- LEFT JOIN flipside_prod_db.algorand.prices_swap price -- ON swap.SWAP_FROM_ASSET_ID = price.ASSET_ID AND DATE_TRUNC('hours', swap.BLOCK_TIMESTAMP) = price.BLOCK_HOUR -- WHERE SWAPPER = 'YEGJVRCZSV23ULU6ONP5ALGF4IXB5GVZGFMJ4LDJ2H3AAIGF6HHST4H5HU' -- AND DATE_TRUNC('months', BLOCK_TIMESTAMP) IN ('2022-05-01', '2022-06-01')

Algorand Swap Profiles - May & June