Calculate Average by SQL SERVER periods

Question

Good night, 2 days of struggle I managed to get this result, however, I’m not able to take the daily average per month and weekly average per year.

1 Query

SELECT a.Ano, a.Mês, a.Reviews
FROM (
    SELECT
        YEAR(r.DataInclusao) AS Ano,
        MONTH(r.DataInclusao) AS Mês,
        COUNT(r.DataInclusao) AS Reviews
    FROM ProdutoSkuReview r
    GROUP BY YEAR(r.DataInclusao), MONTH(r.DataInclusao)
) a
ORDER BY a.Ano, a.Mês

Upshot:
QUERY 2

SELECT a.Dia, a.Mês, a.Ano, a.Reviews
FROM (
    SELECT
        DAY(r.DataInclusao) AS Dia,
        MONTH(r.DataInclusao) AS Mês,
        YEAR(r.DataInclusao) AS Ano,
        COUNT(r.DataInclusao) AS Reviews
    FROM ProdutoSkuReview r
    GROUP BY YEAR(r.DataInclusao), MONTH(r.DataInclusao), DAY(r.DataInclusao)
) a
ORDER BY a.Ano, a.Mês, a.Dia

RESULT:

Answer 1

Use the Clause OVER():

The Clause OVER() used in conjunction with the PARTITION BY divides the result of Select on partitions allowing you to use functions that, without this artifice, required the use of other solutions as sub-consultations, for example.
I sought to adapt the clause to the code already provided in your question to facilitate your understanding.

See examples of clause use over applied to their needs:

Daily Average:

SELECT a.Dia, a.Mes, a.Ano, a.Reviews, AVG(Reviews) OVER(PARTITION BY Ano, Mes) AS Media
FROM (
    SELECT
        DAY(r.DataInclusao) AS Dia,
        MONTH(r.DataInclusao) AS Mes,
        YEAR(r.DataInclusao) AS Ano,
        COUNT(r.DataInclusao) AS Reviews
    FROM ProdutoSkuReview r
    GROUP BY YEAR(r.DataInclusao), MONTH(r.DataInclusao), DAY(r.DataInclusao)
) a
ORDER BY a.Ano, a.Mes, a.Dia

Monthly Average:

SELECT a.Ano, a.Mes, a.Reviews, AVG(Reviews) OVER(PARTITION BY Ano) AS Media
    FROM (
        SELECT
            YEAR(r.DataInclusao) AS Ano,
            MONTH(r.DataInclusao) AS Mes,
            COUNT(r.DataInclusao) AS Reviews            
        FROM ProdutoSkuReview r
        GROUP BY YEAR(r.DataInclusao), MONTH(r.DataInclusao)
    ) a
ORDER BY a.Ano, a.Mes

SELECT - OVER clause (Transact-SQL): Determines the partitioning and ordering of a set of lines before the application of the associated window function. That is, the OVER clause defines a window or set of lines specified by the user in a query result set. A window function computes a value for each line in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, aggregations cumulative, cumulative sum or first N results per group.

Read more about the clause OVER in SELECT - OVER clause (Transact-SQL)

Edited

Result Month, Year, Reviews_no_mes, Media_diaria_no_mes:

whereas the expected result is: Month, Year, Reviews_no_mes, Media_diaria_no_mes, a valid solution is as follows:

SELECT DISTINCT 
    MONTH(r.DataInclusao) AS Mes,
    YEAR(r.DataInclusao) AS Ano,
    SUM(COUNT(r.DataInclusao)) OVER(PARTITION BY YEAR(r.DataInclusao), MONTH(r.DataInclusao)) Reviews_no_mes,
    AVG(COUNT(r.DataInclusao)) OVER(PARTITION BY YEAR(r.DataInclusao), MONTH(r.DataInclusao)) Media_Diaria_no_mes
FROM ProdutoSkuReview r
GROUP BY YEAR(r.DataInclusao), MONTH(r.DataInclusao), DAY(r.DataInclusao)
order by YEAR(r.DataInclusao), MONTH(r.DataInclusao)

On the consultation
COUNT(r.DataInclusao) account for grouped views per day due to GROUP BY YEAR(r.DataInclusao), MONTH(r.DataInclusao), DAY(r.DataInclusao). So based on this result, we partitioned to make calculations per year and month, adding up the daily counts through the SUM(COUNT(r.DataInclusao)) of the partition, we get the total count of the month I called Reviews_no_mes. To get the monthly average, we use the command AVG(COUNT(r.DataInclusao)) which shall take into account the average of the counting of the days of the profit or loss partitioned by the year.

Another way to get the same expected result: Adapting his 1 Query, would look like this:

SELECT a.Ano, a.Mês, sum(a.Reviews) as total_mes, AVG(a.Reviews) as media_no_mes 
FROM (
    SELECT
        YEAR(r.DataInclusao) AS Ano,
        MONTH(r.DataInclusao) AS Mês,
        COUNT(r.DataInclusao) AS Reviews
    FROM ProdutoSkuReview r
    GROUP BY YEAR(r.DataInclusao), MONTH(r.DataInclusao), DAY(r.DataInclusao) 
) a
GROUP by Ano, Mês
ORDER BY a.Ano, a.Mês