3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- Highest view rate questions in a particular tag (non-Community Wiki).
--
-- Highest hitrate non-CW questions, (presumably) what questions are
-- viewed the most per time unit from Google searches.
--


SELECT TOP 100
      p.Id as [Post Link],
      p.Id as [Post ID],
      Score as [Votes],
      ViewCount as [Views],
      DATEDIFF(day, p.CreationDate, CURRENT_TIMESTAMP) as Age,
      p.ViewCount / (DATEDIFF(day, p.CreationDate, CURRENT_TIMESTAMP) + 1) as [View rate],
      p.Tags as [Tags]

FROM  Posts p

-- Note: The tags in the .Tags field are enclosed in angle 
--       brackets: Example: 
--
--         <.net><.net-core><class-library><.net-standard>

-- User input (associating the identifier with the actual user prompt):
--   SingleTag: Only results with tag (only one tag allowed. Example: '.net') 

WHERE p.ViewCount > 28000 AND  -- On Stack Overflow there are 100 non-CW posts with more than about 28000.
      p.CommunityOwnedDate is null AND  -- Field CommunityOwnedDate is absent for non-CW posts.
      p.ViewCount / (DATEDIFF(day, p.CreationDate, CURRENT_TIMESTAMP) + 1) > 5 AND
      -- p.Tags LIKE '%<.net-core>%'  -- '%<java>%' also matches "'javascript'" (after "p.Tags" ): 'LIKE %java%'. Does not work for exact match: "COLLATE SQL_Latin1_General_CP1_CS_AS"
      p.Tags LIKE '%<' + ##SingleTag:string## + '>%'  
      
      -- ViewCount / age >
      
ORDER BY [View Rate] DESC

fork query

edited mar 26 22

created jan 31 14 Peter Mortensen (resurrected)