--取Uniswap上某个ERC220 Token的通过ETH交易对计算出来的价格历史趋势(每天的价格都取当天所有交易的平均价格)
--Token Contract Address为Token合约地址
--decimals 为Token的精度，在以太坊浏览器中查询
select
    stat_date
    ,other_address
    ,avg((eth_amt/other_amt)*eth_prc) AS usd_price
from 
(
    select
        Swap.stat_date as stat_date
        ,other_address
        ,CASE WHEN token0 in ('\\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') then "amount0In" + "amount0Out" ELSE "amount1In" + "amount1Out" END/1e18 AS eth_amt
        ,CASE WHEN token0  not in ('\\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2')  then "amount0In" + "amount0Out" ELSE "amount1In" + "amount1Out" END/power(10,{{decimals}}) AS other_amt
        ,eth_prc
    from 
    (
        SELECT 
            contract_address  AS contract
            ,date_trunc('day', "evt_block_time") AS stat_date
            ,*
        FROM uniswap_v2."Pair_evt_Swap" 
    ) Swap
    join 
    (
        select
             contract
             ,"token0"
             ,"token1"
             ,eth_address
             ,other_address
        from 
        (
            SELECT 
                "pair" AS contract
                ,"token0"
                ,"token1"
                ,case when token0 in ('\\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2')  then token0 else token1 end as eth_address
                ,case when token0 not in ('\\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2')  then token0 else token1 end as other_address
            FROM uniswap_v2."Factory_evt_PairCreated" 
        ) a 
        where other_address = CONCAT('\\x', substring('{{Token Contract Address}}' from 3))::bytea -- Token address
        and eth_address in ('\\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') 
        group by 1,2,3,4,5
    )Factory_evt_PairCreated 
    on Swap.contract =Factory_evt_PairCreated.contract
    left outer join 
    (
    --取ETH的USD价格(当天平均)
        SELECT 
            date_trunc('day', minute) AS stat_date
            ,avg(price) eth_prc
        FROM prices.layer1_usd_eth
        group by 1
    )eth_prcs
    on eth_prcs.stat_date  = Swap.stat_date
) a 
group by 1,2
having avg((eth_amt/other_amt)*eth_prc) is not null
order by
    2,1