Afonso_Diaz2023-08-14 11:58 PM
Updated 2023-09-02
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
›
⌄
-- forked from https://flipsidecrypto.xyz/marqu/q/NLbZxNB0lMvQ/tensorians-top-100-sales-by-sol-amount
with
t1 as (
select
instruction:accounts[5] as merkle_tree,
row_number() over (partition by merkle_tree order by block_timestamp) -1 as leaf_index,
regexp_replace(f_logs.value, '^Program log: Default cNFT Name Suffix: ') as token_id
from solana.core.fact_events
join lateral flatten (input => inner_instruction :instructions) f
join solana.core.fact_transactions
using(tx_id, block_timestamp, succeeded)
join lateral flatten (input => log_messages) f_logs
where f.value:programId = 'metaqbxxUerdq28cj1RbAWkYQm3ybzjb6a8bt518x1s'
and program_id = 'TGARDaEzs7px1tEUssCCZ9ewhTW7oCA1MnY5y7rQk9n'
and instruction:accounts::string like '%5PA96eCFHJSFPY9SWFeRJUHrpoNF5XZL6RrE1JADXhxf%'
and f.value :accounts[2] = '5PA96eCFHJSFPY9SWFeRJUHrpoNF5XZL6RrE1JADXhxf'
and signers[1] = 'DfGoQrVBxvbnCeqcwwomRHn74xN5gBGKDCpgBGsSbYmm'
and f_logs.value like 'Program log: Default cNFT Name Suffix: %'
and succeeded = 1
),
t2 as (
select
tx_id,
block_timestamp,
signers[0] as seller,
a.instruction:accounts[12] as purchaser,
abs(post_balances[0] - pre_balances[0] + iff((post_balances[0] + fee) < pre_balances[0], +fee, -fee)) / 1e9 as amount_sol,
token_id
from solana.core.fact_events a
join solana.core.fact_transactions
using(tx_id, block_timestamp)
join lateral flatten (input => log_messages) f_logs
Run a query to Download Data