(

select BLOCK_TIMESTAMP, CURRENCY, AMOUNT ,PROJECT_NAME as TOKEN ,tx_id

from osmosis.core.fact_transfers inner join osmosis.core.dim_labels

on CURRENCY=ADDRESS

),

VOLUMES AS (select

DATE(BLOCK_TIMESTAMP)AS DATE,

count(distinct TX_ID) as transactions ,TOKEN,SUM(AMOUNT)AS VOLUME,

CASE WHEN TOKEN in('USD Coin','axlUSDC','USDC.grv') THEN 'USD Coin'

WHEN TOKEN in('axlDAI','DAI.grv') THEN 'DAI'

WHEN TOKEN in('USDT') THEN 'USDT'

WHEN TOKEN in('USTC') THEN 'USTC'

END AS TOKENS

from TRANSFER

group by 1,3,5 HAVING TOKENS IS NOT NULL)

SELECT DATE,transactions,TOKENS,

CASE WHEN TOKENS ='USD Coin' THEN VOLUME

WHEN TOKENS ='DAI' THEN (VOLUME/POW(10,18))

WHEN TOKENS ='USDT' THEN VOLUME

WHEN TOKENS = 'USTC' THEN VOLUME

END AS CURRENCY

FROM VOLUMES