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_sales AS (

SELECT

DATE_TRUNC('day', block_timestamp) as "Day",

-- currency_symbol AS "Currency symbol",

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"

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 "Day"

)

SELECT

opensea_sales."Day",

prices."Average ETH price, USD",

opensea_sales."Total platform fees, USD",

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

FROM opensea_sales

LEFT JOIN prices

ON opensea_sales."Day" = prices."Date" -- another option is JOIN USING() when the column name is the same

ORDER BY "Day"