select

trunc(hour, 'hour') as hour,

avg(price) as price

from

crosschain.price.ez_prices_hourly

where

symbol = 'ETH'

group by

1

),

sushiswap AS (

SELECT

trunc(d.block_timestamp, 'hour') as hour,

d.tx_hash,

d.amount_in_usd,

d.amount_out_usd,

(d.amount_in_usd + d.amount_out_usd) as tx_vol

from

ethereum.defi.ez_dex_swaps d

join ethereum.core.fact_transactions t on t.tx_hash = d.tx_hash

where

platform like 'sushi%'

and tx_vol is not null

and tx_vol is not null

AND amount_out_usd IS NOT NULL

AND amount_in_usd IS NOT NULL

ORDER BY

1 DESC

),

revenue AS (

SELECT

tx.hour,

tx.tx_vol AS "Volume USD",

"Volume USD" * 0.003 AS "Sushi Total Charge for Swaps",

"Volume USD" * 0.0025 AS "Sushi LP Rewards",