0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

SELECT owneruserid, creationdate, next_creationdate, 
       datediff(day, next_creationdate, creationdate), rowNum
FROM (
     SELECT owneruserid, creationdate, 
	        LEAD(creationdate, 1, 0) OVER (
              PARTITION BY owneruserid
              ORDER BY creationdate
              ) as next_creationdate, 
            row_number() OVER (PARTITION BY owneruserid ORDER BY creationdate) as rowNum 
	 FROM  posts 
     ) as T
WHERE owneruserid > 0
ORDER BY owneruserid, creationdate;

/*
SELECT  DATEDIFF(day, pcreationdate, creationdate)
FROM    (
        SELECT  *,
                LAG(creationdate) OVER (ORDER BY creationdate) pDataDate
        FROM    rows
        ) q
WHERE   pcreationdate IS NOT NULL
*/

view current version

edited feb 22 20

created feb 22 20 anonymous