- ************************************************************************************************
- *****************************************************************************1
- *********************************************************0
- *****************************************************************
- *****************************************************************
Table: session_web
Every time a user visits the website, a new session starts, it ends when the user leaves the site.
col_name | col_type -----------------+--------------------- date | date user_id | bigint session_id | bigint event | varchar(20)
Sample results
user_id | delta --------------+-------- 8000001 | 1
Solution postgres
WITH ordered_actions AS (
SELECT user_id, date, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date DESC) AS nth_action
FROM session_web
WHERE event NOT IN ('enter', 'exit')
),
last_action AS (
SELECT user_id, date, nth_action
FROM ordered_actions
WHERE nth_action = 1
),
second_last_action AS (
SELECT user_id, date, nth_action
FROM ordered_actions
WHERE nth_action = 2
)
SELECT L.user_id, L.date - S.date AS days_gap
FROM last_action L
INNER JOIN second_last_action S
ON S.user_id = L.user_id;
Explanation
This query selects data from a table called session_web. The table contains data about user sessions on a website. The query first creates a temporary table called ordered_actions that orders the user sessions by date and assigns a row number to each session based on the user_id. The next two temporary tables, last_action and second_last_action, extract the date and row number of the last and second-to-last sessions for each user. Finally, the query joins the last_action and second_last_action tables and calculates the number of days between the two sessions. This information can be used to analyze user behavior on the website, such as how long users typically wait between sessions.
Expected results