Ctr ⮐

Pages Visited

We're still working at devils.fyi, a website similar to levels.fyi.

We've showed our investors how many users have uploaded their salary information. We also want to show them how many pages get viewed each day in 2022. For this, we have site analytics stored in the sessions table.


sessions
column_name type
id
UUID
pages_visited
INTEGER
referer
VARCHAR
started_at
DATE

A session represents a continuous sequence of pages visited by a viewer. If a user visits the home page, clicks a link to another page on the site, and then from that page clicks another link, they have visited 3 pages in their session so far. The session ends when they close the browser tab. The pages_visited column tells us how many pages the viewer visited in each session.

Investors want a way to measure the popularity of the site over time. They like to see a time series graph of how many sessions there were each day, as well as the number of pages visited each day.

Generate the growth report for your investors by selecting:

  • The date, started_at.
  • The count of the sessions each day (name that total_sessions).
  • The total number of pages visited each day (name that total_pages_visited).

Remember that we only want 2022 data for this report. Also, sort the result by the date in ascending order.

Note: I haven't taught you the aggregate function for summing numbers yet. You'll have to look it up yourself, perhaps using Google.

© 2022 Andrew Carlson. All rights reserved.

elephant typing on laptop in a cafe

Try running a query…

started_at
DATE 		total_sessions
BIGINT 		total_pages_visited
BIGINT
1
2022-01-01
4
17
2
2022-01-02
7
50
3
2022-01-03
8
43
4
2022-01-04
7
35
5
2022-01-05
5
30
6
2022-01-06
1
6
7
2022-01-07
8
30
8
2022-01-08
8
41
9
2022-01-09
6
25
10
2022-01-10
7
46
11
2022-01-11
5
19
12
2022-01-12
8
33
13
2022-01-13
5
27
14
2022-01-14
2
17
15
2022-01-15
9
56
16
2022-01-16
5
32
17
2022-01-17
6
33
18
2022-01-18
6
33
19
2022-01-19
4
29
20
2022-01-20
2
8
21
2022-01-21
9
43
22
2022-01-22
6
38
23
2022-01-23
6
22
24
2022-01-24
8
45
25
2022-01-25
10
61
26
2022-01-26
5
31
27
2022-01-27
7
32
28
2022-01-28
12
54
29
2022-01-29
8
43
30
2022-01-30
4
13
31
2022-01-31
11
52
32
2022-02-01
8
49
33
2022-02-02
2
12
34
2022-02-03
11
63
35
2022-02-04
9
46
36
2022-02-05
7
24
37
2022-02-06
11
57
38
2022-02-07
5
32
39
2022-02-08
4
13
40
2022-02-09
10
53
41
2022-02-10
5
33
42
2022-02-11
8
45
43
2022-02-12
3
13
44
2022-02-13
5
20
45
2022-02-14
7
38
46
2022-02-15
6
35
47
2022-02-16
8
48
48
2022-02-17
2
13
49
2022-02-18
6
38
50
2022-02-19
10
45
51
2022-02-20
5
19
52
2022-02-21
9
50
53
2022-02-22
6
31
54
2022-02-23
5
23
55
2022-02-24
9
35
56
2022-02-25
7
36
57
2022-02-26
6
27
58
2022-02-27
5
25
58 rows

You haven't solved this challenge yet!
Are you sure you want to reveal the answer?