0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people looking to improve their interpersonal communication skills

WITH f AS (SELECT
  OwnerUserId AS u,
  DATEDIFF(day, CreationDate, GETDATE()) / 28 AS age
  FROM Posts Where PostTypeId = 2
),
g AS (SELECT u, age, count(*) c FROM f GROUP BY u, age),

h AS (SELECT age, COUNT(DISTINCT u) AS du FROM g GROUP BY age)

SELECT 
DATEADD( day, -age*28, GETDATE() ) AS [date],
h.du AS [Number of distinct users who ...posted  answers in each 28-day period],
(SELECT COUNT(DISTINCT u) FROM g WHERE g.age=h.age AND c>=2)  AS [...posted 2 answers or more in the period],
(SELECT COUNT(DISTINCT u) FROM g WHERE g.age=h.age AND c>=5)  AS [...posted 5 answers or more in the period],
(SELECT COUNT(DISTINCT u) FROM g WHERE g.age=h.age AND c>=10) AS [...posted 10 answers or more in the period]

FROM h
ORDER BY [date]

fork query

edited jan 3 19

created jan 3 19 anonymous