eth_prices as (

select

hour::date as date,

lower(symbol) as symbol, -- to make sure it matches the decoded log

decimals,

token_address,

avg(price) as price

from

ethereum.core.fact_hourly_token_prices

group by 1,2,3,4

),

base_eth as (

select

to_date(a.block_timestamp) as date,

a.origin_from_address,

t.contract_address,

sum(t.raw_amount) as raw_token_amount,

count(distinct a.tx_hash) as tx_count

from ethereum.core.fact_event_logs a

join ethereum.core.fact_token_transfers t

on t.tx_hash = a.tx_hash

where a.topics[0] IN ('0x6939f93e3f21cf1362eb17155b740277de5687dae9a83a85909fd71da95944e7')

and a.block_timestamp > '2022-03-10' -- 2022-03-16 is the first date for stargate

and t.from_address = t.origin_from_address -- only get the tokens transferred from the original sender to avoid multiple logging

group by 1,2,3),

eth as (

select

b.date,

b.origin_from_address,

p.symbol,

raw_token_amount / pow(10, decimals) as token_amount,

token_amount * price as token_amount_usd,