0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for users and developers of distributed applications built on top of the blockchain operating system, EOS.IO

DECLARE @startDate DATE = '2018-03-01';
DECLARE @endDate DATE = '2021-06-30';

WITH months AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY DATEADD(MONTH, n - 1, @startDate)) AS month_number,
        DATEADD(MONTH, n - 1, @startDate) AS start_date,
        DATEADD(DAY, -1, DATEADD(MONTH, n, @startDate)) 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, @startDate, @endDate) + 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

created jul 20 anonymous