Post Islands for users

0

Please login or register to vote for this query.

(click on this box to dismiss)

Genealogy and Family History Meta

Q&A about the site for expert genealogists and people interested in genealogy or family history

--Calculates the number of consecutive days a user has a post
--Try it with 22656 

WITH distinct_days 
     AS (SELECT DISTINCT DateAdd(day, datediff(day,0, creationdate), 0) post_day 
         FROM   posts 
         WHERE  owneruserid = ##userid##), 
     islands 
     AS (SELECT post_day, 
                Datediff(DAY, 0, post_day) - 
                Dense_rank() OVER (ORDER BY Datediff(DAY 
                , 0 
                , 
                post_day)) AS g 
         FROM   distinct_days) 
SELECT MIN(post_day)   start_day, 
       MAX(post_day)   end_day, 
       COUNT(DISTINCT post_day) daycount, 
       MIN(p.id) [Post Link], 
       MAX(p.id) [Post Link]
FROM   islands i 
       INNER JOIN posts p 
         ON p.owneruserid = ##userid## 
            AND i.post_day = DateAdd(day, datediff(day,0, p.creationdate), 0)
GROUP  BY g 
ORDER  BY g

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results