- ******************************************************************************************2021-08-01**********2021-08-02************************************************************************************************************************************
- **************************************************************************
- ************************************************************************************
Table: session_mobile
Every time a user opens the mobile app (iOS, Android), a new session starts, it ends when the user leaves the app.
col_name | col_type -----------------+--------------------- date | date user_id | bigint session_id | bigint event | varchar(20)
Sample results
user_id --------- 8000001 8000002 8000003 8000004 8000005 8000006
Solution postgres
WITH two_continuous_visits AS (
SELECT user_id, date, LAG(date, 1) OVER(PARTITION BY user_id ORDER BY date) last_visit_date
FROM session_mobile
WHERE event NOT IN ('enter', 'exit')
AND date >= '2021-08-01'
AND date <= '2021-08-31'
)
SELECT user_id
FROM two_continuous_visits
WHERE date IS NOT NULL
AND (date - last_visit_date) = 1
GROUP BY user_id
ORDER BY 1;
Explanation
This query is looking for users who had two continuous visits to the mobile app in August 2021.
The query first creates a temporary table called "two_continuous_visits" using a window function that finds the last visit date for each user_id. It only selects events that are not 'enter' or 'exit' and fall within the date range of August 2021.
Then, the query selects user_ids from the temporary table where the date is not null and the difference between the current visit date and the last visit date is exactly one day (i.e. two continuous visits). It groups the results by user_id and orders them by ascending numerical order.
Expected results