SHARE
TWEET

Untitled

a guest Jun 16th, 2019 60 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH START AS (SELECT user_id, TIMESTAMP, ROW_NUMBER() OVER(partition BY user_id ORDER BY TIMESTAMP DESC) AS INDEX
  2. FROM session_start
  3. ORDER BY user_id, INDEX)
  4.  
  5. WITH END AS (SELECT user_id, TIMESTAMP, ROW_NUMBER() OVER(partition BY user_id ORDER BY TIMESTAMP DESC) AS INDEX
  6. FROM session_end
  7. ORDER BY user_id, INDEX)
  8.  
  9. WITH sessions AS SELECT START.user_id AS user_id, START.INDEX AS INDEX, datediff(SECOND, START.TIMESTAMP, END.TIMESTAMP) AS duration
  10. FROM START INNER JOIN END ON START.user_id = END.user_id AND START.INDEX = END.INDEX
  11.  
  12. SELECT user_id, avg(duration) AS avg_seconds
  13. FROM sessions
  14. GROUP BY user_id
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top