marquMango v4 - [capital] inflow and outflow
Updated 2023-04-16
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
inout as (
select distinct tx_id, signers[0] as user_address
from solana.core.fact_events events
inner join solana.core.fact_transactions txs
using(tx_id, block_timestamp, succeeded)
where succeeded
and program_id = '4MangoMjqJ2firMokCjjGgoK8d4MXcrgL7XJaL3w6fVg'
and block_timestamp > '2022-12-08'
and (array_contains('Program log: Instruction: TokenWithdraw' ::variant, txs.log_messages)
or array_contains('Program log: Instruction: TokenDeposit' ::variant, txs.log_messages))
-- and tx_id = '5GPi9SMz9HSpJALToeCHHbcCJeXU84pxaZJVsF31ftYUcPFy7iSMGtQbWce4mDcFt238SdU5J77jAGhKtc4d123D'
),
transfers as (
select
block_timestamp,
tx_id,
user_address,
mint,
iff (tx_to = '78b8f4cGCwmZ9ysPFMWLaLTkkaYnUjwMJYStWe5RTSSX', amount, amount * (-1)) as amount,
iff (tx_to = '78b8f4cGCwmZ9ysPFMWLaLTkkaYnUjwMJYStWe5RTSSX', amount, amount * (-1)) * close as amount_usd
from solana.core.fact_transfers transfers
inner join inout
using(tx_id)
left join solana.core.ez_token_prices_hourly prices
on date_trunc('hour',transfers.block_timestamp) = prices.recorded_hour
and transfers.mint = prices.token_address
where block_timestamp > '2022-12-08'
and (tx_from = '78b8f4cGCwmZ9ysPFMWLaLTkkaYnUjwMJYStWe5RTSSX'
or tx_to = '78b8f4cGCwmZ9ysPFMWLaLTkkaYnUjwMJYStWe5RTSSX')
Run a query to Download Data