SELECT

DATE(hour) as "Date",

AVG(price) as "Average ETH price, USD"

FROM ethereum.core.fact_hourly_token_prices

WHERE "Date" BETWEEN '2022-10-01' AND '2022-10-31'

AND symbol = 'WETH'

GROUP BY "Date"

),

opensea_fees AS (

SELECT

DATE(block_timestamp) as "Date",

count(distinct tx_hash) as "Number of Transactions",

sum(platform_fee) as "Total platform fees, ETH",

sum(platform_fee_usd) as "Total platform fees, USD",

avg(platform_fee) as "Average platform fees, ETH",

avg(platform_fee_usd) as "Average platform fees, USD"

FROM ethereum.core.ez_nft_sales

WHERE

block_timestamp::DATE BETWEEN '2022-10-01' AND '2022-10-31'

AND platform_name = 'opensea'

AND currency_symbol IN ('ETH', 'WETH')

GROUP BY "Date"

opensea_fees."Date",

prices."Average ETH price, USD",

opensea_fees."Total platform fees, USD",

"Total platform fees, ETH" * "Average ETH price, USD" AS "Total platform fees calculated, USD"

FROM opensea_fees

LEFT JOIN prices

ON opensea_fees."Date" = prices."Date" -- another option in Snowflake SQL (Flipside) is JOIN USING() when the column name is the same

ORDER BY "Date"

;