FlippppppaDialect Trading Total copy
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
›
⌄
-- forked from kiryl_sol / Dialect Trading Total @ https://flipsidecrypto.xyz/kiryl_sol/q/dialect-trading-per-day-rTmdVZ
WITH
filtered_transactions AS (
SELECT
DATE_TRUNC('DAY', BLOCK_TIMESTAMP) as date,
SIGNERS[0]::STRING AS signer,
PRE_BALANCES[0]::NUMBER AS pre_balance,
POST_BALANCES[0]::NUMBER AS post_balance
FROM
solana.core.fact_transactions,
LATERAL FLATTEN(input => ACCOUNT_KEYS) AS ACCOUNT_KEYS1,
LATERAL FLATTEN(input => ACCOUNT_KEYS) AS ACCOUNT_KEYS2,
LATERAL FLATTEN(input => INSTRUCTIONS) AS INSTRUCTIONS
WHERE
ACCOUNT_KEYS1.value:pubkey::STRING = 'TCMPhJdwDryooaGtiocG1u3xcYbRpiJzb283XfCZsDp'
AND ACCOUNT_KEYS2.value:pubkey::STRING = 'BGUMAp9Gq7iTEuizy4pqaxsTyUCBK68MDfK752saRPUY'
and INSTRUCTIONS.value:data::STRING like '%4euRRQ61%'
AND BLOCK_TIMESTAMP >= '2023-05-17'
AND SUCCEEDED = TRUE
)
SELECT
count(DISTINCT signer) as unique_traders,
count(*) as total_trades,
SUM(post_balance - pre_balance) / 1000000000 as volume,
(
SUM(post_balance - pre_balance) / 1000000000 / NULLIF(COUNT(*), 0)
) as average_trade
FROM
filtered_transactions
Run a query to Download Data