prices as (

select date(hour) as day, avg(price) as price from ethereum.core.fact_hourly_token_prices

where symbol like '%ETH%' and hour >= dateadd(month, -12, getdate())

group by day

),

metamask_swaps as (

select date(a.block_timestamp) as day, from_address, 'Metamask' as platform,

(a.eth_value * b.price) as amount_usd, a.tx_hash, a.tx_fee as fees from ethereum.core.fact_transactions a join prices b on date(a.block_timestamp) = b.day

and a.to_address = lower('0x881D40237659C251811CEC9c364ef91dC08D300C') and

a.block_timestamp >= dateadd(month, -12, getdate())

),

metamask as (

select day, platform, count(distinct from_address) as users, round(sum(amount_usd),2) as tot_volume, round(avg(amount_usd),2) as avg_volume, round(avg(fees),2) as fees,

count(distinct tx_hash) as transactions from metamask_swaps where amount_usd > 0 and amount_usd is not null

group by day, platform order by day desc

),

other_platform_swaps as (

select date(a.block_timestamp) as day, a.sender, platform,

case when amount_in_usd > amount_out_usd then (amount_in_usd -amount_out_usd)

when amount_out_usd > amount_in_usd then (amount_out_usd -amount_in_usd)

end as amount_usd, a.tx_hash, (b.gas_price * b.gas_used) as fees from

ethereum.core.ez_dex_swaps a join ethereum.core.fact_transactions b

on a.tx_hash = b.tx_hash and b.to_address <> lower('0x881D40237659C251811CEC9c364ef91dC08D300C') and

b.to_address <> lower('0xe66B31678d6C16E9ebf358268a790B763C133750') and

a.block_timestamp >= dateadd(month, -12, getdate())

UNION

select date(a.block_timestamp) as day, a.from_address as sender, 'Coinbase' as platform,

(a.eth_value * b.price) as amount_usd, a.tx_hash, a.tx_fee as fees from ethereum.core.fact_transactions a join prices b on date(a.block_timestamp) = b.day

and a.to_address = lower('0xe66B31678d6C16E9ebf358268a790B763C133750') and

a.block_timestamp >= dateadd(month, -12, getdate())

),

other_platforms as (

select day, platform, count(distinct sender) as users, round(sum(amount_usd),2) as tot_volume, round(avg(amount_usd),2) as avg_volume, round(avg(fees),2) as fees,