elsina2024-09-14: Average Activity Per Collections Per User in Last Month
Updated 2024-11-01
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 sales_rank AS (
SELECT
project_name AS collection,
COUNT(DISTINCT tx_hash) AS sales_count
FROM
base.nft.ez_nft_sales
WHERE
date_trunc('day', block_timestamp) between '2024-10-01' and '2024-10-31'
GROUP BY
collection
ORDER BY
sales_count DESC
LIMIT 30
),
users as (
SELECT
project_name as collections,
buyer_address,
count(DISTINCT tx_hash) as tx_count,
count(DISTINCT project_name) as collection_count,
sum(price_usd) as total_vol,
sum(TOTAL_FEES_USD) as total_total_fee,
sum(PLATFORM_FEE_USD) as total_platform_fee,
sum(CREATOR_FEE_USD) as total_creator_fee,
sum(TX_FEE_USD) as total_tx_fee,
avg(price_usd) as avg_nft_price,
FROM
base.nft.ez_nft_sales
WHERE
block_timestamp::date between '2024-10-01' and '2024-10-31' and collections IN (SELECT collection FROM sales_rank)
GROUP BY
collections, buyer_address
)
QueryRunArchived: QueryRun has been archived