Unanswered questions asked by experienced users

0

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

;with first_cte as (
  SELECT q.Id, q.CreationDate AS QuestionCreationDate
  FROM Posts AS q
	INNER JOIN Users qu ON qu.Id = q.OwnerUserId
   where q.CreationDate >= '20200101'
    and q.PostTypeId = 1
	and qu.Reputation >= 200
    and NOT EXISTS (SELECT 1 FROM Posts a where a.ParentId = q.Id)
  GROUP BY q.Id, q.CreationDate
),
ym_cte as (
  SELECT YEAR(q.QuestionCreationDate) * 100 + MONTH(q.QuestionCreationDate) AS YM,
    q.Id, q.QuestionCreationDate
  FROM first_cte AS q),
u_cte as (
  SELECT YEAR(u.CreationDate) * 100 + MONTH(u.CreationDate) AS YM,
  COUNT(1) UCount
  FROM Users AS u
  WHERE u.Reputation > 200
  GROUP BY YEAR(u.CreationDate) * 100 + MONTH(u.CreationDate)
),
q_cte as (
  SELECT YM, COUNT(1) / 10 AS QCount
  FROM ym_cte as q
  GROUP BY YM
 )
SELECT q.YM, QCount, UCount
FROM q_cte as q 
  JOIN u_cte as u on u.YM = q.YM
ORDER BY q.YM

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results