0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

DECLARE @startMonth INT = MONTH('2021-04-01');
DECLARE @startYear INT = YEAR('2021-04-01');

DECLARE @endMonth INT = MONTH('2022-03-31');
DECLARE @endYear INT = YEAR('2022-03-31');

WITH months AS (
    SELECT 
        MONTH(DATEFROMPARTS(@startYear, n, 1)) AS month_number,
        DATEFROMPARTS(@startYear, n, 1) AS start_date,
        CASE 
            WHEN MONTH(DATEFROMPARTS(@startYear, n, 1)) = @endMonth AND YEAR(DATEFROMPARTS(@startYear, n, 1)) = @endYear THEN '2022-03-31'
            ELSE DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEFROMPARTS(@startYear, n, 1)))
        END AS end_date
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
        FROM sys.objects a
        CROSS JOIN sys.objects b
    ) t
    WHERE n <= DATEDIFF(MONTH, DATEFROMPARTS(@startYear, @startMonth, 1), DATEFROMPARTS(@endYear, @endMonth, 1)) + 1
)
SELECT 
    m.month_number,
    m.start_date,
    m.end_date,
    COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 END) AS total_questions_per_month,
    CASE 
        WHEN COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 END) = 0 THEN 0
        ELSE SUM(CASE WHEN p.PostTypeId = 1 THEN p.Score END) * 1.0 / COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 END)
    END AS average_score_questions_per_month,
    COUNT(CASE WHEN p.PostTypeId = 2 THEN 1 END) AS total_answers_per_month,
    CASE 
        WHEN COUNT(CASE WHEN p.PostTypeId = 2 THEN 1 END) = 0 THEN 0
        ELSE SUM(CASE WHEN p.PostTypeId = 2 THEN p.Score END) * 1.0 / COUNT(CASE WHEN p.PostTypeId = 2 THEN 1 END)
    END AS average_score_answers_per_month,
    CASE 
        WHEN COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 END) = 0 THEN 0
        ELSE SUM(CASE WHEN p.PostTypeId = 1 THEN p.ViewCount END) * 1.0 / COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 END)
    END AS average_views_per_question_per_month,
    CASE 
        WHEN COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 END) = 0 THEN 0
        ELSE COUNT(CASE WHEN p.PostTypeId = 1 AND p.AcceptedAnswerId IS NOT NULL THEN 1 END) * 1.0 / COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 END)
    END AS solution_ratio_per_month
FROM months m
JOIN Posts p ON p.CreationDate >= m.start_date AND p.CreationDate <= m.end_date
GROUP BY m.month_number, m.start_date, m.end_date
ORDER BY m.month_number ASC;

fork query

edited jul 20

created jul 20 anonymous