carlesmontala2-Open Analytics Bounty: Solana (November 26)
Updated 2022-12-03
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 dust_token_price as (
SELECT
date_Trunc('day', block_timestamp) as pdate,
median(swap_from_amount/swap_to_amount) as Dust_price
FROM solana.core.fact_swaps
WHERE succeeded = 'TRUE'
AND swap_to_mint = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ'
AND swap_from_mint IN ('7kbnvuGBxxj8AG9qp8Scn56muWGaRaFqxg1FsRp3PaFT','Ea5SjE2Y6yvCeW5dYTn7PYMuW5ikXkvbGdcmSnXeaLjS','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB', 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v', 'USDH1SM1ojwWUga67PGrgFWUHibbjqMvuMaDkRJTgkX')
AND swap_from_amount>0 AND swap_to_amount>0
and block_timestamp >='2022-03-15'
GROUP BY 1
),
holders AS (
SELECT
block_timestamp,
post_balance.value:owner AS wallet,
post_balance.value:mint AS token,
post_balance.value:uiTokenAmount:uiAmount AS amount,
ROW_NUMBER() OVER (PARTITION BY wallet, token ORDER BY block_timestamp DESC) AS rank
FROM solana.core.fact_transactions,
LATERAL FLATTEN(input => post_token_balances) post_balance
WHERE SUCCEEDED = 'True'
AND post_balance.value:owner = wallet
AND post_balance.value:mint = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ'
AND block_timestamp >= '2022-03-15'
ORDER BY 1,2,4
),
T1 as (
select
block_timestamp::date as date,
sum(amount) as amounts
FROM holders
WHERE rank = 1
AND amount IS NOT NULL
Run a query to Download Data