piperMagic Eden Volume Trends - Number of Sales by hour
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
27
28
29
30
31
32
33
34
35
36
›
⌄
⌄
/*
Solana - Magic Eden Volume Trends
Question 36: 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?
Magic Eden Marketplacve
Old: MEisE1HzehtrDpAAT8PnLHjpSSkRYakotTuJRPjTpo8
New: M2mx93ekt1fmXSVkTrUL9xVFHkmME8HTUi5Cyc5aF7K
*/
WITH all_sales AS (
SELECT
block_timestamp::date AS date,
hour(block_timestamp) AS hourly,
dayname(date) AS day_name,
tx_id,
COALESCE (inner_instruction:instructions[0]:parsed:info:lamports/POW(10,9), 0) as price_0,
COALESCE (inner_instruction:instructions[1]:parsed:info:lamports/POW(10,9), 0) as price_1,
COALESCE (inner_instruction:instructions[2]:parsed:info:lamports/POW(10,9), 0) as price_2,
COALESCE (inner_instruction:instructions[3]:parsed:info:lamports/POW(10,9), 0) as price_3,
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 sales_price
FROM
solana.nfts
WHERE
date >= '2022-01-01' AND date <= '2022-03-16'
AND
(instruction:programId = 'MEisE1HzehtrDpAAT8PnLHjpSSkRYakotTuJRPjTpo8' OR instruction:programId = 'M2mx93ekt1fmXSVkTrUL9xVFHkmME8HTUi5Cyc5aF7K')
AND
array_size(inner_instruction:instructions) > 2
AND
succeeded = 'TRUE'
),
Run a query to Download Data