WITH pricesss AS (SELECT

hOUR::DATE AS TIME,TOKEN_ADDRESS,AVG(PRICE) AS PRICE

FROM crosschain.core.ez_hourly_prices

WHERE TOKEN_ADDRESS in (

'0x95ad61b0a150d79219dcf64e1e6cc01f0b64c4ce' --SHIBA

GROUP BY 1,2)

SELECT DATE_tRUNC ('WEEK',BLOCK_TIMESTAMP) AS DATE,LABEL_TYPE, SYMBOL, count (DISTINCT TX_HASH) as TXN, count (DISTINCT TO_ADDRESS) as Senders, sum (AMOUNT * PRICE) AS USD

from ethereum.core.ez_token_transfers

LEFT JOIN ethereum.core.dim_labels on ADDRESS = FROM_ADDRESS

LEFT JOIN pricesss on TOKEN_ADDRESS = CONTRACT_ADDRESS AND TIME::DATE = BLOCK_TIMESTAMP::DATE

WHERE CONTRACT_ADDRESS in (

'0x95ad61b0a150d79219dcf64e1e6cc01f0b64c4ce' --SHIBA

AND LABEL_TYPE in ('layer2','dex','defi','dapp','cex','CEX') GROUP BY 1,2,3