zpokoPolygon NFT Ecosystem USD Expended
Updated 2023-06-21
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
34
35
36
›
⌄
WITH usd_expended_query AS (
SELECT
date_trunc('day', block_timestamp) AS dt,
SUM(ft.amount_usd) AS usd_expended,
AVG(SUM(ft.amount_usd)) OVER (ORDER BY date_trunc('day', block_timestamp) ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS usd_expended_30d_avg
FROM
polygon.core.ez_token_transfers ft
JOIN polygon.core.dim_labels dl ON ft.to_address = dl.address
WHERE
ft.block_timestamp >= DATE '2022-05-01'
AND dl.label_type = 'nft'
GROUP BY
dt
),
usd_expended_matic_query AS (
SELECT
date_trunc('day', block_timestamp) AS dt,
SUM(ft.amount_usd) AS usd_expended_matic,
AVG(SUM(ft.amount_usd)) OVER (ORDER BY date_trunc('day', block_timestamp) ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS usd_expended_matic_30d_avg
FROM
polygon.core.ez_matic_transfers ft
JOIN polygon.core.dim_labels dl ON ft.matic_to_address = dl.address
WHERE
ft.block_timestamp >= DATE '2022-05-01'
AND dl.label_type = 'nft'
GROUP BY
dt
)
SELECT
usd_expended_query.dt,
usd_expended_query.usd_expended,
usd_expended_query.usd_expended_30d_avg,
usd_expended_matic_query.usd_expended_matic,
usd_expended_matic_query.usd_expended_matic_30d_avg,
usd_expended_query.usd_expended + usd_expended_matic_query.usd_expended_matic AS total_usd_expended,
AVG(usd_expended_query.usd_expended + usd_expended_matic_query.usd_expended_matic) OVER (ORDER BY usd_expended_query.dt ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS total_usd_expended_30d_avg
Run a query to Download Data