elsinaDistribution of total NFTs count bought by users
Updated 2022-12-16Copy Reference Fork
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 sol_prise as (
select
date_trunc('day', hour) as day,
avg(price) as sol_usd
from ethereum.core.fact_hourly_token_prices
where
token_address = '0xd31a59c85ae9d8edefec411d448f90841571b89c' and
day >= '{{start_date}}' and day <= '{{end_date}}'
group by 1
),
users_nft as (
select
purchaser as buyer,
'Solana' as blockchain,
sum(sales_amount * sol_usd) as sales_volume,
count(distinct tx_id) as tx_count
from solana.core.fact_nft_sales s join sol_prise p on block_timestamp::date = day
where
succeeded = true and
block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}'
group by 1
)
select
case
when tx_count = 1 then 'a. 1 NFT'
when tx_count = 2 then 'b. 2 NFTs'
when tx_count <= 4 then 'c. 3 or 4 NFTs'
when tx_count <= 8 then 'd. between 5 and 8 NFTs'
when tx_count <= 16 then 'e. between 9 and 16 NFTs'
when tx_count <= 32 then 'f. between 17 and 32 NFTs'
else 'g. more than 33 NFTs'
end as dis,
-- tx_count,
count(*) as count
from users_nft
group by 1
Run a query to Download Data