0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people who want to be financially literate

SELECT TOP 5 PERCENT 
  a.*
FROM (
  SELECT 
    DATEPART(YEAR, q.CreationDate) AS QYear,
    DATEPART(MONTH, q.CreationDate) AS QMonth,
    q.Id AS QID,
    q.OwnerUserId AS QUserID,
    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,
    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, q.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,
    v.CreationDate AS QBCreationDate,
    v.BountyAmount AS QBAmount,
    row_number() over (partition by q.Id order by a.CreationDate asc, v.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)
  WHERE
    q.PostTypeId = 1 AND
    q.ClosedDate IS NULL AND
    a.CreationDate >= '2017-10-01 00:00:00.0' AND
    v.VoteTypeId = 8 -- bounty start
) a
WHERE 
 a.seq = 1
ORDER BY NEWID()

view current version

edited oct 16 17

created oct 16 17 anonymous