Interview Guides

Find Recent System Error Customers | Dataford Interview Questions - Dataford - Ace your Interview

Find Recent System Error Customers

Easy

SQL & Data Manipulation

Asked at 1 company1AggregationsDate FunctionsData Wrangling

Also asked at

Problem

Task

NovaCloud tracks customer-facing application events in a single log table. Write a SQL query to find all customers who experienced a system_error in the last 24 hours.

Requirements

Return each qualifying customer only once.
Include customer_id and customer_name in the result.
Filter to rows where event_type = 'system_error' and the event happened within the last 24 hours.
Order the result by customer_id ascending.

Table Definition

customer_events

column	type	description
event_id	INT	Primary key for the event record
customer_id	INT	Unique identifier for the customer
customer_name	VARCHAR(100)	Customer name captured with the event
event_type	VARCHAR(50)	Type of event such as login, payment_failed, or system_error
event_time	TIMESTAMP	Time when the event occurred
error_code	VARCHAR(50)	Error code when applicable; may be NULL

Sample Data

event_id	customer_id	customer_name	event_type	event_time	error_code
8	106	Farah Ali	system_error	2024-06-30 11:59:59	SYS-500
2	102	Ben Ortiz	login	2024-07-01 09:15:00	NULL
5	101	Ava Patel	system_error	2024-07-01 08:30:00	SYS-502
1	101	Ava Patel	system_error	2024-07-01 11:00:00	SYS-500
10	108	Hector Diaz	system_error	2024-07-01 10:45:00	NULL
4	103	Chloe Kim	payment_failed	2024-07-01 07:45:00	PAY-402
11	109	Ivy Chen	system_error	2024-07-01 11:59:59	SYS-503
6	104	Daniel Reed	system_error	2024-07-01 12:00:00	SYS-501

Assume the current timestamp is 2024-07-01 12:00:00 for this question.

Expected Output

customer_id	customer_name
101	Ava Patel
104	Daniel Reed
108	Hector Diaz
109	Ivy Chen

Schema

customer_events

Column	Type	Description
event_idPK	INT	Primary key for the event record
customer_id	INT	Unique identifier for the customer
customer_name	VARCHAR(100)	Customer name captured with the event
event_type	VARCHAR(50)	Type of event recorded in the system
event_time	TIMESTAMP	Timestamp when the event occurred
error_code	VARCHAR(50)	Error code for failure events; may be NULL

customer_events

event_id	customer_id	customer_name	event_type	event_time	error_code
8	106	Farah Ali	system_error	2024-06-30 11:59:59	SYS-500
2	102	Ben Ortiz	login	2024-07-01 09:15:00
5	101	Ava Patel	system_error	2024-07-01 08:30:00	SYS-502
1	101	Ava Patel	system_error	2024-07-01 11:00:00	SYS-500
10	108	Hector Diaz	system_error	2024-07-01 10:45:00
4	103	Chloe Kim	payment_failed	2024-07-01 07:45:00	PAY-402
11	109	Ivy Chen	system_error	2024-07-01 11:59:59	SYS-503
6	104	Daniel Reed	system_error	2024-07-01 12:00:00	SYS-501
3	103	Chloe Kim	system_error	2024-06-29 16:20:00	SYS-404
7	105	Elena Gomez	logout	2024-07-01 06:00:00
9	107	Grace Liu	system_error	2024-06-30 12:00:00	SYS-401
12	110		system_error	2024-06-30 10:00:00	SYS-504
13	110	Ava Patel	two_factor_failed	2024-07-03 14:26:47	CFG-201
14	111	Ava Patel	validation_error	2024-07-03 04:14:41	SYS-504
15	105	Grace Liu	checkout_completed	2024-07-01 17:51:15	AUTH-401
16	100	Jack Morgan	system_error	2024-07-02 08:11:39	FILE-410
17	110	Ben Ortiz	notification_sent	2024-06-29 13:11:24	SYS-503
18	111	Maya Singh	permission_denied	2024-07-03 10:27:08	VAL-300
19	110	Liam Brooks	subscription_renewed	2024-06-29 11:27:38	FILE-410
20	100	Ava Patel	logout	2024-07-03 13:47:56	null
21	101	Chloe Kim	payment_failed	2024-06-27 08:48:11	MAIL-551
22	109	Elena Gomez	file_download	2024-06-30 10:33:56	SYS-401
23	111	Olivia Park	profile_update	2024-07-02 06:42:38	QUEUE-701
24	105	Hector Diaz	file_download	2024-06-27 05:50:36	SYS-501
25	104	Bianca Flores	payment_failed	2024-07-02 22:06:54	NET-101
26	103	Xavier Cole	email_bounce	2024-07-03 03:45:51	SYS-401
27	108	Bianca Flores	logout	2024-07-02 21:33:15	SYS-503
28	106	Elena Gomez	search	2024-07-03 15:04:52	null
29	109	Farah Ali	refund_completed	2024-07-03 19:52:18	SYS-504
30	105	Ben Ortiz	system_error	2024-07-03 20:12:07	SYS-500

Expected Output

customer_id	customer_name
100	Jack Morgan
101	Ava Patel
104	Daniel Reed
105	Ben Ortiz
107	Grace Liu
108	Hector Diaz
109	Ivy Chen

Problem

Task

NovaCloud tracks customer-facing application events in a single log table. Write a SQL query to find all customers who experienced a system_error in the last 24 hours.

Requirements

Return each qualifying customer only once.
Include customer_id and customer_name in the result.
Filter to rows where event_type = 'system_error' and the event happened within the last 24 hours.
Order the result by customer_id ascending.

Table Definition

customer_events

column	type	description
event_id	INT	Primary key for the event record
customer_id	INT	Unique identifier for the customer
customer_name	VARCHAR(100)	Customer name captured with the event
event_type	VARCHAR(50)	Type of event such as login, payment_failed, or system_error
event_time	TIMESTAMP	Time when the event occurred
error_code	VARCHAR(50)	Error code when applicable; may be NULL

Sample Data

event_id	customer_id	customer_name	event_type	event_time	error_code
8	106	Farah Ali	system_error	2024-06-30 11:59:59	SYS-500
2	102	Ben Ortiz	login	2024-07-01 09:15:00	NULL
5	101	Ava Patel	system_error	2024-07-01 08:30:00	SYS-502
1	101	Ava Patel	system_error	2024-07-01 11:00:00	SYS-500
10	108	Hector Diaz	system_error	2024-07-01 10:45:00	NULL
4	103	Chloe Kim	payment_failed	2024-07-01 07:45:00	PAY-402
11	109	Ivy Chen	system_error	2024-07-01 11:59:59	SYS-503
6	104	Daniel Reed	system_error	2024-07-01 12:00:00	SYS-501

Assume the current timestamp is 2024-07-01 12:00:00 for this question.

Expected Output

customer_id	customer_name
101	Ava Patel
104	Daniel Reed
108	Hector Diaz
109	Ivy Chen

Schema

customer_events

Column	Type	Description
event_idPK	INT	Primary key for the event record
customer_id	INT	Unique identifier for the customer
customer_name	VARCHAR(100)	Customer name captured with the event
event_type	VARCHAR(50)	Type of event recorded in the system
event_time	TIMESTAMP	Timestamp when the event occurred
error_code	VARCHAR(50)	Error code for failure events; may be NULL

customer_events

event_id	customer_id	customer_name	event_type	event_time	error_code
8	106	Farah Ali	system_error	2024-06-30 11:59:59	SYS-500
2	102	Ben Ortiz	login	2024-07-01 09:15:00
5	101	Ava Patel	system_error	2024-07-01 08:30:00	SYS-502
1	101	Ava Patel	system_error	2024-07-01 11:00:00	SYS-500
10	108	Hector Diaz	system_error	2024-07-01 10:45:00
4	103	Chloe Kim	payment_failed	2024-07-01 07:45:00	PAY-402
11	109	Ivy Chen	system_error	2024-07-01 11:59:59	SYS-503
6	104	Daniel Reed	system_error	2024-07-01 12:00:00	SYS-501
3	103	Chloe Kim	system_error	2024-06-29 16:20:00	SYS-404
7	105	Elena Gomez	logout	2024-07-01 06:00:00
9	107	Grace Liu	system_error	2024-06-30 12:00:00	SYS-401
12	110		system_error	2024-06-30 10:00:00	SYS-504
13	110	Ava Patel	two_factor_failed	2024-07-03 14:26:47	CFG-201
14	111	Ava Patel	validation_error	2024-07-03 04:14:41	SYS-504
15	105	Grace Liu	checkout_completed	2024-07-01 17:51:15	AUTH-401
16	100	Jack Morgan	system_error	2024-07-02 08:11:39	FILE-410
17	110	Ben Ortiz	notification_sent	2024-06-29 13:11:24	SYS-503
18	111	Maya Singh	permission_denied	2024-07-03 10:27:08	VAL-300
19	110	Liam Brooks	subscription_renewed	2024-06-29 11:27:38	FILE-410
20	100	Ava Patel	logout	2024-07-03 13:47:56	null
21	101	Chloe Kim	payment_failed	2024-06-27 08:48:11	MAIL-551
22	109	Elena Gomez	file_download	2024-06-30 10:33:56	SYS-401
23	111	Olivia Park	profile_update	2024-07-02 06:42:38	QUEUE-701
24	105	Hector Diaz	file_download	2024-06-27 05:50:36	SYS-501
25	104	Bianca Flores	payment_failed	2024-07-02 22:06:54	NET-101
26	103	Xavier Cole	email_bounce	2024-07-03 03:45:51	SYS-401
27	108	Bianca Flores	logout	2024-07-02 21:33:15	SYS-503
28	106	Elena Gomez	search	2024-07-03 15:04:52	null
29	109	Farah Ali	refund_completed	2024-07-03 19:52:18	SYS-504
30	105	Ben Ortiz	system_error	2024-07-03 20:12:07	SYS-500

Expected Output

customer_id	customer_name
100	Jack Morgan
101	Ava Patel
104	Daniel Reed
105	Ben Ortiz
107	Grace Liu
108	Hector Diaz
109	Ivy Chen

Practice SQL

PostgreSQL

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

Up next

ATop Weekly Error CodesEasy

Flag and Remove Duplicate CustomersMedium

Monthly Customer Spend and RankMedium

Next question

Find Recent System Error Customers

Easy

SQL & Data Manipulation

Asked at 1 company1AggregationsDate FunctionsData Wrangling

Also asked at

Problem

Task

NovaCloud tracks customer-facing application events in a single log table. Write a SQL query to find all customers who experienced a system_error in the last 24 hours.

Requirements

Return each qualifying customer only once.
Include customer_id and customer_name in the result.
Filter to rows where event_type = 'system_error' and the event happened within the last 24 hours.
Order the result by customer_id ascending.

Table Definition

customer_events

column	type	description
event_id	INT	Primary key for the event record
customer_id	INT	Unique identifier for the customer
customer_name	VARCHAR(100)	Customer name captured with the event
event_type	VARCHAR(50)	Type of event such as login, payment_failed, or system_error
event_time	TIMESTAMP	Time when the event occurred
error_code	VARCHAR(50)	Error code when applicable; may be NULL

Sample Data

event_id	customer_id	customer_name	event_type	event_time	error_code
8	106	Farah Ali	system_error	2024-06-30 11:59:59	SYS-500
2	102	Ben Ortiz	login	2024-07-01 09:15:00	NULL
5	101	Ava Patel	system_error	2024-07-01 08:30:00	SYS-502
1	101	Ava Patel	system_error	2024-07-01 11:00:00	SYS-500
10	108	Hector Diaz	system_error	2024-07-01 10:45:00	NULL
4	103	Chloe Kim	payment_failed	2024-07-01 07:45:00	PAY-402
11	109	Ivy Chen	system_error	2024-07-01 11:59:59	SYS-503
6	104	Daniel Reed	system_error	2024-07-01 12:00:00	SYS-501

Assume the current timestamp is 2024-07-01 12:00:00 for this question.

Expected Output

customer_id	customer_name
101	Ava Patel
104	Daniel Reed
108	Hector Diaz
109	Ivy Chen

Schema

customer_events

Column	Type	Description
event_idPK	INT	Primary key for the event record
customer_id	INT	Unique identifier for the customer
customer_name	VARCHAR(100)	Customer name captured with the event
event_type	VARCHAR(50)	Type of event recorded in the system
event_time	TIMESTAMP	Timestamp when the event occurred
error_code	VARCHAR(50)	Error code for failure events; may be NULL

customer_events

event_id	customer_id	customer_name	event_type	event_time	error_code
8	106	Farah Ali	system_error	2024-06-30 11:59:59	SYS-500
2	102	Ben Ortiz	login	2024-07-01 09:15:00
5	101	Ava Patel	system_error	2024-07-01 08:30:00	SYS-502
1	101	Ava Patel	system_error	2024-07-01 11:00:00	SYS-500
10	108	Hector Diaz	system_error	2024-07-01 10:45:00
4	103	Chloe Kim	payment_failed	2024-07-01 07:45:00	PAY-402
11	109	Ivy Chen	system_error	2024-07-01 11:59:59	SYS-503
6	104	Daniel Reed	system_error	2024-07-01 12:00:00	SYS-501
3	103	Chloe Kim	system_error	2024-06-29 16:20:00	SYS-404
7	105	Elena Gomez	logout	2024-07-01 06:00:00
9	107	Grace Liu	system_error	2024-06-30 12:00:00	SYS-401
12	110		system_error	2024-06-30 10:00:00	SYS-504
13	110	Ava Patel	two_factor_failed	2024-07-03 14:26:47	CFG-201
14	111	Ava Patel	validation_error	2024-07-03 04:14:41	SYS-504
15	105	Grace Liu	checkout_completed	2024-07-01 17:51:15	AUTH-401
16	100	Jack Morgan	system_error	2024-07-02 08:11:39	FILE-410
17	110	Ben Ortiz	notification_sent	2024-06-29 13:11:24	SYS-503
18	111	Maya Singh	permission_denied	2024-07-03 10:27:08	VAL-300
19	110	Liam Brooks	subscription_renewed	2024-06-29 11:27:38	FILE-410
20	100	Ava Patel	logout	2024-07-03 13:47:56	null
21	101	Chloe Kim	payment_failed	2024-06-27 08:48:11	MAIL-551
22	109	Elena Gomez	file_download	2024-06-30 10:33:56	SYS-401
23	111	Olivia Park	profile_update	2024-07-02 06:42:38	QUEUE-701
24	105	Hector Diaz	file_download	2024-06-27 05:50:36	SYS-501
25	104	Bianca Flores	payment_failed	2024-07-02 22:06:54	NET-101
26	103	Xavier Cole	email_bounce	2024-07-03 03:45:51	SYS-401
27	108	Bianca Flores	logout	2024-07-02 21:33:15	SYS-503
28	106	Elena Gomez	search	2024-07-03 15:04:52	null
29	109	Farah Ali	refund_completed	2024-07-03 19:52:18	SYS-504
30	105	Ben Ortiz	system_error	2024-07-03 20:12:07	SYS-500

Expected Output

customer_id	customer_name
100	Jack Morgan
101	Ava Patel
104	Daniel Reed
105	Ben Ortiz
107	Grace Liu
108	Hector Diaz
109	Ivy Chen

Problem

Task

NovaCloud tracks customer-facing application events in a single log table. Write a SQL query to find all customers who experienced a system_error in the last 24 hours.

Requirements

Return each qualifying customer only once.
Include customer_id and customer_name in the result.
Filter to rows where event_type = 'system_error' and the event happened within the last 24 hours.
Order the result by customer_id ascending.

Table Definition

customer_events

column	type	description
event_id	INT	Primary key for the event record
customer_id	INT	Unique identifier for the customer
customer_name	VARCHAR(100)	Customer name captured with the event
event_type	VARCHAR(50)	Type of event such as login, payment_failed, or system_error
event_time	TIMESTAMP	Time when the event occurred
error_code	VARCHAR(50)	Error code when applicable; may be NULL

Sample Data

event_id	customer_id	customer_name	event_type	event_time	error_code
8	106	Farah Ali	system_error	2024-06-30 11:59:59	SYS-500
2	102	Ben Ortiz	login	2024-07-01 09:15:00	NULL
5	101	Ava Patel	system_error	2024-07-01 08:30:00	SYS-502
1	101	Ava Patel	system_error	2024-07-01 11:00:00	SYS-500
10	108	Hector Diaz	system_error	2024-07-01 10:45:00	NULL
4	103	Chloe Kim	payment_failed	2024-07-01 07:45:00	PAY-402
11	109	Ivy Chen	system_error	2024-07-01 11:59:59	SYS-503
6	104	Daniel Reed	system_error	2024-07-01 12:00:00	SYS-501

Assume the current timestamp is 2024-07-01 12:00:00 for this question.

Expected Output

customer_id	customer_name
101	Ava Patel
104	Daniel Reed
108	Hector Diaz
109	Ivy Chen

Schema

customer_events

Column	Type	Description
event_idPK	INT	Primary key for the event record
customer_id	INT	Unique identifier for the customer
customer_name	VARCHAR(100)	Customer name captured with the event
event_type	VARCHAR(50)	Type of event recorded in the system
event_time	TIMESTAMP	Timestamp when the event occurred
error_code	VARCHAR(50)	Error code for failure events; may be NULL

customer_events

event_id	customer_id	customer_name	event_type	event_time	error_code
8	106	Farah Ali	system_error	2024-06-30 11:59:59	SYS-500
2	102	Ben Ortiz	login	2024-07-01 09:15:00
5	101	Ava Patel	system_error	2024-07-01 08:30:00	SYS-502
1	101	Ava Patel	system_error	2024-07-01 11:00:00	SYS-500
10	108	Hector Diaz	system_error	2024-07-01 10:45:00
4	103	Chloe Kim	payment_failed	2024-07-01 07:45:00	PAY-402
11	109	Ivy Chen	system_error	2024-07-01 11:59:59	SYS-503
6	104	Daniel Reed	system_error	2024-07-01 12:00:00	SYS-501
3	103	Chloe Kim	system_error	2024-06-29 16:20:00	SYS-404
7	105	Elena Gomez	logout	2024-07-01 06:00:00
9	107	Grace Liu	system_error	2024-06-30 12:00:00	SYS-401
12	110		system_error	2024-06-30 10:00:00	SYS-504
13	110	Ava Patel	two_factor_failed	2024-07-03 14:26:47	CFG-201
14	111	Ava Patel	validation_error	2024-07-03 04:14:41	SYS-504
15	105	Grace Liu	checkout_completed	2024-07-01 17:51:15	AUTH-401
16	100	Jack Morgan	system_error	2024-07-02 08:11:39	FILE-410
17	110	Ben Ortiz	notification_sent	2024-06-29 13:11:24	SYS-503
18	111	Maya Singh	permission_denied	2024-07-03 10:27:08	VAL-300
19	110	Liam Brooks	subscription_renewed	2024-06-29 11:27:38	FILE-410
20	100	Ava Patel	logout	2024-07-03 13:47:56	null
21	101	Chloe Kim	payment_failed	2024-06-27 08:48:11	MAIL-551
22	109	Elena Gomez	file_download	2024-06-30 10:33:56	SYS-401
23	111	Olivia Park	profile_update	2024-07-02 06:42:38	QUEUE-701
24	105	Hector Diaz	file_download	2024-06-27 05:50:36	SYS-501
25	104	Bianca Flores	payment_failed	2024-07-02 22:06:54	NET-101
26	103	Xavier Cole	email_bounce	2024-07-03 03:45:51	SYS-401
27	108	Bianca Flores	logout	2024-07-02 21:33:15	SYS-503
28	106	Elena Gomez	search	2024-07-03 15:04:52	null
29	109	Farah Ali	refund_completed	2024-07-03 19:52:18	SYS-504
30	105	Ben Ortiz	system_error	2024-07-03 20:12:07	SYS-500

Expected Output

customer_id	customer_name
100	Jack Morgan
101	Ava Patel
104	Daniel Reed
105	Ben Ortiz
107	Grace Liu
108	Hector Diaz
109	Ivy Chen

Practice SQL

PostgreSQL

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

Up next

ATop Weekly Error CodesEasy

Flag and Remove Duplicate CustomersMedium

Monthly Customer Spend and RankMedium

Next question