- **************************************************************************************
- ************
*********************************************************************************************
Table 1: 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)
Table 2: 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
year | mon | mau -----------+--------+-------- 2021 | 7 | 123 2021 | 8 | 234
Solution postgres
WITH session AS (
SELECT * FROM session_web
WHERE event NOT IN ('enter', 'exit')
UNION ALL
SELECT * FROM session_mobile
WHERE event NOT IN ('enter', 'exit')
)
SELECT
EXTRACT(YEAR FROM date) AS year,
EXTRACT(MONTH FROM date) AS mon,
COUNT(DISTINCT user_id) AS mau
FROM session
GROUP BY 1,2;
Explanation
This query is selecting data from two tables, session_web and session_mobile, and combining them into one table called "session". The data being selected is for events that are not "enter" or "exit".
Then, the query is extracting the year and month from the "date" column and counting the number of unique user IDs for each month and year. This count represents the monthly active users (MAU) for that particular month and year.
Finally, the results are grouped by year and month, so that you can see the monthly active user count for each month and year.
Expected results