rw2023Warp day by day
Updated 2023-07-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
›
⌄
WITH abc_mints AS (
SELECT instruction:accounts[5]::string AS mint
FROM solana.core.fact_events
WHERE succeeded
AND block_timestamp::date = '2022-08-21'::date
AND program_id = 'cndy3Z4yapfJBmL3ShUp5exZKqR3z33thTzeNMm2gRZ' -- Candy Machine Program ID
AND instruction:accounts[0] = 'Gp1MTXdKKRDLC8fDVqU3SKFQ1cGqMq47VCA2bDpnMCkb' -- Candy Machine
AND instruction:accounts[1] = 'GVkb5GuwGKydA4xXLT9PNpx63h7bhFNrDLQSxi6j5NuF' -- CM Creator
AND instruction:accounts[6] = 'CMAWeiRp4sYJVDJA7XAy4qyvX2ovLX848x4Qj5H3nK7D' -- Mint Authority
),
warped AS (
SELECT
signers[0] AS user_address,
DATE(block_timestamp) AS burn_date
FROM solana.core.fact_events
WHERE PARSE_JSON(instruction):parsed:info:mint IN (SELECT mint FROM abc_mints)
AND BLOCK_TIMESTAMP > '2023-04-26'
AND event_type = 'burn'
),
burned_per_day AS (
SELECT burn_date, COUNT(*) AS nfts_burned
FROM warped
GROUP BY burn_date
)
SELECT burn_date,
nfts_burned,
SUM(nfts_burned) OVER(ORDER BY burn_date) AS cumulative_nfts_burned
FROM burned_per_day
ORDER BY burn_date;
Run a query to Download Data