0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for users of the Magento e-Commerce platform

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,
    row_number() over (partition by q.Id order by a.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)
  WHERE
    q.PostTypeId = 1 AND
    a.CreationDate >= '2017-10-01 00:00:00.0' AND
    q.ClosedDate IS NULL
) a
WHERE 
 a.seq = 1
ORDER BY NEWID()

view current version

edited oct 16 17

created oct 16 17 anonymous