Consecutive Logins
You want to understand how often users log in to your company’s website. You're given a table named user_activity_log with the following columns:
user_id(INT) - Unique identifier for each user.timestamp(DATETIME) - The exact time the user performed an activity.activity_type(VARCHAR) - The type of activity the user has performed. The only two types of activities areLOGINandLOGOUT.
Write a SQL query that determines the time elapsed (in minutes) between consecutive logins for each user. The result should show each user and the gap between their logins. Your output should contain the following columns: user_id, current_login, previous_login, minutes_elapsed. Round to the nearest minute
First, we utilize a CTE named ConsecutiveLogins to filter login actions from the user_activity_log table. Within this CTE, for each user's login, we use the LAG() function to retrieve the timestamp of their previous login, ensuring the comparison is in chronological order for each user.
In the main query, we extract the user ID, current login, and previous login timestamps. To calculate the time difference between consecutive logins in minutes, we leverage SQLite's julianday function. By subtracting and converting, we find the elapsed minutes between these logins.
Lastly, we filter out entries without a prior login by excluding rows where previous_login is null, ensuring our results only showcase genuine consecutive login actions.
SQL-- SQLite (used by code editor)
WITH ConsecutiveLogins AS (
SELECT
user_id,
timestamp AS current_login,
LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) AS previous_login
FROM
user_activity_log
WHERE
activity_type = 'LOGIN'
)
SELECT
user_id,
current_login,
previous_login,
ROUND((julianday(current_login) - julianday(previous_login)) * 24 * 60) AS minutes_elapsed
FROM
ConsecutiveLogins
WHERE
previous_login IS NOT NULL;
-- Postgresql
WITH ConsecutiveLogins AS (
SELECT
user_id,
timestamp AS current_login,
LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) AS previous_login
FROM
user_activity_log
WHERE
activity_type = 'LOGIN'
)
SELECT
user_id,
current_login,
previous_login,
ROUND(EXTRACT(EPOCH FROM (current_login - previous_login))/60) AS minutes_elapsed
FROM
ConsecutiveLogins
WHERE
previous_login IS NOT NULL;
-- MySQL
WITH ConsecutiveLogins AS (
SELECT
user_id,
timestamp AS current_login,
LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) AS previous_login
FROM
user_activity_log
WHERE
activity_type = 'LOGIN'
)
SELECT
user_id,
current_login,
previous_login,
ROUND(TIMESTAMPDIFF(MINUTE, previous_login, current_login)) AS minutes_elapsed
FROM
ConsecutiveLogins
WHERE
previous_login IS NOT NULL;
with login_data as ( select * from user_activity_log where activity_type = 'LOGIN' ) ,cte as ( select user_id, timestamp as current_login ,lag(timestamp,1,timestamp) over (partition by user_id order by timestamp asc) as previous_login, round((julianday(timestamp) - julianday(lag(timestamp,1,timestamp) over (partition by user_id order by timestamp asc))) * 24 * 60) as minutes_elapsed from login_data ) select * from cte where current_login <> previous_login;