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 are LOGIN and LOGOUT.

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;