WITH ViewRangeBounds (Boundary) AS ( SELECT 0 UNION ALL S...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Christianity Meta

Q&A about the site for committed Christians, experts in Christianity and those interested in learning more

WITH ViewRangeBounds (Boundary) AS (
    SELECT 0
    UNION ALL
    SELECT Scale * Val
    FROM (VALUES(1),(2),(5)) AS ValTable (Val)
    CROSS JOIN (VALUES(1),(10),(100),(1000)) AS ScaleTable (Scale)
    UNION ALL
    SELECT 10000000000000000000000000
),
ViewRange AS (
    SELECT
        b1.Boundary AS LowerBound,
        MIN(b2.Boundary) AS UpperBound
    FROM ViewRangeBounds AS b1
    JOIN ViewRangeBounds AS b2 ON b1.Boundary < b2.Boundary
    GROUP BY b1.Boundary
)
SELECT 
    CONCAT(LowerBound, ' to ', UpperBound) AS ViewRangeLabel,
    PostsData.HasAnswer,
    COUNT(*) AS NumPosts,
    ROUND((100.0 * COUNT(*)) / SUM(COUNT(*)) OVER (PARTITION BY PostsData.HasAnswer), 3) AS PercentPostsByHasAnswer
FROM (
    SELECT
        Id,
        Score,
        ViewCount / (DATEDIFF(dd, CreationDate, GETDATE()) / 30.44) AS ViewsPerMonth,
        CASE
            WHEN AnswerCount = 0 THEN 'No'
            ELSE 'Yes'
        END AS HasAnswer
    FROM Posts
    WHERE
        Posts.PostTypeId = 1 -- Questions
        AND DeletionDate IS NULL
        AND ClosedDate IS NULL
) AS PostsData
JOIN ViewRange ON ViewRange.LowerBound <= PostsData.ViewsPerMonth AND PostsData.ViewsPerMonth < ViewRange.UpperBound
GROUP BY ViewRange.LowerBound, ViewRange.UpperBound, PostsData.HasAnswer
ORDER BY PostsData.HasAnswer, ViewRange.LowerBound

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results