SELECT

DISTINCT tx_hash

FROM

avalanche.core.fact_event_logs

WHERE

event_name = 'Swap'

),

event_data AS (

SELECT event_name,

CASE

WHEN tx_hash IN (SELECT tx_hash FROM swaps) THEN 'Swap'

ELSE 'Transfer'

END AS label,

DATE_TRUNC('HOUR', block_timestamp) AS hour,

tx_hash,

origin_from_address,

origin_to_address,

event_inputs:from AS token_from,

event_inputs:to AS token_to,

event_inputs:value*1e-6 AS amount

FROM

avalanche.core.fact_event_logs

WHERE

tx_status = 'SUCCESS'

AND contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'

AND block_timestamp >= '2022-07-01'

AND event_name = 'Transfer'

),

hourly_data AS (

SELECT

label,

hour,

COUNT(*) AS tx,

SUM(amount) AS volume,

COUNT(DISTINCT origin_from_address) AS users