SELECT

DATE_TRUNC('hour', DATEADD(hour, SEQ4(), '2024-12-28 02:00:00'::timestamp)) AS hour

FROM

TABLE(GENERATOR(ROWCOUNT => 100000))

WHERE

DATE_TRUNC('hour', DATEADD(hour, SEQ4(), '2024-12-28 02:00:00'::timestamp)) <= CURRENT_TIMESTAMP

),

prices AS(

SELECT hour AS price_hour, symbol, price

FROM base.price.ez_prices_hourly

WHERE token_address = lower('0x0521AaA7C96E25afeE79FDd4f1Bb48F008aE4eac')

AND hour::date >= '2024-12-28'

ORDER BY 1

),

users AS (

-- Extract unique users from transfers to ensure every user has an entry for each hour

SELECT DISTINCT to_address AS user

FROM base.core.ez_token_transfers

WHERE contract_address = lower('0x0521AaA7C96E25afeE79FDd4f1Bb48F008aE4eac')

-- AND from_address NOT IN (SELECT address FROM ethereum.core.dim_contracts)

-- AND to_address NOT IN (SELECT address FROM ethereum.core.dim_contracts)

AND user <> '0x0000000000000000000000000000000000000000'

-- AND user IN ('0xf2614a233c7c3e7f08b1f887ba133a13f1eb2c55')

-- AND user IN ('0x662e3363966b8111bff3b2b539adb4533809e026')

-- AND user IN ('0x7699c613be2ec32984e153dbc27c929fd159caab','0x66ec04035f2f14bbbfe744a8a56ca02fd21582ff')

-- AND user in ('0xed6ac3a92e4a77b0553f2a243afc51b3b865d3e1')

),

hourly_users AS (

-- Create all combinations of users and hours

SELECT hour, user

FROM hourly_series

CROSS JOIN users

),