1

Please login or register to vote for this query.

(click on this box to dismiss)

Considering only users with questions

Q&A for professional and enthusiast programmers

SELECT
    YEAR(u.CreationDate) AS u_year
  , COUNT(1) AS u_count
  , SUM(p.p_count) AS p_count
  , CONVERT(decimal(6,2),SUM(p.p_count)/COUNT(1)) AS ppu_avg
  , MAX(p.p_count) AS p_count_max
  , MIN(p.p_count) AS p_count_min
FROM
    Users u
INNER JOIN
  ( SELECT
        p.OwnerUserId
      , YEAR(p.CreationDate) AS p_year
      , COUNT(1) AS p_count
    FROM
        Posts p
    WHERE
        p.PostTypeId = 1
    GROUP BY
        p.OwnerUserId
      , YEAR(p.CreationDate)
  ) p
ON
    p.OwnerUserId = u.Id
AND p.p_year = YEAR(u.CreationDate)
GROUP BY
    YEAR(u.CreationDate)
ORDER BY
    u_year

view current version

edited mar 1 19

created mar 1 19 fra-san