jackguyTracking USDC & USDT on Solana
Updated 2022-06-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 tab1 as (
SELECT
-- date_trunc('month', block_timestamp) as DAY,
-- CASE WHEN mint LIKE 'Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB' then 'USDT'
-- WHEN mint LIKE 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' then 'USDC' END as ASSET,
address_name,
sum(amount) as SWAP_VOLUME
FROM solana.core.fact_transfers
LEFT outer JOIN solana.core.dim_labels
ON address = tx_to
where mint in ('USDH1SM1ojwWUga67PGrgFWUHibbjqMvuMaDkRJTgkX','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB','9vMJfxuKxXBoEa7rM12mYLMwTacLMLDJqHozw96WQL8','7kbnvuGBxxj8AG9qp8Scn56muWGaRaFqxg1FsRp3PaFT','Ea5SjE2Y6yvCeW5dYTn7PYMuW5ikXkvbGdcmSnXeaLjS','EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v')
AND BLOCK_TIMESTAMP > '2022-01-01'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 400
)
SELECT
CASE WHEN mint LIKE 'USDH1SM1ojwWUga67PGrgFWUHibbjqMvuMaDkRJTgkX' then 'USDH'
WHEN mint LIKE 'Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB' then 'USDT'
WHEN mint LIKE '9vMJfxuKxXBoEa7rM12mYLMwTacLMLDJqHozw96WQL8i' then 'UST'
WHEN mint LIKE '7kbnvuGBxxj8AG9qp8Scn56muWGaRaFqxg1FsRp3PaFT' then 'USDX'
WHEN mint LIKE 'Ea5SjE2Y6yvCeW5dYTn7PYMuW5ikXkvbGdcmSnXeaLjS' then 'PAI'
WHEN mint LIKE 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' then 'USDC' END as BIG_CATEGORY,
address_name as SMALL_CATEGORY,
count(DISTINCT tx_from) as VALUE
FROM solana.core.fact_transfers
LEFT outer JOIN solana.core.dim_labels
ON address = tx_to
WHERE address_name in (SELECT address_name from tab1)
and NOT CASE WHEN mint LIKE 'USDH1SM1ojwWUga67PGrgFWUHibbjqMvuMaDkRJTgkX' then 'USDH'
WHEN mint LIKE 'Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB' then 'USDT'
WHEN mint LIKE '9vMJfxuKxXBoEa7rM12mYLMwTacLMLDJqHozw96WQL8i' then 'UST'
WHEN mint LIKE '7kbnvuGBxxj8AG9qp8Scn56muWGaRaFqxg1FsRp3PaFT' then 'USDX'
WHEN mint LIKE 'Ea5SjE2Y6yvCeW5dYTn7PYMuW5ikXkvbGdcmSnXeaLjS' then 'PAI'
WHEN mint LIKE 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' then 'USDC' END IS NULL
Run a query to Download Data