crypto_edgarTotal Investors
Updated 2023-06-29
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 mooar_mic_drops_transfers as (
SELECT
DATE_TRUNC('Day', BLOCK_TIMESTAMP) AS DAY,
INSTRUCTION:accounts[0] as wallet_address,
value as tmp_instruction,
(tmp_instruction:parsed:info:amount)::float / POW(10, 9) as amount_gmt
FROM
solana.core.fact_events as events,
lateral flatten(input => events.INNER_INSTRUCTION:instructions)
WHERE BLOCK_TIMESTAMP BETWEEN '2023-06-20 00:00:00' AND CURRENT_TIMESTAMP()
AND PROGRAM_ID = 'E74bvE68HWB2bsdRiFdX55gSHDG7wWHhy8DhfjQs3iyB'
AND INSTRUCTION:accounts[2] = '7v34pHCKLtuZCopCmvaLFGPGewsG1PWUA9wd5PAWFNRH'
AND tmp_instruction:parsed:type = 'transfer'
AND tmp_instruction:parsed:info:destination = 'C22oU2kXGHEvaTz3KcKAaFv8HJKYyddvWEiyPQpwGrce'
AND SUCCEEDED = 'True'
ORDER BY amount_gmt),
mooar_mic_drops_rolling_amounts as (
SELECT
DATE_TRUNC('Day', BLOCK_TIMESTAMP) AS DAY,
SIGNERS[0] as wallet_address,
value AS matched_message,
(SUBSTRING(log.value, POSITION('rolling amount: ' IN log.value) + LENGTH('rolling amount: ')))::float / POW(10, 9) AS amount_gmt
FROM
solana.core.fact_transactions,
LATERAL FLATTEN(input => LOG_MESSAGES) log
WHERE
BLOCK_TIMESTAMP BETWEEN '2023-06-20 00:00:00' AND CURRENT_TIMESTAMP()
AND SUCCEEDED = 'True'
AND matched_message LIKE '%rolling amount: %'
),
final_table as (
(
SELECT *
FROM mooar_mic_drops_transfers
)
UNION
Run a query to Download Data