Prompt upvote/feedback ratio

1

Please login or register to vote for this query.

(click on this box to dismiss)

Drupal Answers

Q&A for Drupal developers and administrators

WITH Raw AS (
  SELECT
    DATEFROMPARTS(
      YEAR(Votes.CreationDate),
      MONTH(Votes.CreationDate),
      1) AS Month,
    CASE
      WHEN
        DATEDIFF(d, Posts.CreationDate, Votes.CreationDate)
          > ##PromptDay:int?30##
      THEN 1
      ELSE 0
    END AS Prompt,
    COUNT(*) AS Count,
    'v' AS Type
  FROM Votes, Posts
  WHERE
    Votes.PostId = Posts.Id
    AND
    Votes.VoteTypeId = 2
  GROUP BY
    DATEFROMPARTS(
      YEAR(Votes.CreationDate),
      MONTH(Votes.CreationDate),
      1),
    CASE
      WHEN
        DATEDIFF(d, Posts.CreationDate, Votes.CreationDate)
          > ##PromptDay:int?30##
      THEN 1
      ELSE 0
    END

  UNION

  SELECT
    DATEFROMPARTS(
      YEAR(PostFeedback.CreationDate),
      MONTH(PostFeedback.CreationDate),
      1) AS Month,
    CASE
      WHEN
        DATEDIFF(d, Posts.CreationDate, PostFeedback.CreationDate)
          > ##PromptDay:int?30##
      THEN 1
      ELSE 0
    END AS Prompt,
    COUNT(*) AS Count,
    'f' AS Type
  FROM PostFeedback, Posts
  WHERE
    PostFeedback.PostId = Posts.Id
    AND
    PostFeedback.VoteTypeId = 2
  GROUP BY
    DATEFROMPARTS(
      YEAR(PostFeedback.CreationDate),
      MONTH(PostFeedback.CreationDate),
      1),
    CASE
      WHEN
        DATEDIFF(d, Posts.CreationDate, PostFeedback.CreationDate)
          > ##PromptDay:int?30##
      THEN 1
      ELSE 0
    END
  
)
SELECT
  Month,
  CASE WHEN Prompt = 1 THEN 'Prompt' ELSE 'Delayed' END
  + ' ' +
  CASE WHEN Type='v' THEN 'Upvote' ELSE 'Feedback' END AS Type,
  Count  
FROM Raw
ORDER BY Month DESC, Prompt, Raw.Type;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results