- ****************************************************************************************
**************************************
Table 1: dates
Calendar dates from 01/01/2019 to 12/31/2025.
col_name | col_type ----------+---------- year | smallint month | smallint date | date
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
date | avg_session_per_user
------------+--------------
2021-01-01 | 1.51
2021-01-02 | 1.23
Solution postgres
SELECT D.date, COUNT(DISTINCT session_id) * 1.0 /COUNT(DISTINCT user_id) AS avg_session_per_user
FROM dates D
LEFT JOIN session_web W
ON D.date = W.date
WHERE D.date >= '2021-08-01'
AND D.date <= '2021-08-31'
GROUP BY D.date;
Explanation
This query retrieves the average number of sessions per user for each day in August 2021.
The SELECT statement specifies that we want to display the date and the calculated average number of sessions per user. The calculation is performed by dividing the count of distinct session IDs by the count of distinct user IDs, and multiplying the result by 1.0 to ensure that decimal values are returned.
The FROM clause indicates that we are using a table called "dates" to retrieve the date values.
The LEFT JOIN clause is used to combine the "dates" table with another table called "session_web" based on the date column. This allows us to retrieve information about sessions that occurred on each date.
The WHERE clause limits the results to only include dates in August 2021.
Finally, the GROUP BY clause groups the results by date so that we can calculate the average number of sessions per user for each day.
Expected results