marquBERN - [activity] swap volume and running avg
Updated 2024-06-30
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
swap_txs as (
select distinct
tx_id
, signers[0] ::string as swapper
from solana.core.fact_events
inner join solana.core.fact_transactions
using(tx_id, block_timestamp, succeeded, block_id)
where succeeded
and program_id in (
'FLUXubRmkEi2q6K3Y9kBPg9248ggaZVsoSFhtJHSrm1X'
, 'JUP6i4ozu5ydDCnLiMogSckDPpbtr7BJ4FtzYWkb5Rk' -- v1
, 'JUP2jxvXaqu7NQY1GmNF4m1vodw12LVXYxbFL2uJvfo' -- v2
, 'JUP3c2Uh3WA4Ng34tw6kPd2G4C5BB21Xo36Je1s32Ph' -- v3
, 'JUP4Fb2cqiRUcaTHdrPC8h2gNsA2ETXiPDD33WcGuJB' -- v4
, 'JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4' -- v6
)
and array_contains('CKfatsPMUf8SkiURsDXs7eK6GWb4Jsd6UDbs7twMCWxo' ::variant, instruction :accounts) -- BERN
and array_contains('Program log: Instruction: Swap' ::variant, log_messages)
and block_timestamp > current_date() - interval '{{months}} months' - interval '1 month'
),
transfers as (
select
block_timestamp
, tx_id
, tx_from
, tx_to
, amount
, iff(swapper = tx_from, 'sell', 'buy') as label_action
QueryRunArchived: QueryRun has been archived