Sql query to calculate average daily volume in Dune Analytics

Question

I'm running this query in Dune Analytics, which shows me the daily trading volume for Uniswap for a designated timeframe. How can I calculate the average of daily trading volume of the past 365 days for example?

SELECT
    date_trunc('day', block_time) AS day,
    SUM(usd_amount) AS usd_volume
FROM dex.trades
WHERE block_time > now() - interval '365 days'
AND project = 'Uniswap'
GROUP BY 1
ORDER BY 1;

Answer 1

You divide the sum of 365 days by 365 if you want the average.
If you don't want to include weekend and public holidays you need to divide by the number of working days.

SELECT
    block_time) AS day,
    SUM(usd_amount) AS usd_volume_365_days,
    SUM(usd_amount) / 365 as avg_daily
FROM dex.trades
WHERE block_time > now() - interval '365 days'
AND project = 'Uniswap';

Answer 2

I figured it out:

select avg(usd_volume) from
(
SELECT
    date_trunc('day', block_time) AS day,
    SUM(usd_amount) AS usd_volume
FROM dex.trades
WHERE block_time > now() - interval '365days'
AND project = 'Uniswap'
GROUP BY 1
ORDER BY 1
)x