3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for professional robotic engineers, hobbyists, researchers and students

DECLARE @tag NVARCHAR(25) = ##Tag:string?-##, @filterByTag BIT;

CREATE TABLE #QualifiedPosts (
  Id int
);

SELECT
@filterByTag=CASE @tag WHEN '-' THEN 0 ELSE 1 END;
PRINT(@filterByTag);

INSERT INTO #QualifiedPosts
SELECT 
  p.Id
FROM Posts p
JOIN (
  SELECT
    pt.PostId
  FROM PostTags pt
  JOIN Tags t
  ON t.Id = pt.TagId AND
    (@filterByTag = 0 OR t.TagName = @tag)
  GROUP BY pt.PostId
  ) AS FilteredTags ON FilteredTags.PostId = p.Id
WHERE PostTypeId = 1;

INSERT INTO #QualifiedPosts
SELECT
  p.Id
FROM Posts p
JOIN (
  SELECT
    pt.PostId
  FROM PostTags pt
  JOIN Tags t
  ON t.Id = pt.TagId AND
    (@filterByTag = 0 OR t.TagName = @tag)
  GROUP BY pt.PostId
  ) AS FilteredTags ON FilteredTags.PostId = p.ParentId
WHERE PostTypeId = 2;

-- Used only as x-axis datapoints
WITH postcount AS (
  SELECT 
    dateadd(day, datediff(day, 0, p.CreationDate), 0) AS datetime,
    COUNT(*) AS total
  FROM Posts p
  WHERE PostTypeId IN (1,2)
  GROUP BY dateadd(day, datediff(day, 0, p.CreationDate), 0)
),
qcount AS (
  SELECT 
    dateadd(day, datediff(day, 0, p.CreationDate), 0) AS datetime,
    COUNT(*) AS total
  FROM Posts p
  JOIN #QualifiedPosts qp ON qp.Id = p.Id
  WHERE PostTypeId = 1
  GROUP BY dateadd(day, datediff(day, 0, p.CreationDate), 0)
),
acount AS (
  SELECT 
    dateadd(day, datediff(day, 0, p.CreationDate), 0) AS datetime,
    COUNT(*) AS total
  FROM Posts p
  JOIN #QualifiedPosts qp ON qp.Id = p.Id
  WHERE PostTypeId = 2
  GROUP BY dateadd(day, datediff(day, 0, p.CreationDate), 0)
),
usercount AS (
  SELECT 
    dateadd(day, datediff(day, 0, CreationDate), 0) AS datetime,
    COUNT(*) AS total
  FROM Users
  GROUP BY dateadd(day, datediff(day, 0, CreationDate), 0)
),
votecount AS (
  SELECT 
    dateadd(day, datediff(day, 0, v.CreationDate), 0) AS datetime,
    COUNT(*) AS total
  FROM Votes v
  JOIN #QualifiedPosts qp ON qp.Id = v.PostId
  WHERE v.VoteTypeId IN (1,2,3)
  GROUP BY dateadd(day, datediff(day, 0, v.CreationDate), 0)
),
stats AS (SELECT 
  p.datetime AS 'date',
  ISNULL(v.total, 0) AS 'votes per day',
  ISNULL(q.total, 0) AS 'questions per day',
  ISNULL(a.total, 0) AS 'answers per day',
  ISNULL(p.total, 0) AS 'posts per day'
FROM
  postcount p 
  FULL OUTER JOIN usercount u on u.datetime = p.datetime 
  FULL OUTER JOIN votecount v on v.datetime = p.datetime
  FULL OUTER JOIN qcount q on q.datetime = p.datetime
  FULL OUTER JOIN acount a on a.datetime = p.datetime
  )
  
SELECT
  date,
  [votes per day],
  [questions per day],
  [answers per day]
FROM stats
WHERE
  stats.date >= ##StartDate:string?2014-12-16##
ORDER BY stats.date

fork query

edited feb 19 15 entotto

created dec 17 14 jmac