105. Number of days gap between last two actions

hard google

  • ************************************************************************************************
  • *****************************************************************************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;


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

More Google questions

ID Title Level FTPR
101 Average number of visits per user google easy
102 Histogram by visit session duration google hard
103 Median and average session duration by day of the week google medium
104 MAU: Monthly active users google easy
106 Mobile vs. web google medium
107 Customer count by platform google medium
147 2 days streak customers google hard
148 Most popular video category google medium
149 fans by video category google easy
150 Countries with above average customers google easy
186 Average comments per question google easy
187 Comment distribution at Google Forum google easy
188 Top 1 popular question by department google hard