select hour as price_hour,

token_address,

avg(price) as price

from ethereum.core.fact_hourly_token_prices

group by 1,2

, first_uni_tx as (

select origin_from_address as user_id,

min(block_timestamp) as first_transaction

from ethereum.core.ez_dex_swaps tx

where platform ilike '%uniswap%'

group by 1

having first_transaction >= '2020-08-01' and first_transaction < '2020-08-15'

, uni_transacted as (

select origin_from_address as user_id,

zeroifnull(sum(case when symbol_in = 'WETH' then zeroifnull(amount_in)

when symbol_out = 'WETH' then amount_out end)) as total_eth_transacted,

zeroifnull(sum(case when symbol_in = 'WETH' or symbol_out = 'WETH' then zeroifnull(amount_in)*price end))

as total_eth_usd_transacted,

zeroifnull(sum(case when symbol_in != 'WETH' and symbol_out != 'WETH' then zeroifnull(amount_in)*price end))

as total_token_usd_transacted,

count(DISTINCT token_in) as num_unique_tokens_sell,

count(DISTINCT token_out) as num_unique_tokens_buy

from ethereum.core.ez_dex_swaps tx

join price on (token_address = token_in

and date_trunc('hour', block_timestamp) = price_hour)

where platform ilike '%uniswap%'

and amount_in*price < 10000000 -- Remove overflowing outlier

and price > 0

group by 1

, base as (

select from_address as user_id,