WITH price AS(

SELECT

hour,

price

FROM base.price.ez_prices_hourly

WHERE token_address = '0xcbb7c0000ab88b473b1f5afd9ef808440eed33bf'

),

sources AS (

SELECT

block_timestamp,

from_address AS source,

amount

FROM base.core.ez_token_transfers

WHERE contract_address = '0xcbb7c0000ab88b473b1f5afd9ef808440eed33bf'

AND to_address NOT IN(SELECT address from base.core.dim_contracts)

AND amount IS NOT NULL

SELECT

CASE WHEN a.source = '0x0000000000000000000000000000000000000000' OR b.address = '0x0000000000000000000000000000000000000000' THEN 'Minted'

WHEN a.source IN('0x19ceead7105607cd444f5ad10dd51356436095a1','0x4e962bb3889bf030368f56810a9c96b83cb3e778','0x70acdf2ad0bf2402c957154f944c19ef4e1cbae1',

'0xf877acafa28c19b96727966690b2f44d35ad5976','0x42ecc7c3f10740805f9aa493b90552180bb7a4b6') THEN 'dex'

WHEN b.address_name IS NULL AND a.source NOT IN(SELECT address FROM base.core.dim_contracts) THEN 'wallet / Multisig'

WHEN b.label_type ilike '%chadmin%' THEN 'wallet / Multisig'

WHEN b.address_name IS NOT NULL THEN b.label_type

ELSE 'other contract'

END AS source_type,

SUM(amount) AS "volume cbBTC",

SUM(amount * price) AS "volume usd"

FROM sources AS a

LEFT JOIN crosschain.core.dim_labels AS b ON a.source = b.address

INNER JOIN price AS p ON DATE_TRUNC('hour', block_timestamp) = p.hour

GROUP BY 1