'Is the logic and the implementation of this query correct?
So i have a table that logs users using certain apps. It has the fields below
id
time
app
user
server
type (can be IN or OUT)
So a user starts using a certain app and when they get the right to use the app a row is logged and the type column is set to 'OUT'
Similarly, when they stop using the app, another row gets logged and now the type column is set to 'IN'
So i have to build a session table which shows the time between an app being used by a certain user & server up until when the user stops using it. It has the columns
id
outTime
inTime
app
user
server
So i made the following query
WITH cte AS
(
SELECT
*,
LEAD(time) OVER
(
PARTITION BY
app,
username,
server
ORDER BY time
) AS NextTime
FROM licenses
)
SELECT
time AS outTime,
NextTime AS inTime,
app,
username,
server
FROM cte
WHERE type = 'OUT'
The timestamp is down to seconds. The idea is to select a row where the user starts using a specific app from a specific server and save this timestamp as OutTime. After that it searches for the next row (time) with the same user, server, app - which would be the time where the user stops using the app.
Is this logic and query right or are there any flaws in it?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
