--start_date为统计开始日期格式为:2022-06-01 00:00:00
--Token Contract Address为Token合约地址
--价格计算逻辑为取当天的平均价格
select
    stat_date 
    ,price
from 
(
    select 
        stat_date 
        ,sum(usd_amount) *  power(10,{{decimals}})  / sum(token_a_amount_raw) as price
    from 
    (
            select 
                date_trunc('day', block_time) AS stat_date
                ,usd_amount 
                ,"token_a_amount_raw" as token_a_amount_raw
            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}}'
    
            union all 
    
            select 
                date_trunc('day', block_time) AS stat_date
                ,usd_amount 
                ,"token_a_amount_raw" as token_a_amount_raw
            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}}'
    ) pr
    group by 1
) a 
order by 1