--start_date为统计开始日期格式为:2022-06-01 00:00:00
--Token Contract Address为Token合约地址
WITH transfers AS (
    SELECT DAY,
    address,
    token_address,
    sum(amount) AS amount 
    FROM
    ( 
        SELECT date_trunc('day', evt_block_time) AS DAY,
            "to" AS address,
            tr.contract_address AS token_address,
            value AS amount
        FROM erc20."ERC20_evt_Transfer" tr
        WHERE contract_address =  CONCAT('\\x', substring('{{Token Contract Address}}' from 3))::bytea -- Token address
        
        UNION ALL 
        
        SELECT date_trunc('day', evt_block_time) AS DAY,
            "from" AS address,
            tr.contract_address AS token_address, -value AS amount
        FROM erc20."ERC20_evt_Transfer" tr
        WHERE contract_address =  CONCAT('\\x', substring('{{Token Contract Address}}' from 3))::bytea -- Token address
    ) t
    GROUP BY 1, 2, 3
),
balances_with_gap_days AS ( 
    SELECT t.day,
        address,
        SUM(amount) OVER (PARTITION BY address ORDER BY t.day) AS balance,
        lead(DAY, 1, now()) OVER (PARTITION BY address ORDER BY t.day) AS next_day
        FROM transfers t 
),
days AS ( 
    SELECT generate_series('{{start_date}}'::TIMESTAMP, date_trunc('day', NOW()), '1 day') AS DAY
),
balance_all_days AS ( 
    SELECT d.day,
        address,
        SUM(balance/1e9) AS balance
    FROM balances_with_gap_days b
    INNER JOIN days d ON b.day <= d.day
    AND d.day < b.next_day
    GROUP BY 1, 2
    ORDER BY 1, 2 
),

Hodler_info as (SELECT b.day AS 日期,
    COUNT(address) AS 累计HODLer数,
    COUNT(address) - lag(COUNT(address)) OVER (ORDER BY b.day) AS 累计新增HODLer数
FROM balance_all_days b
WHERE balance > 0
GROUP BY 1
ORDER BY 日期 )

select 日期, 累计HODLer数
from Hodler_info