Questions with first answer in October (Selecting top 5 of dominant tag incl. Bounty)

0

Please login or register to vote for this query.

(click on this box to dismiss)

LEGO® Answers

Q&A for LEGO® and building block enthusiasts

SELECT
  a.*
FROM (
  SELECT 
    DATEPART(YEAR, q.CreationDate) AS QYear,
    DATEPART(MONTH, q.CreationDate) AS QMonth,
    q.Id AS QID,
    q.OwnerUserId AS QUserID,
    q.Title AS QTitle,
    q.AnswerCount AS QAnswerCount,
    q.ViewCount AS QViewCount,
    q.CommentCount AS QCommentCount,
    q.FavoriteCount AS QFavoriteCount,
    q.Tags AS QTags,
    q.Score AS QScore,
    a.OwnerUserId AS AUserID,
    u.Reputation AS AUserReputation,
    u.UpVotes AS AUserUpVotes,
    u.DownVotes AS AUserDownVotes,
    a.Id AS AID,
    aa.Id AS AAID,
    CASE WHEN aa.Id IS NULL THEN 0 ELSE IIF(a.Id = aa.Id, 1, 0) END AS FirstAisAA,
    a.Score AS AScore,
    aa.Score AS AAScore,
    q.CreationDate AS QCreationDate,
    a.CreationDate AS ACreationDate,
    aa.CreationDate AS AACreationDate,
    DATEDIFF(MINUTE, q.CreationDate, a.CreationDate) AS QTime2Answer,
    CASE WHEN aa.CreationDate IS NULL THEN NULL ELSE DATEDIFF(MINUTE, a.CreationDate, aa.CreationDate) END AS QTime2AccAnswer,
    DATEDIFF(HOUR, q.CreationDate, q.LastActivityDate) AS QActiveTime,
    t.TagName AS QTagName,
    t.Count AS QTagCount,
    CASE WHEN v.PostId IS NULL THEN 0 ELSE 1 END AS QBounty,
    qh.CreationDate AS QBCreationDate,
    v.BountyAmount AS QBAmount,
    CASE WHEN qh.CreationDate IS NULL THEN NULL ELSE DATEDIFF(MINUTE, qh.CreationDate, a.CreationDate) END AS QTimeBounty2Answer,
    row_number() over (partition by q.Id order by a.CreationDate asc, qh.CreationDate asc, t.Count desc) AS seq
  FROM
    Posts q
    JOIN
    Posts a
    ON (q.Id = a.ParentId)
    LEFT JOIN
    Posts aa
    ON (q.AcceptedAnswerId = aa.Id)
    LEFT JOIN
    PostTags pt
    ON (q.Id = pt.PostId)
    JOIN
    (SELECT TOP 5 * FROM Tags ORDER BY Count desc) t
    ON (pt.TagId = t.Id)
    LEFT JOIN
    Votes v 
    ON (q.Id = v.PostId)
    LEFT JOIN 
    PostHistory qh
    ON (v.PostId = qh.PostId)
    LEFT JOIN Users u
    ON (a.OwnerUserId = u.Id) 
  WHERE
    q.PostTypeId = 1 AND
    q.ClosedDate IS NULL AND
    (v.voteTypeId is NULL OR 
      (v.VoteTypeId = 8 AND -- bounty start
       qh.PostHistoryTypeId = 33 AND -- post notice added
       v.CreationDate = cast(qh.CreationDate as date)
    ))
) a
WHERE 
 a.ACreationDate >= '2017-10-01 00:00:00.0' AND
 a.seq = 1
ORDER BY NEWID()

Enter Parameters

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