bachihadeswap7
Updated 2023-01-20
999
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
›
⌄
-- fees generated by pools
WITH FLATTEN_SOL_TRANSFER AS (
SELECT
tx_id as tx_id_flatten,
pre.index as index_value,
pre.value as pre_value,
post.value as post_value,
abs(post_value - pre_value)/pow(10,9) as balance_change,
account.value:pubkey as pubkey, -- Flatten address
account.value:signer as signer,
account.value:writable as writable
FROM solana.core.fact_transactions,
lateral flatten(input => pre_balances) pre,
lateral flatten(input => post_balances) post,
lateral flatten(input => account_keys) account
WHERE 1=1
AND SUCCEEDED = TRUE
-- AND block_timestamp > '2022-09-21'
AND instructions[0]:programId = 'hadeK9DLv9eA7ya5KCTqSvSvRZeJC3JgD5a9Y3CNbvu'
AND pre.index = post.index --* Important
AND pre.index = account.index --* Important
AND post.index = account.index
),
POOL_ADDRESS_OWNER AS (
-- Why do we need to find the pool_owner for pool_address?
-- This is because for Hadeswaps sales, we only have the SOL-NFT LP Holding Address, but we don't have the original LP-Owner's Address
-- Hence, we need to find it and LEFT JOIN it to SELL to pairs
-- Initialise Pair Has 2 tx_id
-- 1. 5asaw4jzKKNxrnn8gDErjYRCqbeJYj6wzC3VUmJdnJizT5NyiFkeRU5WvyQd8YLrruRckyHdrSqQJYzdjnRbpA1A
-- 2. 3n6vtNzrCM2YhFpZiRNKnCd5sVjKYJaVEdhkpPAUvHxuv45eh7GScUjyn6SqCWmpxtoZas22jfZUPurXNqnfuX8S
-- Only the second one has information about the pool
SELECT
split(log_messages[1], ':')[2] as program_instruction1,
signers[0] as pool_owner,
inner_instructions[0]:instructions[0]:parsed:info:lamports/pow(10,9) as lamports,
inner_instructions[0]:instructions[0]:parsed:info:newAccount as newAccount, -- pool address created by user
Run a query to Download Data