IN_MINT | OUT_MINT | TOTAL_DCA_IN | NUM_OPEN_DCA | |
|---|---|---|---|---|
1 | EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v | So11111111111111111111111111111111111111112 | 33420471.384434 | 17989 |
2 | UxxvnMnUGoXJig9wc8phE1mYJZMFq3hpfr259zMWcq4 | JxxWsvm9jHt4ah7DT9NuLyVLYZcZLUdPD93PcPQ71Ka | 288879467.16221 | 15764 |
3 | EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v | WENWENvqqNya429ubCdR81ZmD69brwQaaBYY6p3LCpk | 16291601.713315 | 14047 |
4 | EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v | JUPyiwrYJFskUPiHa7hkeR8VUtAeFoSYbKedZNsDvCN | 88891496.966703 | 11790 |
5 | So11111111111111111111111111111111111111112 | EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v | 253322.54308255 | 8679 |
6 | So11111111111111111111111111111111111111112 | WENWENvqqNya429ubCdR81ZmD69brwQaaBYY6p3LCpk | 55763.175633092 | 7695 |
7 | EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v | Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB | 3444683.756852 | 6686 |
8 | Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB | EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v | 3956467.761187 | 6450 |
9 | JxxWsvm9jHt4ah7DT9NuLyVLYZcZLUdPD93PcPQ71Ka | UxxvnMnUGoXJig9wc8phE1mYJZMFq3hpfr259zMWcq4 | 23912133.8406375 | 4602 |
10 | So11111111111111111111111111111111111111112 | JUPyiwrYJFskUPiHa7hkeR8VUtAeFoSYbKedZNsDvCN | 107014.681198623 | 3963 |
Flipside Communityjupiter dca routes using fact transactions
Updated 2024-01-31
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
›
⌄
⌄
/**
Given:
- program id 'DCA265Vj8a9CEuX1eb1LWRnDT7uK6q1xMipnNyatn23M' (Jupiter DCA)
- event name 'openDca' (or 'openDcaV2')
- sample tx id '2wc4aT9eDvejvJUjd4Gx1NDwbidTi2EXkPNKtDxSDvj3dRgwAvmhMp2fDCb1qGhjEtgAp6JeM6f8Lnoc7aYRwh9B' on 2023-12-31
- query below that uses the new "solana.core.fact_decoded_instructions" table to get:
in and out token address pairs, and for each pair the number of DCAs initiated and the total amount in
Task:
- Write a query to get the same result without using
"solana.core.fact_decoded_instructions",
e.g. just using fact_transactions or fact_events
**/
WITH dca AS (
SELECT
block_timestamp,
tx_id,
decoded_instruction :name :: STRING AS event_name,
decoded_instruction :accounts [1] :pubkey :: STRING AS USER,
decoded_instruction :accounts [2] :pubkey :: STRING AS in_mint,
decoded_instruction :accounts [3] :pubkey :: STRING AS out_mint,
decoded_instruction :args :cycleFrequency :: INT AS cycle_freq_secs,
decoded_instruction :args :inAmount :: INT AS total_dca_amount,
decoded_instruction :args :inAmountPerCycle :: INT AS dca_per_cycle
FROM
solana.core.fact_decoded_instructions
WHERE
block_timestamp :: DATE >= CURRENT_DATE() - INTERVAL '1 months'
AND program_id = 'DCA265Vj8a9CEuX1eb1LWRnDT7uK6q1xMipnNyatn23M'
AND decoded_instruction :name :: STRING = 'openDca'
UNION ALL
SELECT
block_timestamp,
tx_id,
Last run: about 1 year ago
10
1KB
5s