--Token Contract Address为Token合约地址
--decimals 为Token的精度，在以太坊浏览器中查询
--Wallet Address为对应的钱包地址
SELECT
	  Wallet_Address
    ,total_day.stat_date as stat_date
    ,sum(distinct total_amount/power(10,{{decimals}})) as total_amount_cnt
from 
( 
    SELECT generate_series('2021-01-01'::TIMESTAMP, date_trunc('hour', NOW()), '1 hour') AS stat_date-- Generate all days since the first contract
)total_day
join 
(
    SELECT 
        stat_date
        ,replace(cast(Wallet_Address as varchar),'\\','0')  as Wallet_Address
        ,sum(amount) as total_amount
    FROM 
    (

        SELECT 
            date_trunc('hour', evt_block_time) AS stat_date
            ,"from" AS Wallet_Address
            , -SUM(value) AS amount
        FROM erc20."ERC20_evt_Transfer"
        WHERE contract_address = CONCAT('\\x', substring('{{Token Contract Address}}' from 3))::bytea -- Token address
        GROUP BY 1,2

        UNION ALL

        SELECT 
            date_trunc('hour', evt_block_time) AS DAY
            ,"to" AS Wallet_Address
            , SUM(value) AS amount
        FROM erc20."ERC20_evt_Transfer"
        WHERE contract_address = CONCAT('\\x', substring('{{Token Contract Address}}' from 3))::bytea -- Token address
        GROUP BY 1,2
    ) to_from
    where Wallet_Address = CONCAT('\\x', substring('{{Wallet Address}}' from 3))::bytea -- Token address
    GROUP BY  1,2
) total_trans
ON total_trans.stat_date <= total_day.stat_date
group by 1,2
order by
    1,2