jp12Flash [SOL] Saga - Num orders
Updated 2023-09-09Copy Reference Fork
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
›
⌄
-- SELECT COUNT(DISTINCT tx_id) as num_orders, 100 * num_orders as amount
-- -- FROM solana.core.fact_transactions
-- FROM solana.core.fact_events
-- -- WHERE tx_id = 'MuQC1cGUVyeLD1AMvTFZSpJULMKAtbQbPuF921cZVLWuagb1CJZ9DdNe29MzJ6hnmb1YpZWgBKjzC1mkfXwetNx'
-- WHERE block_timestamp >= '2022-06-22' and PROGRAM_ID = '781wH11JGQgEoBkBzuc8uoQLtp8KxeHk1yZiS1JhFYKy'
-- and SUCCEEDED = 'TRUE' and INNER_INSTRUCTION:instructions[1]:parsed:info:amount = '100000000'
WITH tx as (
SELECT INNER_INSTRUCTION:instructions[1]:parsed:info:authority::string as user, 1 as num_order
-- FROM solana.core.fact_transactions
FROM solana.core.fact_events
-- WHERE tx_id = 'MuQC1cGUVyeLD1AMvTFZSpJULMKAtbQbPuF921cZVLWuagb1CJZ9DdNe29MzJ6hnmb1YpZWgBKjzC1mkfXwetNx'
WHERE block_timestamp >= '2022-06-22' and PROGRAM_ID = '781wH11JGQgEoBkBzuc8uoQLtp8KxeHk1yZiS1JhFYKy'
and SUCCEEDED = 'TRUE' and INNER_INSTRUCTION:instructions[1]:parsed:info:amount = '100000000'
UNION
SELECT INNER_INSTRUCTION:instructions[1]:parsed:info:authority::string as user, -1 as num_order
-- FROM solana.core.fact_transactions
FROM solana.core.fact_events
-- WHERE tx_id = 'MuQC1cGUVyeLD1AMvTFZSpJULMKAtbQbPuF921cZVLWuagb1CJZ9DdNe29MzJ6hnmb1YpZWgBKjzC1mkfXwetNx'
WHERE block_timestamp >= '2022-06-22' and PROGRAM_ID = '781wH11JGQgEoBkBzuc8uoQLtp8KxeHk1yZiS1JhFYKy'
and SUCCEEDED = 'TRUE' and INNER_INSTRUCTION:instructions[0]:parsed:info:amount = '100000000'
)
SELECT SUM(num_order) as total_orders, 100 * total_orders as amount
FROM tx
-- GROUP BY 1
HAVING total_orders > 0
ORDER BY total_orders DESC
Run a query to Download Data