stargate_bridge_addresses as (

select address

from ethereum.core.dim_labels

where label_subtype = 'bridge' and label = 'stargate finance'

),

eth_prices as (

select

date(block_timestamp) as date,

replace(feed_name, ' / USD') as symbol,

median(coalesce(latest_answer_adj, latest_answer_unadj / pow(10,8))) as price --using median cause there will be some nulls / zeroes

from ethereum.chainlink.ez_oracle_feeds

where feed_category = 'Cryptocurrency (USD pairs)'

and feed_name in ('ETH / USD')

group by 1,2

order by 1

select

to_date(a.block_timestamp) as date,

case a.origin_to_address

when '0x011e52e4e40cf9498c79273329e8827b21e2e581' then 'sushi'

when '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' then 'lifi'

when '0xed8877f8536781d2fc40c1e0054cbeb8fd960ee4' then 'chainhop'

when '0x7bd5ade0975ec1d46d6472ba9dcc2321c4c41311' then 'zunami'

else coalesce(l.label, 'Unknown')

end as project,

'ethereum' AS source_chain,

t.symbol,

sum(t.amount) as token_amount,

sum(case when t.symbol = 'SGETH' then t.amount * p.price else t.amount_usd end) as token_amount_usd,

count(distinct a.tx_hash) as tx_count,

count(distinct a.origin_from_address) as user_count

from ethereum.core.fact_event_logs a

join ethereum.core.ez_token_transfers t