0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

WITH Questions AS
(
SELECT TOP 100 *, 
ViewCount/POWER(CAST(DATEDIFF(DAY,CreationDate,GETDATE()) AS FLOAT),10) AS ViewsPerDay
FROM Posts
WHERE PostTypeId = 1 AND ClosedDate IS NULL
)
SELECT TOP 100 Q.Id AS [Post Link], 
               ViewsPerDay/NULLIF(VotesPerDay,0) AS Weight
FROM Questions Q
CROSS APPLY (
SELECT TOP 1 CAST(A.Score AS FLOAT)/DATEDIFF(DAY,A.CreationDate,GETDATE())
FROM Posts A 
WHERE A.ParentId = Q.Id
ORDER BY CAST(A.Score AS FLOAT)/DATEDIFF(DAY,A.CreationDate,GETDATE()) DESC) 
CA(VotesPerDay)
ORDER BY ViewsPerDay/NULLIF(VotesPerDay,0) DESC

fork query

edited jul 11 13

created jul 11 13 smithmartin