I've got a table with 3 fields: userID, url and a loged datetime of user action. I want to write a query in sql Redshift in Redash, which returns a list of user session (all actions which have less that an hour between them) with fields userID, start datetime and end datetime. What can I use to achieve this?
Asked
Active
Viewed 1,190 times
0
-
You can use sql. please have a try and post what you get. (This is not a code writing service). One approach will be to use window functions and search previous answers. – Jon Scott May 09 '18 at 16:21
2 Answers
0
My $0.02, I use the following query since it excludes "lost connections" and only shows ones with active sessions. It also shows the running query if there is one.
-- Who
SELECT s.user_name,
s.process,
s.starttime,
s.db_name,
c.remotehost,
si.starttime query_start_time,
si.text query
FROM stv_sessions s
LEFT JOIN stl_connection_log c
ON s.user_name = c.username AND s.process = c.pid AND c.event = 'authenticated'
LEFT JOIN stv_inflight si ON c.pid = si.pid
WHERE s.user_name <> 'rdsdb'
ORDER BY user_name, starttime;

debugme
- 1,041
- 1
- 10
- 22