SELECT date(hour) as date, avg(price) as p

from ethereum.core.fact_hourly_token_prices where SYMBOL = 'WETH' and date >= CURRENT_DATE - 60 GROUP by date

),

metamask as (

SELECT date(block_timestamp) as date, 'Metamask' as platform_name, sum(AMOUNT_USD) as vol, avg(AMOUNT_USD) as avg_vol, avg(tx_fee * eth_price.p) as avg_usd_fee, COUNT(DISTINCT ORIGIN_FROM_ADDRESS) as wallets,

COUNT(DISTINCT tx_hash) as tx_count

FROM (

SELECT a.*, b.tx_fee,

row_number() over (partition by a.tx_hash order by a._LOG_ID ASC) as r

from ethereum.core.ez_token_transfers a

join ethereum.core.fact_transactions b on a.tx_hash = b.tx_hash

where date(a.block_timestamp) >= CURRENT_DATE - 60

and a.ORIGIN_TO_ADDRESS = '0x881d40237659c251811cec9c364ef91dc08d300c'

and a.AMOUNT_USD > 0

and a.HAS_PRICE = TRUE

and a.AMOUNT_USD < 10000000

-- and tx_hash= '0x52ea03e075b94038ff861156ee0b1621ec23e9132c2441db90325edcb094eeba' -- sample

qualify r = 1

join eth_price on eth_price.date = date(block_timestamp)

GROUP by date(block_timestamp)

),

other_dexes as (

SELECT date(a.block_timestamp) as date,

CASE WHEN a.ORIGIN_TO_ADDRESS ilike '0xdef171fe48cf0115b1d80b88dc8eab59176fee57' or a.ORIGIN_TO_ADDRESS ilike '0x1bD435F3C054b6e901B7b108a0ab7617C808677b' THEN 'Paraswap' ELSE a.platform END as platform_name,

sum(a.AMOUNT_IN_USD) as vol, avg(a.AMOUNT_IN_USD) as avg_vol, avg(b.tx_fee * eth_price.p) as usd_fee, COUNT(DISTINCT a.ORIGIN_FROM_ADDRESS) as wallets,

COUNT(DISTINCT a.tx_hash) as tx_count

FROM ethereum.core.ez_dex_swaps a

join ethereum.core.fact_transactions b on a.tx_hash = b.tx_hash

join eth_price on eth_price.date = date(a.block_timestamp)

WHERE date(a.block_timestamp) >= CURRENT_DATE - 60

AND a.AMOUNT_IN_USD > 0

and a.AMOUNT_IN_USD < 10000000