fb3ce38c-7bfe-475c-bca8-3f1e44ae80a6full_stats_eth_mp-s
Updated 2023-08-10
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
ranked_buyers AS (
SELECT
buyer_address,
DATE_TRUNC('day', BLOCK_TIMESTAMP) AS date,
ROW_NUMBER() OVER (
PARTITION BY
buyer_address
ORDER BY
BLOCK_TIMESTAMP
) AS rn
FROM
ethereum.core.ez_nft_sales
WHERE
event_type = 'sale'
)
SELECT
DATE_TRUNC('day', e.BLOCK_TIMESTAMP) AS date,
SUM(e.price_usd) AS price,
COUNT(DISTINCT e.TX_HASH) AS volume,
e.platform_name,
COUNT(DISTINCT e.buyer_address) AS unique_daily_buyer,
COUNT(DISTINCT e.seller_address) AS unique_daily_seller,
COUNT(DISTINCT e.project_name) AS unique_projects_sold,
COUNT(DISTINCT rb.buyer_address) AS net_new_daily_buyer
FROM
ethereum.core.ez_nft_sales e
LEFT JOIN ranked_buyers rb ON e.buyer_address = rb.buyer_address
AND DATE_TRUNC('day', e.BLOCK_TIMESTAMP) = rb.date
AND rb.rn = 1
WHERE
e.event_type = 'sale'
GROUP BY
DATE_TRUNC('day', e.BLOCK_TIMESTAMP),
e.platform_name
ORDER BY
Run a query to Download Data