SQL Server LAG() function to calculate differences between rows

Admin 1
Viewed 12202

I'm new in SQL Server and I've got some doubts about the lag() function. I have to calculate the average distance (in days) between two user's activities. Then, I have to GROUP BY all the users, calculate all the date differences between rows for each user, and finally select the average of the group.

Just to be clear, I've got this kind of table: original table

First I have to filter days with activities (activities!=0). Then I have to create this:

calculate differences between rows

And finally, the expected outcome is this one:

what i want

I thought this could be a "kind of" code:

select userid, avg(diff)
  (SELECT *,DATEDIFF(day, Lag(dateid, 1) OVER(ORDER BY [Userid]), 
   dateid) as diff
   FROM table1
   where activities!=0
   group by userid) t
group by userid

Of course it doesn't work. I think I also have to do a while loop since rownumber changes for each users.

I hope you can help meeee! thank you very much

date-difference lag datediff sql-server sql
2 Answers

You are almost there. Just add partition by userid so the difference is calculated for each userid and order by dateid.

select userid, avg(diff)
  (SELECT t.*
         ,DATEDIFF(day, Lag(dateid, 1) OVER(PARTITION BY [Userid] ORDER BY [dateid]),dateid) as diff
   FROM table1 t
   where wager!=0
  ) t
group by userid
user_34318690

You don't need lag() at all. The average is the maximum minus the minimum divided by one less than the count:

SELECT userid,
       DATEDIFF(day, MIN(dateid), MAX(dateid)) * 1.0 / NULLIF(COUNT(*), 1) as avg_diff
FROM table1
WHERE wager<> 0
GROUP BY userid;
user_11440350
Related