date_trunc('hour', BLOCK_TIMESTAMP) as date,

b.LABEL_TYPE,

PROJECT_NAME,

count(DISTINCT TX_HASH) as Tx_Cnt,

count(DISTINCT ORIGIN_FROM_ADDRESS) as User_Cnt,

sum(EVENT_INPUTS:value/1e18) as volume,

CASE

WHEN date < '2022-11-07 13:00:00.000' THEN 'Before Announcement Time'

WHEN date >= '2022-11-07 13:00:00.000' THEN 'After Announcement Time'

end as period

FROM ethereum.core.fact_event_logs a LEFT JOIN ethereum.core.dim_labels b on a.EVENT_INPUTS:to = b.ADDRESS

LEFT JOIN crosschain.core.address_labels c on a.EVENT_INPUTS:to = c.ADDRESS

WHERE CONTRACT_ADDRESS = '0xb3999f658c0391d94a37f7ff328f3fec942bcadc'

and ORIGIN_TO_ADDRESS != '0x1a9a4d919943340b7e855e310489e16155f4ed29'

and ORIGIN_FUNCTION_SIGNATURE != '0xeb4d6138'

and EVENT_NAME in ('Transfer')

and BLOCK_TIMESTAMP::date >= '2022-11-07'

GROUP by 1,2,3,7

ORDER by 1