anomoneSomeone's CTE for Luna wallets
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 balance AS (
SELECT
date,
address,
sum (balance_usd) as total_balance
FROM terra.daily_balances
WHERE date > getdate() - interval '1 month'
GROUP BY 1,2
),
address_class as(
SELECT
date,
address,
CASE
WHEN total_balance >= 100000 THEN 'Large Wallet'
ELSE 'Smaller Wallet'
END AS address_tag,
total_balance
FROM balance
)
SELECT
date_trunc('day', s.block_timestamp) as date,
a.address_tag,
count(DISTINCT s.tx_id) as tx_count
FROM terra.transfers s
JOIN address_class a
ON (s.event_from = a.address)
AND date_trunc('day', s.block_timestamp) = a.date
WHERE date_trunc('day', s.block_timestamp) > getdate() - interval '1 month'
AND a.address_tag IS NOT NULL
GROUP BY 1,2
ORDER BY 1