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

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

),

metamask as (

SELECT 'Metamask' as platform_name, avg(usd_fee) as avg_usd_fee, COUNT(DISTINCT tx_hash) as tx_count FROM (

SELECT a.*, b.tx_fee * eth_price.p as usd_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

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

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

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

),

other_dexes as (

SELECT

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

avg(b.tx_fee * eth_price.p) as usd_fee,

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 - 10

AND a.AMOUNT_IN_USD > 0

and a.AMOUNT_IN_USD < 10000000

GROUP BY platform_name

),