Interview Guides

Find Active Chegg Study Users | Dataford Interview Questions - Dataford - Ace your Interview

Find Active Chegg Study Users

Easy

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsData Wrangling

Also asked at

Problem

Task

You are given a table of Chegg Study user activity. Write a SQL query to return each user_id and email for users whose status is 'active' and who have completed more than 3 study sessions. Sort the results by study_sessions_completed in descending order, and then by user_id in ascending order.

Schema

chegg_study_users

column	type	description
user_id	INT	Unique user identifier
email	VARCHAR(255)	User email address
status	VARCHAR(20)	Account status such as active or inactive
study_sessions_completed	INT	Number of completed study sessions
signup_date	DATE	Date the user signed up

Sample data

user_id	email	status	study_sessions_completed	signup_date
104	devon.lee@school.edu	active	2	2024-02-01
101	maya.patel@school.edu	active	5	2024-01-10
108	sara.kim@school.edu	inactive	9	2024-01-05
103	jordan.ng@school.edu	active	4	2024-03-12
106		active	7	2024-02-18

Expected output

user_id	email
106
101	maya.patel@school.edu
103	jordan.ng@school.edu
110	priya.shah@school.edu

Schema

chegg_study_users

Column	Type	Description
user_idPK	INT	Unique user identifier
email	VARCHAR(255)	User email address
status	VARCHAR(20)	Account status
study_sessions_completed	INT	Number of completed study sessions
signup_date	DATE	Date the user signed up

chegg_study_users

user_id	email	status	study_sessions_completed	signup_date
104	devon.lee@school.edu	active	2	2024-02-01
101	maya.patel@school.edu	active	5	2024-01-10
108	sara.kim@school.edu	inactive	9	2024-01-05
103	jordan.ng@school.edu	active	4	2024-03-12
106		active	7	2024-02-18
102	liam.chen@school.edu	inactive	6	2024-01-22
109	omar.hassan@school.edu	active	3	2024-03-01
105	ava.martin@school.edu	active	0	2024-04-02
110	priya.shah@school.edu	active	4	2024-02-25
107			8	2024-01-30
1	sara.kim@school.edu	expired	9	2024-02-14
2	priya.shah@school.edu	prospect	null	2024-02-11
3	henry.cole@school.edu	basic	17	2024-01-26
4	chloe.morris@school.edu	churned	7	2024-03-19
5	jackson.bell@school.edu	prospect	null	2024-01-02
6	null	prospect	67	2024-03-17
7	lucas.hill@school.edu	premium	86	2024-03-07
8	omar.hassan@school.edu	review	63	2024-03-09
9	grace.long@school.edu	dormant	25	2024-03-22
10	chloe.morris@school.edu	expired	71	2024-02-28
11	ethan.brooks@school.edu	trial	93	2024-01-23
12	emma.roberts@school.edu	suspended	10	2024-02-09
13	chloe.morris@school.edu	premium	14	2024-02-26
14	maya.patel@school.edu	active	43	2024-02-09
15	sara.kim@school.edu	suspended	33	2024-01-22
16	caleb.foster@school.edu	inactive	62	2024-02-19
17	ethan.brooks@school.edu	churned	85	2024-02-26
18	lucas.hill@school.edu	paused	42	2024-03-22
19	chloe.morris@school.edu	null	36	2024-01-07

Expected Output

user_id	email
14	maya.patel@school.edu
106	null
101	maya.patel@school.edu
103	jordan.ng@school.edu
110	priya.shah@school.edu

Problem

Task

Schema

chegg_study_users

column	type	description
user_id	INT	Unique user identifier
email	VARCHAR(255)	User email address
status	VARCHAR(20)	Account status such as active or inactive
study_sessions_completed	INT	Number of completed study sessions
signup_date	DATE	Date the user signed up

Sample data

user_id	email	status	study_sessions_completed	signup_date
104	devon.lee@school.edu	active	2	2024-02-01
101	maya.patel@school.edu	active	5	2024-01-10
108	sara.kim@school.edu	inactive	9	2024-01-05
103	jordan.ng@school.edu	active	4	2024-03-12
106		active	7	2024-02-18

Expected output

user_id	email
106
101	maya.patel@school.edu
103	jordan.ng@school.edu
110	priya.shah@school.edu

Schema

chegg_study_users

Column	Type	Description
user_idPK	INT	Unique user identifier
email	VARCHAR(255)	User email address
status	VARCHAR(20)	Account status
study_sessions_completed	INT	Number of completed study sessions
signup_date	DATE	Date the user signed up

chegg_study_users

user_id	email	status	study_sessions_completed	signup_date
104	devon.lee@school.edu	active	2	2024-02-01
101	maya.patel@school.edu	active	5	2024-01-10
108	sara.kim@school.edu	inactive	9	2024-01-05
103	jordan.ng@school.edu	active	4	2024-03-12
106		active	7	2024-02-18
102	liam.chen@school.edu	inactive	6	2024-01-22
109	omar.hassan@school.edu	active	3	2024-03-01
105	ava.martin@school.edu	active	0	2024-04-02
110	priya.shah@school.edu	active	4	2024-02-25
107			8	2024-01-30
1	sara.kim@school.edu	expired	9	2024-02-14
2	priya.shah@school.edu	prospect	null	2024-02-11
3	henry.cole@school.edu	basic	17	2024-01-26
4	chloe.morris@school.edu	churned	7	2024-03-19
5	jackson.bell@school.edu	prospect	null	2024-01-02
6	null	prospect	67	2024-03-17
7	lucas.hill@school.edu	premium	86	2024-03-07
8	omar.hassan@school.edu	review	63	2024-03-09
9	grace.long@school.edu	dormant	25	2024-03-22
10	chloe.morris@school.edu	expired	71	2024-02-28
11	ethan.brooks@school.edu	trial	93	2024-01-23
12	emma.roberts@school.edu	suspended	10	2024-02-09
13	chloe.morris@school.edu	premium	14	2024-02-26
14	maya.patel@school.edu	active	43	2024-02-09
15	sara.kim@school.edu	suspended	33	2024-01-22
16	caleb.foster@school.edu	inactive	62	2024-02-19
17	ethan.brooks@school.edu	churned	85	2024-02-26
18	lucas.hill@school.edu	paused	42	2024-03-22
19	chloe.morris@school.edu	null	36	2024-01-07

Expected Output

user_id	email
14	maya.patel@school.edu
106	null
101	maya.patel@school.edu
103	jordan.ng@school.edu
110	priya.shah@school.edu

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Find Three-Day Chime Activity StreaksHard ATop Active Users by Event CountMedium

Second-Highest Marketplace Transactions UserMedium

Next question

Find Active Chegg Study Users

Easy

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsData Wrangling

Also asked at

Problem

Task

Schema

chegg_study_users

column	type	description
user_id	INT	Unique user identifier
email	VARCHAR(255)	User email address
status	VARCHAR(20)	Account status such as active or inactive
study_sessions_completed	INT	Number of completed study sessions
signup_date	DATE	Date the user signed up

Sample data

user_id	email	status	study_sessions_completed	signup_date
104	devon.lee@school.edu	active	2	2024-02-01
101	maya.patel@school.edu	active	5	2024-01-10
108	sara.kim@school.edu	inactive	9	2024-01-05
103	jordan.ng@school.edu	active	4	2024-03-12
106		active	7	2024-02-18

Expected output

user_id	email
106
101	maya.patel@school.edu
103	jordan.ng@school.edu
110	priya.shah@school.edu

Schema

chegg_study_users

Column	Type	Description
user_idPK	INT	Unique user identifier
email	VARCHAR(255)	User email address
status	VARCHAR(20)	Account status
study_sessions_completed	INT	Number of completed study sessions
signup_date	DATE	Date the user signed up

chegg_study_users

user_id	email	status	study_sessions_completed	signup_date
104	devon.lee@school.edu	active	2	2024-02-01
101	maya.patel@school.edu	active	5	2024-01-10
108	sara.kim@school.edu	inactive	9	2024-01-05
103	jordan.ng@school.edu	active	4	2024-03-12
106		active	7	2024-02-18
102	liam.chen@school.edu	inactive	6	2024-01-22
109	omar.hassan@school.edu	active	3	2024-03-01
105	ava.martin@school.edu	active	0	2024-04-02
110	priya.shah@school.edu	active	4	2024-02-25
107			8	2024-01-30
1	sara.kim@school.edu	expired	9	2024-02-14
2	priya.shah@school.edu	prospect	null	2024-02-11
3	henry.cole@school.edu	basic	17	2024-01-26
4	chloe.morris@school.edu	churned	7	2024-03-19
5	jackson.bell@school.edu	prospect	null	2024-01-02
6	null	prospect	67	2024-03-17
7	lucas.hill@school.edu	premium	86	2024-03-07
8	omar.hassan@school.edu	review	63	2024-03-09
9	grace.long@school.edu	dormant	25	2024-03-22
10	chloe.morris@school.edu	expired	71	2024-02-28
11	ethan.brooks@school.edu	trial	93	2024-01-23
12	emma.roberts@school.edu	suspended	10	2024-02-09
13	chloe.morris@school.edu	premium	14	2024-02-26
14	maya.patel@school.edu	active	43	2024-02-09
15	sara.kim@school.edu	suspended	33	2024-01-22
16	caleb.foster@school.edu	inactive	62	2024-02-19
17	ethan.brooks@school.edu	churned	85	2024-02-26
18	lucas.hill@school.edu	paused	42	2024-03-22
19	chloe.morris@school.edu	null	36	2024-01-07

Expected Output

user_id	email
14	maya.patel@school.edu
106	null
101	maya.patel@school.edu
103	jordan.ng@school.edu
110	priya.shah@school.edu

Problem

Task

Schema

chegg_study_users

column	type	description
user_id	INT	Unique user identifier
email	VARCHAR(255)	User email address
status	VARCHAR(20)	Account status such as active or inactive
study_sessions_completed	INT	Number of completed study sessions
signup_date	DATE	Date the user signed up

Sample data

user_id	email	status	study_sessions_completed	signup_date
104	devon.lee@school.edu	active	2	2024-02-01
101	maya.patel@school.edu	active	5	2024-01-10
108	sara.kim@school.edu	inactive	9	2024-01-05
103	jordan.ng@school.edu	active	4	2024-03-12
106		active	7	2024-02-18

Expected output

user_id	email
106
101	maya.patel@school.edu
103	jordan.ng@school.edu
110	priya.shah@school.edu

Schema

chegg_study_users

Column	Type	Description
user_idPK	INT	Unique user identifier
email	VARCHAR(255)	User email address
status	VARCHAR(20)	Account status
study_sessions_completed	INT	Number of completed study sessions
signup_date	DATE	Date the user signed up

chegg_study_users

user_id	email	status	study_sessions_completed	signup_date
104	devon.lee@school.edu	active	2	2024-02-01
101	maya.patel@school.edu	active	5	2024-01-10
108	sara.kim@school.edu	inactive	9	2024-01-05
103	jordan.ng@school.edu	active	4	2024-03-12
106		active	7	2024-02-18
102	liam.chen@school.edu	inactive	6	2024-01-22
109	omar.hassan@school.edu	active	3	2024-03-01
105	ava.martin@school.edu	active	0	2024-04-02
110	priya.shah@school.edu	active	4	2024-02-25
107			8	2024-01-30
1	sara.kim@school.edu	expired	9	2024-02-14
2	priya.shah@school.edu	prospect	null	2024-02-11
3	henry.cole@school.edu	basic	17	2024-01-26
4	chloe.morris@school.edu	churned	7	2024-03-19
5	jackson.bell@school.edu	prospect	null	2024-01-02
6	null	prospect	67	2024-03-17
7	lucas.hill@school.edu	premium	86	2024-03-07
8	omar.hassan@school.edu	review	63	2024-03-09
9	grace.long@school.edu	dormant	25	2024-03-22
10	chloe.morris@school.edu	expired	71	2024-02-28
11	ethan.brooks@school.edu	trial	93	2024-01-23
12	emma.roberts@school.edu	suspended	10	2024-02-09
13	chloe.morris@school.edu	premium	14	2024-02-26
14	maya.patel@school.edu	active	43	2024-02-09
15	sara.kim@school.edu	suspended	33	2024-01-22
16	caleb.foster@school.edu	inactive	62	2024-02-19
17	ethan.brooks@school.edu	churned	85	2024-02-26
18	lucas.hill@school.edu	paused	42	2024-03-22
19	chloe.morris@school.edu	null	36	2024-01-07

Expected Output

user_id	email
14	maya.patel@school.edu
106	null
101	maya.patel@school.edu
103	jordan.ng@school.edu
110	priya.shah@school.edu

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Find Three-Day Chime Activity StreaksHard ATop Active Users by Event CountMedium

Second-Highest Marketplace Transactions UserMedium

Next question