Views over Negative Vote ratio, by week

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @strtDate DATE = ##StartDate:string?2015-01-01##;

WITH votesByDate AS (
    SELECT      v.votetypeid,
                v.CreationDate,
                COALESCE (p.ViewCount, q.ViewCount) AS ViewCount,
                p.posttypeid
    FROM        Votes   v
    JOIN        Posts   p
    ON          p.id    = v.postid
    LEFT JOIN   Posts   q
    ON          q.id    = p.ParentId
    WHERE       v.CreationDate > DATEADD(year, 0, @strtDate)
    AND         p.posttypeid IN (1, 2)
)
SELECT      DATEADD(week, DATEDIFF(week, 0, vbd.CreationDate), 0)     AS [Week],
            -- Need this extra conversion, depending on date range
            --SUM( vbd.ViewCount * 1.0) * 1.0
            --SUM( vbd.ViewCount) * 1.0
            SUM( CASE WHEN vbd.posttypeid = 1 then vbd.ViewCount * 1.0 else 0 end ) * 1.0
            /
            SUM( CASE WHEN vbd.posttypeid = 1  AND  vbd.votetypeid = 3 then 1 else 0 end )
            AS [Total Views / Neg Votes ratio (For Q's, overall is similar)]
FROM        votesByDate vbd
GROUP BY    DATEADD(week, DATEDIFF(week, 0, vbd.CreationDate), 0)
ORDER BY    DATEADD(week, DATEDIFF(week, 0, vbd.CreationDate), 0)

Enter Parameters

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