0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

Select a.Id, a.CreationDate, a.DisplayName, a.Title, a.Tags, a.Link, b.UpMod as Upvote, b.DownMod as DownVote, b.AcceptedByOriginator as Accepted
From 
(Select Distinct a.Id, a.CreationDate, u.DisplayName, p.Title, p.Tags, 
   CONCAT('http://stackoverflow.com/questions/', p.Id, '#answer-', a.Id) as Link
   --,vu.DisplayName
From Posts a 
   Inner Join Posts p On a.ParentId = p.Id 
   Inner Join Users u On a.OwnerUserId = u.Id
   Inner Join PostTags pt on p.Id = Pt.PostId
   Inner Join Tags t on pt.TagId = t.Id
   --Left Join Votes v on a.Id = v.PostId and v.VoteTypeId = 2 
   --Left Join Users vu on v.UserId = vu.Id
Where a.OwnerUserId in (
--------TIER 2----------
      --9652074,  --Saps
      --9652169,  --Nelson
      --8573097,  --Rafon
      --8566739,  --Henri
      --8579073,  --James
--------TIER 1----------
      9455567,  --Xer
      9460075,  --Micah
      9460321,  --Mervin
      9461114,  --Tom
      5993323   --Ricky
  )
  And a.PostTypeId = 2  -- Answer
  And p.PostTypeId = 1  -- Question
  And t.TagName in ('google-maps-api-3','google-maps', 'google-maps-api',
  'google-places-api', 'google-static-maps', 'google-street-view',
  'google-geocoding-api', 'google-direction', 'google-distancematrix-api',
  'google-maps-android-api-2', 'google-maps-sdk-ios', 'google-maps-embed',
  'google-maps-mobile', 'google-geolocation', 'google-maps-timezone',
  'google-elevation-api', 'google-roads-api')
  --And a.CreationDate >= DATEADD(Day, -30, Getdate())  -- 30 days answers 
  ) a Left Join (
  SELECT *
FROM (
  SELECT v.PostID as Id, vt.Name, COUNT(*) AS CNT
     FROM 
     Posts p
       INNER JOIN Votes v
         ON v.PostId = p.Id
       INNER JOIN VoteTypes vt
         ON v.VoteTypeId = vt.Id
     WHERE
     p.OwnerUserId in
     (
--------TIER 2----------
      --9652074,  --Saps
      --9652169,  --Nelson
      --8573097,  --Rafon
      --8566739,  --Henri
      --8579073,  --James
--------TIER 1----------
      9455567,  --Xer
      9460075,  --Micah
      9460321,  --Mervin
      9461114,  --Tom
      5993323   --Ricky
      ) 
  GROUP BY 
  vt.Name, v.PostID) s_tab pivot (min(CNT)for [Name] in ([UpMod], [DownMod],[AcceptedByOriginator]))AS PVT
  ) b on a.Id = b.Id
  Order by a.DisplayName, a.CreationDate desc

view current version

edited apr 24 18

created apr 13 18 tomjosef