0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people who build and repair bicycles, people who train cycling, or commute on bicycles

CREATE TABLE #Results
(
    PostTypeId       INT,
    DaysSincePosting INT,
    Upvotes          INT,
    Downvotes        INT,
    TotalVotes       INT
);

INSERT INTO
    #Results
SELECT
    p.PostTypeId,
    DATEDIFF(DAY, CAST(p.CreationDate AS DATE), CAST(v.CreationDate AS DATE)),
    SUM(IIF(v.VoteTypeId = 2, 1, 0)),
    SUM(IIF(v.VoteTypeId = 3, 1, 0)),
    COUNT(*) AS [All Votes]
FROM
    Posts p
        JOIN Votes v ON p.Id = v.PostId
WHERE
    p.PostTypeId IN (1, 2) AND
    v.VoteTypeId IN (2, 3) AND
    DATEDIFF(DAY, CAST(p.CreationDate AS DATE), CAST(v.CreationDate AS DATE)) >= 0 -- Something Wrong with underlying data
GROUP BY p.PostTypeId,
         DATEDIFF(DAY, CAST(p.CreationDate AS DATE), CAST(v.CreationDate AS DATE));

DECLARE @percentFmtStr CHAR(2) = 'P3';
DECLARE @commaSepFmtStr CHAR(3) = '#,0';

-- Questions
SELECT
    DaysSincePosting,
    FORMAT(Upvotes, @commaSepFmtStr) AS [Question Upvotes],
    FORMAT(Downvotes, @commaSepFmtStr) AS [Question Downvotes],
    FORMAT(TotalVotes, @commaSepFmtStr) AS [Question Votes],
    FORMAT(CAST(Upvotes AS FLOAT) / CAST(TotalVotes AS FLOAT), @percentFmtStr) AS [Upvotes as a percentage of daily votes],
    FORMAT(CAST(Downvotes AS FLOAT) / CAST(TotalVotes AS FLOAT), @percentFmtStr) AS [Downvotes as a percentage of daily votes]
FROM
    #Results
WHERE
    PostTypeId = 1
ORDER BY DaysSincePosting;

-- Questions Ordered By Percent of Daily Downvotes
SELECT
    DaysSincePosting,
    FORMAT(Upvotes, @commaSepFmtStr) AS [Question Upvotes],
    FORMAT(Downvotes, @commaSepFmtStr) AS [Question Downvotes],
    FORMAT(TotalVotes, @commaSepFmtStr) AS [Question Votes],
    FORMAT(CAST(Upvotes AS FLOAT) / CAST(TotalVotes AS FLOAT), @percentFmtStr) AS [Upvotes as a percentage of daily votes],
    FORMAT(CAST(Downvotes AS FLOAT) / CAST(TotalVotes AS FLOAT), @percentFmtStr) AS [Downvotes as a percentage of daily votes]
FROM
    #Results
WHERE
    PostTypeId = 1
ORDER BY CAST(Downvotes AS FLOAT) / CAST(TotalVotes AS FLOAT) DESC;

-- Questions Ordered By Percent of Daily Upvotes
SELECT
    DaysSincePosting,
    FORMAT(Upvotes, @commaSepFmtStr) AS [Question Upvotes],
    FORMAT(Downvotes, @commaSepFmtStr) AS [Question Downvotes],
    FORMAT(TotalVotes, @commaSepFmtStr) AS [Question Votes],
    FORMAT(CAST(Upvotes AS FLOAT) / CAST(TotalVotes AS FLOAT), @percentFmtStr) AS [Upvotes as a percentage of daily votes],
    FORMAT(CAST(Downvotes AS FLOAT) / CAST(TotalVotes AS FLOAT), @percentFmtStr) AS [Downvotes as a percentage of daily votes]
FROM
    #Results
WHERE
    PostTypeId = 1
ORDER BY CAST(Upvotes AS FLOAT) / CAST(TotalVotes AS FLOAT) DESC;

-- Answers
SELECT
    DaysSincePosting,
    FORMAT(Upvotes, @commaSepFmtStr) AS [Answer Upvotes],
    FORMAT(Downvotes, @commaSepFmtStr) AS [Answer Downvotes],
    FORMAT(TotalVotes, @commaSepFmtStr) AS [Answer Votes],
    FORMAT(CAST(Upvotes AS FLOAT) / CAST(TotalVotes AS FLOAT), @percentFmtStr) AS [Upvotes as a percentage of daily votes],
    FORMAT(CAST(Downvotes AS FLOAT) / CAST(TotalVotes AS FLOAT), @percentFmtStr) AS [Downvotes as a percentage of daily votes]
FROM
    #Results
WHERE
    PostTypeId = 2
ORDER BY DaysSincePosting;

-- Answers Ordered By Percent of Daily Downvotes
SELECT
    DaysSincePosting,
    FORMAT(Upvotes, @commaSepFmtStr) AS [Answer Upvotes],
    FORMAT(Downvotes, @commaSepFmtStr) AS [Answer Downvotes],
    FORMAT(TotalVotes, @commaSepFmtStr) AS [Answer Votes],
    FORMAT(CAST(Upvotes AS FLOAT) / CAST(TotalVotes AS FLOAT), @percentFmtStr) AS [Upvotes as a percentage of daily votes],
    FORMAT(CAST(Downvotes AS FLOAT) / CAST(TotalVotes AS FLOAT), @percentFmtStr) AS [Downvotes as a percentage of daily votes]
FROM
    #Results
WHERE
    PostTypeId = 2
ORDER BY CAST(Downvotes AS FLOAT) / CAST(TotalVotes AS FLOAT) DESC;

-- Answers Ordered By Percent of Daily Upvotes
SELECT
    DaysSincePosting,
    FORMAT(Upvotes, @commaSepFmtStr) AS [Answer Upvotes],
    FORMAT(Downvotes, @commaSepFmtStr) AS [Answer Downvotes],
    FORMAT(TotalVotes, @commaSepFmtStr) AS [Answer Votes],
    FORMAT(CAST(Upvotes AS FLOAT) / CAST(TotalVotes AS FLOAT), @percentFmtStr) AS [Upvotes as a percentage of daily votes],
    FORMAT(CAST(Downvotes AS FLOAT) / CAST(TotalVotes AS FLOAT), @percentFmtStr) AS [Downvotes as a percentage of daily votes]
FROM
    #Results
WHERE
    PostTypeId = 2
ORDER BY CAST(Upvotes AS FLOAT) / CAST(TotalVotes AS FLOAT) DESC;

fork query

edited jun 9

created jun 9 Henry.Ecker