0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for developers and users of Stellar and the Stellar Distributed Exchange

Select myuser.Id, 
share.Ans_posted,share.Ans_num,share.AnsLen,
Edit.Edited,
Edit.Num_edits,
commentLength.Num_com
FROM
(
SELECT u.Id FROM Users u WHERE u.Id = ##UserId##
) as myuser
LEFT JOIN(
  SELECT p.OwnerUserId, 
  IIF(count(p.Id)>0, 1, 0) AS [Ans_posted], 
  count(p.Id) AS [Ans_num],  
  AVG(LEN(p.Body)) AS AnsLen
  FROM Posts as p 
  WHERE p.OwnerUserId = ##UserId##
  AND p.PostTypeId =2
  AND p.CreationDate BETWEEN '##Date?2021-10-01##' AND DATEADD(week,2,'##Date?2021-10-01##') 
  GROUP BY p.OwnerUserId) as share ON myuser.id = share.OwnerUserId
LEFT JOIN (
  SELECT ph.UserId, IIF(count(ph.Id)>0, 1, 0) AS [Edited], count(ph.Id) AS Num_edits
  FROM PostHistory as ph
  INNER JOIN Posts p on ph.PostId = p.Id
  WHERE p.OwnerUserId = ##UserId## AND ph.UserId = ##UserId##
  AND ph.CreationDate BETWEEN '##Date?2021-10-01##' AND DATEADD(week,2,'##Date?2021-10-01##')
  AND p.PostTypeId = 2
  AND ph.PostHistoryTypeId = 5
  GROUP BY ph.UserId
) as Edit ON myuser.id = Edit.UserId
LEFT JOIN (
  SELECT c.UserId, count(c.Id) AS Num_com
  FROM Comments as c
  INNER JOIN Posts p on c.PostId = p.Id
  WHERE p.OwnerUserId = ##UserId## AND c.UserId = ##UserId##
  AND p.PostTypeId = 2
  AND c.CreationDate BETWEEN '##Date?2021-10-01##' AND DATEADD(week,2,'##Date?2021-10-01##') 
  GROUP BY c.UserId
) as commentLength ON myuser.id = commentLength.UserId

fork query

created jan 27 bwhoslkw