New users and questions per year, with questions per user in first user's year

1

Please login or register to vote for this query.

(click on this box to dismiss)

Considering only users with questions

Physical Fitness Meta

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

SELECT
    YEAR(u.CreationDate) AS u_year
  , COUNT(1) AS u_count
  , SUM(p.p_count) AS p_count
  , CONVERT(decimal(6,3),CONVERT(decimal,SUM(p.p_count))/CONVERT(decimal,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

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results