logo

Back to questions

User Session Activity

Medium

This is the same question as problem #24 in the SQL Chapter of Ace the Data Science Interview!

Assume you are given the table below containing information on user session activity. Write a query that ranks users according to their total session durations (in minutes) by descending order for each session type between the start date (2022-01-01) and end date (2022-02-01). Output the user id, session type, and the ranking of the total session duration.

Table:

Column NameType
session_idinteger
user_idinteger
session_typestring ("like", "reply", "retweet")
durationinteger (in minutes)
start_datetimestamp

Example Input:

session_iduser_idsession_typedurationstart_date
6368111like312/25/2021 12:00:00
1742111retweet601/02/2022 12:00:00
8464222reply801/16/2022 12:00:00
7153111retweet501/28/2022 12:00:00
3252333reply1501/10/2022 12:00:00

Example Output:

user_idsession_typeranking
333reply1
222reply2
111retweet1

Explanation: User 333 is listed on the top due to the highest duration of 15 minutes. The ranking resets on 3rd row as the session type changes.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.