Interview Guides

Top Weekly Error Codes | Dataford Interview Questions - Dataford - Ace your Interview

Top Weekly Error Codes

Easy

SQL & Data Manipulation

Asked at 1 company1AggregationsGroup ByDate Functions

Also asked at

Problem

Task

NovaCare wants a quick view of which application errors are happening most often in recent customer logs. Write a SQL query to find the most common error_code values from the last 7 days.

Requirements

Use only rows from the last 7 days based on log_time.
Exclude rows where error_code is NULL.
Return each error_code and its occurrence count as error_count.
Sort by error_count descending, then error_code ascending.

Table Definition

customer_logs

Column	Type	Description
log_id	INT	Primary key for each log row
customer_id	INT	Customer who generated the log
log_time	TIMESTAMP	Time the log was recorded
log_level	VARCHAR(20)	Severity level such as INFO or ERROR
error_code	VARCHAR(20)	Error code associated with the log
message	TEXT	Log message text

Sample Data

log_id	customer_id	log_time	log_level	error_code	message
1	101	2024-06-14 09:12:00	ERROR	E401	Unauthorized token
2	102	2024-06-13 11:45:00	ERROR	E500	Internal server error
3	103	2024-06-12 08:30:00	WARN	NULL	Slow response
4	104	2024-06-10 14:05:00	ERROR	E401	Unauthorized token
5	105	2024-06-09 16:20:00	ERROR	E404	Resource not found
6	106	2024-06-07 10:00:00	ERROR	E500	Internal server error

Expected Output

error_code	error_count
E401	3
E500	2
E404	1

Schema

customer_logs

Column	Type	Description
log_idPK	INT	Primary key for each log entry
customer_id	INT	Customer associated with the log
log_time	TIMESTAMP	Timestamp when the log was recorded
log_level	VARCHAR(20)	Severity level of the log event
error_code	VARCHAR(20)	Application error code if present
message	TEXT	Raw log message

customer_logs

log_id	customer_id	log_time	log_level	error_code	message
1	101	2024-06-14 09:12:00	ERROR	E401	Unauthorized token
2	102	2024-06-13 11:45:00	ERROR	E500	Internal server error
3	103	2024-06-12 08:30:00	WARN		Slow response
4	104	2024-06-10 14:05:00	ERROR	E401	Unauthorized token
5	105	2024-06-09 16:20:00	ERROR	E404	Resource not found
6	106	2024-06-07 10:00:00	ERROR	E500	Internal server error
7	107	2024-06-14 18:40:00	INFO
8	108	2024-06-11 07:55:00	ERROR	E401	Unauthorized token
9	109	2024-06-08 00:00:00	ERROR	E500	Internal server error
10	110	2024-06-01 13:15:00	ERROR	E404	Resource not found
11	108	2024-05-29 22:12:24	SYNC	E415	Unauthorized token
12	104	2024-06-11 13:40:12	INFO	E502	null
13	105	2024-06-09 14:12:15	VALIDATION	E400	null
14	100	2024-06-01 14:21:12	NETWORK	E301	Invalid request payload
15	109	2024-06-12 00:08:34	SUCCESS	E500	Unsupported media type
16	100	2024-06-12 10:52:06	METRIC	E504	Internal server error
17	109	2024-05-30 20:42:11	RETRY	E305	Password reset link expired
18	107	2024-06-10 02:12:46	CACHE	E403	Permission denied
19	107	2024-06-15 14:02:27	METRIC	E460	Queue backlog detected
20	104	2024-06-10 16:33:23	WARN	null	Gateway timeout
21	111	2024-06-04 18:24:19	QUEUE	null	Payment authorization failed
22	108	2024-06-16 07:01:10	NOTICE	E415	Resource not found
23	109	2024-06-07 23:51:23	EMERGENCY	E509	Service unavailable
24	105	2024-06-04 02:03:10	SUCCESS	E404	Invalid request payload
25	104	2024-06-07 06:12:59	ALERT	E599	Bad gateway
26	100	2024-06-11 09:02:25	EMERGENCY	E521	Permission denied
27	109	2024-06-02 02:57:24	NOTICE	null	Session expired
28	104	2024-06-08 22:26:30	APPLICATION	E301	Cache miss threshold exceeded
29	101	2024-06-06 07:08:34	ERROR	E302	Webhook timeout
30	100	2024-06-13 17:52:33	VALIDATION	E410	Dependency service error

Expected Output

error_code	error_count
E401	3
E500	3
E301	1
E400	1
E403	1
E404	1
E410	1
E415	1
E460	1
E502	1
E504	1
E521	1

Problem

Task

NovaCare wants a quick view of which application errors are happening most often in recent customer logs. Write a SQL query to find the most common error_code values from the last 7 days.

Requirements

Use only rows from the last 7 days based on log_time.
Exclude rows where error_code is NULL.
Return each error_code and its occurrence count as error_count.
Sort by error_count descending, then error_code ascending.

Table Definition

customer_logs

Column	Type	Description
log_id	INT	Primary key for each log row
customer_id	INT	Customer who generated the log
log_time	TIMESTAMP	Time the log was recorded
log_level	VARCHAR(20)	Severity level such as INFO or ERROR
error_code	VARCHAR(20)	Error code associated with the log
message	TEXT	Log message text

Sample Data

log_id	customer_id	log_time	log_level	error_code	message
1	101	2024-06-14 09:12:00	ERROR	E401	Unauthorized token
2	102	2024-06-13 11:45:00	ERROR	E500	Internal server error
3	103	2024-06-12 08:30:00	WARN	NULL	Slow response
4	104	2024-06-10 14:05:00	ERROR	E401	Unauthorized token
5	105	2024-06-09 16:20:00	ERROR	E404	Resource not found
6	106	2024-06-07 10:00:00	ERROR	E500	Internal server error

Expected Output

error_code	error_count
E401	3
E500	2
E404	1

Schema

customer_logs

Column	Type	Description
log_idPK	INT	Primary key for each log entry
customer_id	INT	Customer associated with the log
log_time	TIMESTAMP	Timestamp when the log was recorded
log_level	VARCHAR(20)	Severity level of the log event
error_code	VARCHAR(20)	Application error code if present
message	TEXT	Raw log message

customer_logs

log_id	customer_id	log_time	log_level	error_code	message
1	101	2024-06-14 09:12:00	ERROR	E401	Unauthorized token
2	102	2024-06-13 11:45:00	ERROR	E500	Internal server error
3	103	2024-06-12 08:30:00	WARN		Slow response
4	104	2024-06-10 14:05:00	ERROR	E401	Unauthorized token
5	105	2024-06-09 16:20:00	ERROR	E404	Resource not found
6	106	2024-06-07 10:00:00	ERROR	E500	Internal server error
7	107	2024-06-14 18:40:00	INFO
8	108	2024-06-11 07:55:00	ERROR	E401	Unauthorized token
9	109	2024-06-08 00:00:00	ERROR	E500	Internal server error
10	110	2024-06-01 13:15:00	ERROR	E404	Resource not found
11	108	2024-05-29 22:12:24	SYNC	E415	Unauthorized token
12	104	2024-06-11 13:40:12	INFO	E502	null
13	105	2024-06-09 14:12:15	VALIDATION	E400	null
14	100	2024-06-01 14:21:12	NETWORK	E301	Invalid request payload
15	109	2024-06-12 00:08:34	SUCCESS	E500	Unsupported media type
16	100	2024-06-12 10:52:06	METRIC	E504	Internal server error
17	109	2024-05-30 20:42:11	RETRY	E305	Password reset link expired
18	107	2024-06-10 02:12:46	CACHE	E403	Permission denied
19	107	2024-06-15 14:02:27	METRIC	E460	Queue backlog detected
20	104	2024-06-10 16:33:23	WARN	null	Gateway timeout
21	111	2024-06-04 18:24:19	QUEUE	null	Payment authorization failed
22	108	2024-06-16 07:01:10	NOTICE	E415	Resource not found
23	109	2024-06-07 23:51:23	EMERGENCY	E509	Service unavailable
24	105	2024-06-04 02:03:10	SUCCESS	E404	Invalid request payload
25	104	2024-06-07 06:12:59	ALERT	E599	Bad gateway
26	100	2024-06-11 09:02:25	EMERGENCY	E521	Permission denied
27	109	2024-06-02 02:57:24	NOTICE	null	Session expired
28	104	2024-06-08 22:26:30	APPLICATION	E301	Cache miss threshold exceeded
29	101	2024-06-06 07:08:34	ERROR	E302	Webhook timeout
30	100	2024-06-13 17:52:33	VALIDATION	E410	Dependency service error

Expected Output

error_code	error_count
E401	3
E500	3
E301	1
E400	1
E403	1
E404	1
E410	1
E415	1
E460	1
E502	1
E504	1
E521	1

Practice SQL

PostgreSQL

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

Up next

AFind Recent System Error CustomersEasy

Diagnose Inconsistent Customer ReportMedium

Monthly Customer Spend and RankMedium

Next question

Top Weekly Error Codes

Easy

SQL & Data Manipulation

Asked at 1 company1AggregationsGroup ByDate Functions

Also asked at

Problem

Task

NovaCare wants a quick view of which application errors are happening most often in recent customer logs. Write a SQL query to find the most common error_code values from the last 7 days.

Requirements

Use only rows from the last 7 days based on log_time.
Exclude rows where error_code is NULL.
Return each error_code and its occurrence count as error_count.
Sort by error_count descending, then error_code ascending.

Table Definition

customer_logs

Column	Type	Description
log_id	INT	Primary key for each log row
customer_id	INT	Customer who generated the log
log_time	TIMESTAMP	Time the log was recorded
log_level	VARCHAR(20)	Severity level such as INFO or ERROR
error_code	VARCHAR(20)	Error code associated with the log
message	TEXT	Log message text

Sample Data

log_id	customer_id	log_time	log_level	error_code	message
1	101	2024-06-14 09:12:00	ERROR	E401	Unauthorized token
2	102	2024-06-13 11:45:00	ERROR	E500	Internal server error
3	103	2024-06-12 08:30:00	WARN	NULL	Slow response
4	104	2024-06-10 14:05:00	ERROR	E401	Unauthorized token
5	105	2024-06-09 16:20:00	ERROR	E404	Resource not found
6	106	2024-06-07 10:00:00	ERROR	E500	Internal server error

Expected Output

error_code	error_count
E401	3
E500	2
E404	1

Schema

customer_logs

Column	Type	Description
log_idPK	INT	Primary key for each log entry
customer_id	INT	Customer associated with the log
log_time	TIMESTAMP	Timestamp when the log was recorded
log_level	VARCHAR(20)	Severity level of the log event
error_code	VARCHAR(20)	Application error code if present
message	TEXT	Raw log message

customer_logs

log_id	customer_id	log_time	log_level	error_code	message
1	101	2024-06-14 09:12:00	ERROR	E401	Unauthorized token
2	102	2024-06-13 11:45:00	ERROR	E500	Internal server error
3	103	2024-06-12 08:30:00	WARN		Slow response
4	104	2024-06-10 14:05:00	ERROR	E401	Unauthorized token
5	105	2024-06-09 16:20:00	ERROR	E404	Resource not found
6	106	2024-06-07 10:00:00	ERROR	E500	Internal server error
7	107	2024-06-14 18:40:00	INFO
8	108	2024-06-11 07:55:00	ERROR	E401	Unauthorized token
9	109	2024-06-08 00:00:00	ERROR	E500	Internal server error
10	110	2024-06-01 13:15:00	ERROR	E404	Resource not found
11	108	2024-05-29 22:12:24	SYNC	E415	Unauthorized token
12	104	2024-06-11 13:40:12	INFO	E502	null
13	105	2024-06-09 14:12:15	VALIDATION	E400	null
14	100	2024-06-01 14:21:12	NETWORK	E301	Invalid request payload
15	109	2024-06-12 00:08:34	SUCCESS	E500	Unsupported media type
16	100	2024-06-12 10:52:06	METRIC	E504	Internal server error
17	109	2024-05-30 20:42:11	RETRY	E305	Password reset link expired
18	107	2024-06-10 02:12:46	CACHE	E403	Permission denied
19	107	2024-06-15 14:02:27	METRIC	E460	Queue backlog detected
20	104	2024-06-10 16:33:23	WARN	null	Gateway timeout
21	111	2024-06-04 18:24:19	QUEUE	null	Payment authorization failed
22	108	2024-06-16 07:01:10	NOTICE	E415	Resource not found
23	109	2024-06-07 23:51:23	EMERGENCY	E509	Service unavailable
24	105	2024-06-04 02:03:10	SUCCESS	E404	Invalid request payload
25	104	2024-06-07 06:12:59	ALERT	E599	Bad gateway
26	100	2024-06-11 09:02:25	EMERGENCY	E521	Permission denied
27	109	2024-06-02 02:57:24	NOTICE	null	Session expired
28	104	2024-06-08 22:26:30	APPLICATION	E301	Cache miss threshold exceeded
29	101	2024-06-06 07:08:34	ERROR	E302	Webhook timeout
30	100	2024-06-13 17:52:33	VALIDATION	E410	Dependency service error

Expected Output

error_code	error_count
E401	3
E500	3
E301	1
E400	1
E403	1
E404	1
E410	1
E415	1
E460	1
E502	1
E504	1
E521	1

Problem

Task

NovaCare wants a quick view of which application errors are happening most often in recent customer logs. Write a SQL query to find the most common error_code values from the last 7 days.

Requirements

Use only rows from the last 7 days based on log_time.
Exclude rows where error_code is NULL.
Return each error_code and its occurrence count as error_count.
Sort by error_count descending, then error_code ascending.

Table Definition

customer_logs

Column	Type	Description
log_id	INT	Primary key for each log row
customer_id	INT	Customer who generated the log
log_time	TIMESTAMP	Time the log was recorded
log_level	VARCHAR(20)	Severity level such as INFO or ERROR
error_code	VARCHAR(20)	Error code associated with the log
message	TEXT	Log message text

Sample Data

log_id	customer_id	log_time	log_level	error_code	message
1	101	2024-06-14 09:12:00	ERROR	E401	Unauthorized token
2	102	2024-06-13 11:45:00	ERROR	E500	Internal server error
3	103	2024-06-12 08:30:00	WARN	NULL	Slow response
4	104	2024-06-10 14:05:00	ERROR	E401	Unauthorized token
5	105	2024-06-09 16:20:00	ERROR	E404	Resource not found
6	106	2024-06-07 10:00:00	ERROR	E500	Internal server error

Expected Output

error_code	error_count
E401	3
E500	2
E404	1

Schema

customer_logs

Column	Type	Description
log_idPK	INT	Primary key for each log entry
customer_id	INT	Customer associated with the log
log_time	TIMESTAMP	Timestamp when the log was recorded
log_level	VARCHAR(20)	Severity level of the log event
error_code	VARCHAR(20)	Application error code if present
message	TEXT	Raw log message

customer_logs

log_id	customer_id	log_time	log_level	error_code	message
1	101	2024-06-14 09:12:00	ERROR	E401	Unauthorized token
2	102	2024-06-13 11:45:00	ERROR	E500	Internal server error
3	103	2024-06-12 08:30:00	WARN		Slow response
4	104	2024-06-10 14:05:00	ERROR	E401	Unauthorized token
5	105	2024-06-09 16:20:00	ERROR	E404	Resource not found
6	106	2024-06-07 10:00:00	ERROR	E500	Internal server error
7	107	2024-06-14 18:40:00	INFO
8	108	2024-06-11 07:55:00	ERROR	E401	Unauthorized token
9	109	2024-06-08 00:00:00	ERROR	E500	Internal server error
10	110	2024-06-01 13:15:00	ERROR	E404	Resource not found
11	108	2024-05-29 22:12:24	SYNC	E415	Unauthorized token
12	104	2024-06-11 13:40:12	INFO	E502	null
13	105	2024-06-09 14:12:15	VALIDATION	E400	null
14	100	2024-06-01 14:21:12	NETWORK	E301	Invalid request payload
15	109	2024-06-12 00:08:34	SUCCESS	E500	Unsupported media type
16	100	2024-06-12 10:52:06	METRIC	E504	Internal server error
17	109	2024-05-30 20:42:11	RETRY	E305	Password reset link expired
18	107	2024-06-10 02:12:46	CACHE	E403	Permission denied
19	107	2024-06-15 14:02:27	METRIC	E460	Queue backlog detected
20	104	2024-06-10 16:33:23	WARN	null	Gateway timeout
21	111	2024-06-04 18:24:19	QUEUE	null	Payment authorization failed
22	108	2024-06-16 07:01:10	NOTICE	E415	Resource not found
23	109	2024-06-07 23:51:23	EMERGENCY	E509	Service unavailable
24	105	2024-06-04 02:03:10	SUCCESS	E404	Invalid request payload
25	104	2024-06-07 06:12:59	ALERT	E599	Bad gateway
26	100	2024-06-11 09:02:25	EMERGENCY	E521	Permission denied
27	109	2024-06-02 02:57:24	NOTICE	null	Session expired
28	104	2024-06-08 22:26:30	APPLICATION	E301	Cache miss threshold exceeded
29	101	2024-06-06 07:08:34	ERROR	E302	Webhook timeout
30	100	2024-06-13 17:52:33	VALIDATION	E410	Dependency service error

Expected Output

error_code	error_count
E401	3
E500	3
E301	1
E400	1
E403	1
E404	1
E410	1
E415	1
E460	1
E502	1
E504	1
E521	1

Practice SQL

PostgreSQL

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

Up next

AFind Recent System Error CustomersEasy

Diagnose Inconsistent Customer ReportMedium

Monthly Customer Spend and RankMedium

Next question