dammyzuks[21] MDAO 101: CTEs & Joins copy
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
›
⌄
-- forked from metricsdao / [21] MDAO 101: CTEs & Joins @ https://flipsidecrypto.xyz/metricsdao/q/HEUqTlbiP6nF/21]-mdao-101-ctes-joins
-- MetricsDAO Blockchain Analytics 101
-- https://docs.metricsdao.xyz/analyst-resources/blockchain-data-101
WITH prices AS (
SELECT
date_trunc('day', hour) AS _date,
AVG(price) AS avg_price_usd
FROM ethereum.core.fact_hourly_token_prices
WHERE _date BETWEEN '2022-01-01' AND '2022-06-30'
AND symbol = 'WETH'
GROUP BY 1
),
openfee AS (
SELECT
DATE_TRUNC('day', block_timestamp) AS _date,
SUM(platform_fee) AS total_platform_fee,
SUM(platform_fee_usd) AS total_platform_fees_usd
FROM ethereum.core.ez_nft_sales
WHERE block_timestamp::DATE BETWEEN '2022-01-01' AND '2022-06-30'
AND platform_name = 'opensea'
GROUP BY 1
)
SELECT
o._date,
o.total_platform_fees_usd,
p.avg_price_usd
FROM openfee o
LEFT JOIN prices p USING (_date)
ORDER BY 1, 3 DESC
Run a query to Download Data