101. Average number of visits per user

easy google

  • ****************************************************************************************
  • **************************************

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;


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

More Google questions

ID Title Level FTPR
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
105 Number of days gap between last two actions google hard
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