winnie-fs2. Wallet Distribution of Users Transferring To Quaser copy
Updated 2023-04-25
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
›
⌄
-- forked from zackmendel / 2. Wallet Distribution of Users Transferring To Quaser @ https://flipsidecrypto.xyz/zackmendel/q/4-e-wallet-distribution-of-delegators-and-non-delegators-arb-balance-N4yR2m
WITH wallet AS (
SELECT
DISTINCT sender AS user
FROM osmosis.core.fact_transfers
WHERE receiver LIKE 'quasar%'
),
transfer_out AS (
SELECT
DISTINCT sender AS user1,
sum (amount/pow(10,decimal)) AS transfer_out
FROM osmosis.core.fact_transfers
WHERE currency = 'uosmo'
AND sender IN (SELECT user FROM wallet)
GROUP BY 1
),
transfer_in AS (
SELECT
DISTINCT receiver AS user2,
sum (amount/pow(10,decimal)) AS transfer_in
FROM osmosis.core.fact_transfers
WHERE currency = 'uosmo'
AND sender IN (SELECT user FROM wallet)
GROUP BY 1
),
balance AS (
SELECT
user1 AS wallet,
transfer_in - transfer_out AS balance
FROM transfer_in i JOIN transfer_out o ON i.user2 = o.user1
ORDER BY 2 DESC
)
SELECT
CASE
WHEN balance < 0.1 THEN 'Plankton<0.1'::varchar
Run a query to Download Data