Abolfazl_771025daily / weekly / monthly with more than 0% royalty
Updated 2022-09-25
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 yawww as( SELECT
block_timestamp,
instruction:accounts as user,
TX_ID,
COALESCE(inner_instruction:instructions[4]:parsed:info:lamports * POWER(10, -9), 0) AS platform_fee,
COALESCE(inner_instruction:instructions[5]:parsed:info:lamports * POWER(10, -9), 0) AS collection_royalty
FROM solana.core.fact_events e
LEFT JOIN solana.core.dim_labels l ON l.address = inner_instruction:instructions[0]:parsed:info:mint::string
WHERE instruction:programId = '5SKmrbAxnHV2sgqyDXkGrLrokZYtWWVEEk5Soed7VLVN'
--group by 1,2,3,4,5,6,7
), magic_eden as ( SELECT
block_timestamp,
instruction:accounts as user,
TX_ID,
COALESCE(inner_instruction:instructions[4]:parsed:info:lamports * POWER(10, -9), 0) AS platform_fee,
COALESCE(inner_instruction:instructions[5]:parsed:info:lamports * POWER(10, -9), 0) AS collection_royalty
FROM solana.core.fact_events e
LEFT JOIN solana.core.dim_labels l ON l.address = inner_instruction:instructions[0]:parsed:info:mint::string
WHERE instruction:programId in ('M2mx93ekt1fmXSVkTrUL9xVFHkmME8HTUi5Cyc5aF7K' , 'MEisE1HzehtrDpAAT8PnLHjpSSkRYakotTuJRPjTpo8')
),solanaart as( SELECT
block_timestamp,
instruction:accounts as user,
TX_ID,
COALESCE(inner_instruction:instructions[4]:parsed:info:lamports * POWER(10, -9), 0) AS platform_fee,
COALESCE(inner_instruction:instructions[5]:parsed:info:lamports * POWER(10, -9), 0) AS collection_royalty
FROM solana.core.fact_events e
LEFT JOIN solana.core.dim_labels l ON l.address = inner_instruction:instructions[0]:parsed:info:mint::string
WHERE instruction:programId = 'CJsLwbP1iu5DuUikHEJnLfANgKy6stB2uFgvBBHoyxwz'
)
select
date_trunc('day',block_timestamp) as "date(day)",
date_trunc('week',block_timestamp) as "date(week)",
date_trunc('month',block_timestamp) as "date(month)",
'yawww' as platform,
count(tx_id) as "total count of transaction",
count(DISTINCT user) as "total count of users"
Run a query to Download Data