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

FROM crosschain.core.ez_hourly_prices

WHERE TOKEN_ADDRESS in (

'0x6982508145454ce325ddbe47a25d4ec3d2311933' --PEPE

GROUP BY 1,2)

SELECT BLOCK_TIMESTAMP::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 (

'0x6982508145454ce325ddbe47a25d4ec3d2311933' --PEPE

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