SELECT

DATE_TRUNC('hour', block_timestamp) AS event_hour,

tx_hash,

decoded_log:user AS wallet_address,

decoded_log:amount AS raw_token_quantity,

'0x31c994ac062c1970c086260bc61babb708643fac' AS token_address,

event_name AS activity_type

FROM avalanche.core.ez_decoded_event_logs

WHERE

contract_address = '0xb866b661db0b1b0f39ed28fc59c77d60983dccbb'

AND event_name IN ('Deposit', 'Withdraw')

),

price_data AS (

SELECT

hour AS recorded_hour,

token_address,

symbol AS token_symbol,

name AS token_name,

decimals,

price

FROM avalanche.price.ez_prices_hourly

WHERE token_address = '0x31c994ac062c1970c086260bc61babb708643fac'

),

final_aggregations AS (

SELECT

t.event_hour,

t.wallet_address,

p.token_symbol,

t.tx_hash,

t.activity_type,

t.raw_token_quantity / POW(10, COALESCE(p.decimals, 18)) AS token_quantity,

p.price * (t.raw_token_quantity / POW(10, COALESCE(p.decimals, 18))) AS usd_value,

p.price

FROM transaction_data t

LEFT JOIN price_data p