linda20-4DtLkePercentage of Gobblers listed
Updated 2022-11-03
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 ETH AS (
SELECT HOUR as date ,
avg(price) as ETH_price
FROM ethereum.core.fact_hourly_token_prices
WHERE token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH contract
GROUP BY 1
),
mints AS (
SELECT
trunc(block_timestamp,'hour') AS date,TOKENID,
COUNT(DISTINCT nft_to_address) AS n_minters,
sum(n_minters) over (ORDER BY date) AS cumu_minters,
sum(nft_count) AS nft_minted,
sum(nft_minted) over (ORDER BY date) AS cum_nft_minted
FROM ethereum.core.ez_nft_mints
WHERE block_timestamp::date >= CURRENT_DATE-90
AND nft_address= '0x60bb1e2aa1c9acafb4d34f71585d7e959f387769'
AND nft_from_address='0x0000000000000000000000000000000000000000'
AND event_type = 'nft_mint'
GROUP BY 1,2
ORDER BY 1
),
seles AS (
SELECT date_trunc('hour',block_timestamp) AS date1,n_minters,cumu_minters,nft_minted,cum_nft_minted,a.TOKENID,
COUNT(DISTINCT tx_hash)AS sale_count ,
COUNT(DISTINCT buyer_address) AS unique_buyers ,
sum( price * ETH_price) AS volume ,
sum(sale_count) over (ORDER BY date1) AS cumu_count,
sum(unique_buyers) over (ORDER BY date1) AS cumu_buyer,
sum(volume) over (ORDER BY date1) AS cumu_volume,
sale_count/datediff(HOUR,'2022-10-31 20:22:35.000' , current_date-1) AS average_sale_HOUR,
unique_buyers/datediff(HOUR,'2022-10-31 20:22:35.000' , current_date-1) AS average_unique_buyers_HOUR,
Run a query to Download Data