lanomild-gray
Updated 2024-09-09
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 TX AS
(SELECT
date_trunc('month', block_timestamp) AS date,
from_address,
COUNT(tx_hash) AS cnt
FROM
kaia.core.fact_transactions
WHERE block_timestamp > CURRENT_DATE() - 90
GROUP BY
date, from_address
),
avg_tx AS
(
SELECT
from_address,
ROUND(SUM(cnt) / (SELECT COUNT(DISTINCT date_trunc('day', date)) FROM tx)) AS cnt
FROM
TX
GROUP BY
from_address
),
add_info as (
SELECT
from_address,
cnt,
CASE
WHEN cnt <= 3 THEN '<= 3' -- 상위 0.1%
WHEN cnt > 3 AND cnt <= 10 THEN '<= 10' -- 상위 0.1% 초과, 상위 1% 이하
WHEN cnt > 10 AND cnt <= 20 THEN '<= 20' -- 상위 1% 초과, 상위 5% 이하
WHEN cnt > 20 AND cnt <= 40 THEN '<= 40' -- 상위 5% 초과, 상위 10% 이하
WHEN cnt > 40 AND cnt <= 80 THEN '<= 80'
WHEN cnt > 80 THEN '> 80' -- 나머지
END AS category
FROM
avg_tx
),
QueryRunArchived: QueryRun has been archived