1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for users, experts, and developers of the Tezos blockchain project

WITH Raw AS (
  SELECT
    DATEFROMPARTS(
      YEAR(Votes.CreationDate),
      MONTH(Votes.CreationDate),
      1) AS Month,
    COUNT(*) AS Count
  FROM PostsWithDeleted, Votes
  WHERE
    Votes.PostId = PostsWithDeleted.Id
    AND
    Votes.VoteTypeId = 2
    AND
    PostsWithDeleted.PostTypeId = 2
  GROUP BY
    DATEFROMPARTS(
      YEAR(Votes.CreationDate),
      MONTH(Votes.CreationDate),
      1)
),
Raw2 AS (
  SELECT
    DATEFROMPARTS(
      YEAR(CreationDate),
      MONTH(CreationDate),
      1) AS Month,
    COUNT(*) AS Count
  FROM PostsWithDeleted
  WHERE PostTypeId = 2
  GROUP BY
    DATEFROMPARTS(
      YEAR(CreationDate),
      MONTH(CreationDate),
      1)
),
Raw3 AS (
  SELECT
    Month,
    SUM(Count) OVER
        (ORDER BY Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
      AS Count
  FROM Raw2
)
SELECT
  Raw.Month,
  1.0 * Raw.Count / Raw3.Count
FROM Raw, Raw3
WHERE
  Raw.Month = Raw3.Month
ORDER BY Raw.Month

fork query

edited oct 21

created oct 21 peterh - PLEASE DELETE THIS ACCOUNT