lanomild-gray
    Updated 2024-09-09
    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