h4wkMagic Eden
Updated 2022-03-16
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
›
⌄
-- Create a visualization of Magic Eden total sales volume per day since January 1st.
-- How has sales volume trended over time?
-- Are there any trends to when sales volume seems to go up or down on certain days of the week or during certain times of the day?
-- Are users more likely to trade a NFT during the week or the weekend?
-- WITH secondary_sales AS ( SELECT block_timestamp, block_id, blockchain, recent_block_hash, tx_id, mint,
-- succeeded, pretokenbalances, posttokenbalances, instruction, inner_instruction,
WITH secondary_sales AS ( SELECT DATE_TRUNC('day', block_timestamp) as date,
-- tx_id,
sum(COALESCE(inner_instruction:instructions[0]:parsed:info:lamports/POW(10,9),0)) as price_0,
sum(COALESCE(inner_instruction:instructions[1]:parsed:info:lamports/POW(10,9),0)) as price_1,
sum(COALESCE(inner_instruction:instructions[2]:parsed:info:lamports/POW(10,9),0)) as price_2,
sum(COALESCE(inner_instruction:instructions[3]:parsed:info:lamports/POW(10,9),0)) as price_3,
sum(COALESCE (inner_instruction:instructions[4]:parsed:info:lamports/POW(10,9), 0)) as price_4,
(price_0 + price_1 + price_2 + price_3 + price_4) AS volume
FROM solana.nfts
WHERE date(block_timestamp) >= '2022-01-01'
AND instruction:programId in ('MEisE1HzehtrDpAAT8PnLHjpSSkRYakotTuJRPjTpo8', 'M2mx93ekt1fmXSVkTrUL9xVFHkmME8HTUi5Cyc5aF7K')
AND succeeded = 'TRUE'
AND array_size(inner_instruction:instructions) > 2
group by date)
select * from secondary_sales
Run a query to Download Data