Identify Power Users by Session Time

Task

Google Analytics wants to flag power users as customers whose total session duration ranks in the top 5% of all customers.

Write a SQL query to return those customers using only the ga_sessions table.

Requirements

Aggregate total session duration for each customer_id.
Return the customers whose total session duration is in the top 5%, based on the number of distinct customers.
Exclude rows where customer_id is NULL.
Order the final result by total_session_duration descending, then customer_id ascending.

Table Definition

column	type	description
session_id	INT	Unique session identifier
customer_id	VARCHAR(50)	Google Analytics customer identifier
session_duration_seconds	INT	Session duration in seconds
session_date	DATE	Date of the session
device_type	VARCHAR(20)	Device used for the session

Sample Data

session_id	customer_id	session_duration_seconds	session_date	device_type
1	CUST_008	900	2024-04-03	mobile
2	CUST_002	120	2024-04-01	desktop
3	CUST_010	1800	2024-04-02	tablet
4	CUST_001	300	2024-04-01	mobile
5	CUST_005	0	2024-04-02	desktop
6	CUST_010	1500	2024-04-05	mobile
7	CUST_003	450	2024-04-01	mobile
8	CUST_002	180	2024-04-04	desktop
9	CUST_007	-30	2024-04-03	tablet
10	CUST_004	600	2024-04-02	mobile

Expected Output

With 10 distinct non-null customers in the sample, the top 5% rounds up to 1 customer.

customer_id	total_session_duration
CUST_010	3300

Task

Google Analytics wants to flag power users as customers whose total session duration ranks in the top 5% of all customers.

Write a SQL query to return those customers using only the ga_sessions table.

Requirements

Aggregate total session duration for each customer_id.
Return the customers whose total session duration is in the top 5%, based on the number of distinct customers.
Exclude rows where customer_id is NULL.
Order the final result by total_session_duration descending, then customer_id ascending.

Table Definition

column	type	description
session_id	INT	Unique session identifier
customer_id	VARCHAR(50)	Google Analytics customer identifier
session_duration_seconds	INT	Session duration in seconds
session_date	DATE	Date of the session
device_type	VARCHAR(20)	Device used for the session

Sample Data

session_id	customer_id	session_duration_seconds	session_date	device_type
1	CUST_008	900	2024-04-03	mobile
2	CUST_002	120	2024-04-01	desktop
3	CUST_010	1800	2024-04-02	tablet
4	CUST_001	300	2024-04-01	mobile
5	CUST_005	0	2024-04-02	desktop
6	CUST_010	1500	2024-04-05	mobile
7	CUST_003	450	2024-04-01	mobile
8	CUST_002	180	2024-04-04	desktop
9	CUST_007	-30	2024-04-03	tablet
10	CUST_004	600	2024-04-02	mobile

Expected Output

With 10 distinct non-null customers in the sample, the top 5% rounds up to 1 customer.

customer_id	total_session_duration
CUST_010	3300

Task

Google Analytics wants to flag power users as customers whose total session duration ranks in the top 5% of all customers.

Write a SQL query to return those customers using only the ga_sessions table.

Requirements

Aggregate total session duration for each customer_id.
Return the customers whose total session duration is in the top 5%, based on the number of distinct customers.
Exclude rows where customer_id is NULL.
Order the final result by total_session_duration descending, then customer_id ascending.

Table Definition

column	type	description
session_id	INT	Unique session identifier
customer_id	VARCHAR(50)	Google Analytics customer identifier
session_duration_seconds	INT	Session duration in seconds
session_date	DATE	Date of the session
device_type	VARCHAR(20)	Device used for the session

Sample Data

session_id	customer_id	session_duration_seconds	session_date	device_type
1	CUST_008	900	2024-04-03	mobile
2	CUST_002	120	2024-04-01	desktop
3	CUST_010	1800	2024-04-02	tablet
4	CUST_001	300	2024-04-01	mobile
5	CUST_005	0	2024-04-02	desktop
6	CUST_010	1500	2024-04-05	mobile
7	CUST_003	450	2024-04-01	mobile
8	CUST_002	180	2024-04-04	desktop
9	CUST_007	-30	2024-04-03	tablet
10	CUST_004	600	2024-04-02	mobile

Expected Output

With 10 distinct non-null customers in the sample, the top 5% rounds up to 1 customer.

customer_id	total_session_duration
CUST_010	3300

Task

Google Analytics wants to flag power users as customers whose total session duration ranks in the top 5% of all customers.

Write a SQL query to return those customers using only the ga_sessions table.

Requirements

Aggregate total session duration for each customer_id.
Return the customers whose total session duration is in the top 5%, based on the number of distinct customers.
Exclude rows where customer_id is NULL.
Order the final result by total_session_duration descending, then customer_id ascending.

Table Definition

column	type	description
session_id	INT	Unique session identifier
customer_id	VARCHAR(50)	Google Analytics customer identifier
session_duration_seconds	INT	Session duration in seconds
session_date	DATE	Date of the session
device_type	VARCHAR(20)	Device used for the session

Sample Data

session_id	customer_id	session_duration_seconds	session_date	device_type
1	CUST_008	900	2024-04-03	mobile
2	CUST_002	120	2024-04-01	desktop
3	CUST_010	1800	2024-04-02	tablet
4	CUST_001	300	2024-04-01	mobile
5	CUST_005	0	2024-04-02	desktop
6	CUST_010	1500	2024-04-05	mobile
7	CUST_003	450	2024-04-01	mobile
8	CUST_002	180	2024-04-04	desktop
9	CUST_007	-30	2024-04-03	tablet
10	CUST_004	600	2024-04-02	mobile

Expected Output

With 10 distinct non-null customers in the sample, the top 5% rounds up to 1 customer.

customer_id	total_session_duration
CUST_010	3300

Interview Guides

Task

Requirements

Table Definition

Sample Data

Expected Output

Identify Power Users by Session Time

Task

Requirements

Table Definition

Sample Data

Expected Output

Practice SQL

Identify Power Users by Session Time

Task

Requirements

Table Definition

Sample Data

Expected Output

Identify Power Users by Session Time

Task

Requirements

Table Definition

Sample Data

Expected Output

Practice SQL