--start_date为统计开始日期格式为:2022-06-01 00:00:00
--Token Contract Address为Token合约地址
--decimals 为Token的精度，在以太坊浏览器中查询
select 
    (stat_date + '8 hour'::interval) as stat_date
    ,sum(volume_usd_amount) as volume_usd_amount
from 
(
        select 
             
            date_trunc('hour', block_time) AS stat_date
            ,sum(usd_amount) / sum("token_a_amount") as price
            ,sum(usd_amount  *  power(10,{{decimals}}) ) / sum("token_a_amount_raw") as a_price
            ,sum(usd_amount) as volume_usd_amount
        from dex.trades
        where "token_a_address" = CONCAT('\\x', substring('{{Token Contract Address}}' from 3))::bytea -- Token address
        and "token_b_amount_raw" >0
        and block_time > '{{start_date}}'
        group by 1    

        union all 

        select 
            date_trunc('hour', block_time) AS stat_date
            ,sum(usd_amount)  / sum("token_b_amount") as price
            ,sum(usd_amount  * power(10,{{decimals}}) )  / sum("token_b_amount_raw") as a_price
            ,sum(usd_amount) as volume_usd_amount
        from dex.trades
        where "token_b_address" = CONCAT('\\x', substring('{{Token Contract Address}}' from 3))::bytea -- Token address
        and "token_b_amount_raw" >0
        and block_time > '{{start_date}}'
        group by 1
) pr
group by
    (stat_date + '8 hour'::interval)
order by
    (stat_date + '8 hour'::interval)