Diagnose Client Issue Signals in Health Catalyst

Task

You are given usage, data quality, and support data from a Health Catalyst client environment. Write a PostgreSQL query that helps you investigate whether a reported issue is more likely caused by the product, the source data, or the way the tool is being used. Your query should return one row per client and surface the strongest issue signal based on recent activity, data quality failures, and support history.

Schema

table	column	type	description
client_issues	client_id	INT	Client identifier
client_issues	issue_date	DATE	Date the issue was reported
client_issues	issue_type	VARCHAR(50)	Reported issue category
client_issues	severity	INT	Severity score from 1 to 5
tool_usage_events	usage_id	INT	Event identifier
tool_usage_events	client_id	INT	Client identifier
tool_usage_events	event_date	DATE	Usage event date
tool_usage_events	action_name	VARCHAR(100)	Action taken in the Health Catalyst tool
tool_usage_events	success_flag	BOOLEAN	Whether the action completed successfully
data_quality_checks	check_id	INT	Check identifier
data_quality_checks	client_id	INT	Client identifier
data_quality_checks	check_date	DATE	Check date
data_quality_checks	check_name	VARCHAR(100)	Data validation rule name
data_quality_checks	failed_flag	BOOLEAN	Whether the check failed
support_cases	case_id	INT	Support case identifier
support_cases	client_id	INT	Client identifier
support_cases	case_date	DATE	Case open date
support_cases	case_reason	VARCHAR(100)	Reason logged by support
support_cases	resolved_flag	BOOLEAN	Whether the case was resolved

Sample data

client_id	issue_date	issue_type	severity
101	2024-05-10	dashboard_error	5
102	2024-05-11	slow_refresh	3
103	2024-05-12	incorrect_metric	4

usage_id	client_id	event_date	action_name	success_flag
1	101	2024-05-09	open_dashboard	true
2	101	2024-05-10	run_report	false
3	102	2024-05-11	open_dashboard	true
4	103	2024-05-12	export_csv	true

check_id	client_id	check_date	check_name	failed_flag
11	101	2024-05-10	missing_claims_rows	true
12	102	2024-05-10	late_arriving_claims	false
13	103	2024-05-12	duplicate_encounters	true

case_id	client_id	case_date	case_reason	resolved_flag
21	101	2024-05-10	product_defect	false
22	102	2024-05-11	training_question	true
23	103	2024-05-12	data_mapping	false

Expected output

client_id	issue_date	issue_type	issue_signal	signal_score
101	2024-05-10	dashboard_error	product	3
102	2024-05-11	slow_refresh	usage	2
103	2024-05-12	incorrect_metric	data	3

Problem

Task

Schema

table	column	type	description
client_issues	client_id	INT	Client identifier
client_issues	issue_date	DATE	Date the issue was reported
client_issues	issue_type	VARCHAR(50)	Reported issue category
client_issues	severity	INT	Severity score from 1 to 5
tool_usage_events	usage_id	INT	Event identifier
tool_usage_events	client_id	INT	Client identifier
tool_usage_events	event_date	DATE	Usage event date
tool_usage_events	action_name	VARCHAR(100)	Action taken in the Health Catalyst tool
tool_usage_events	success_flag	BOOLEAN	Whether the action completed successfully
data_quality_checks	check_id	INT	Check identifier
data_quality_checks	client_id	INT	Client identifier
data_quality_checks	check_date	DATE	Check date
data_quality_checks	check_name	VARCHAR(100)	Data validation rule name
data_quality_checks	failed_flag	BOOLEAN	Whether the check failed
support_cases	case_id	INT	Support case identifier
support_cases	client_id	INT	Client identifier
support_cases	case_date	DATE	Case open date
support_cases	case_reason	VARCHAR(100)	Reason logged by support
support_cases	resolved_flag	BOOLEAN	Whether the case was resolved

Sample data

client_id	issue_date	issue_type	severity
101	2024-05-10	dashboard_error	5
102	2024-05-11	slow_refresh	3
103	2024-05-12	incorrect_metric	4

usage_id	client_id	event_date	action_name	success_flag
1	101	2024-05-09	open_dashboard	true
2	101	2024-05-10	run_report	false
3	102	2024-05-11	open_dashboard	true
4	103	2024-05-12	export_csv	true

check_id	client_id	check_date	check_name	failed_flag
11	101	2024-05-10	missing_claims_rows	true
12	102	2024-05-10	late_arriving_claims	false
13	103	2024-05-12	duplicate_encounters	true

case_id	client_id	case_date	case_reason	resolved_flag
21	101	2024-05-10	product_defect	false
22	102	2024-05-11	training_question	true
23	103	2024-05-12	data_mapping	false

Expected output

client_id	issue_date	issue_type	issue_signal	signal_score
101	2024-05-10	dashboard_error	product	3
102	2024-05-11	slow_refresh	usage	2
103	2024-05-12	incorrect_metric	data	3

Problem

Task

Schema

table	column	type	description
client_issues	client_id	INT	Client identifier
client_issues	issue_date	DATE	Date the issue was reported
client_issues	issue_type	VARCHAR(50)	Reported issue category
client_issues	severity	INT	Severity score from 1 to 5
tool_usage_events	usage_id	INT	Event identifier
tool_usage_events	client_id	INT	Client identifier
tool_usage_events	event_date	DATE	Usage event date
tool_usage_events	action_name	VARCHAR(100)	Action taken in the Health Catalyst tool
tool_usage_events	success_flag	BOOLEAN	Whether the action completed successfully
data_quality_checks	check_id	INT	Check identifier
data_quality_checks	client_id	INT	Client identifier
data_quality_checks	check_date	DATE	Check date
data_quality_checks	check_name	VARCHAR(100)	Data validation rule name
data_quality_checks	failed_flag	BOOLEAN	Whether the check failed
support_cases	case_id	INT	Support case identifier
support_cases	client_id	INT	Client identifier
support_cases	case_date	DATE	Case open date
support_cases	case_reason	VARCHAR(100)	Reason logged by support
support_cases	resolved_flag	BOOLEAN	Whether the case was resolved

Sample data

client_id	issue_date	issue_type	severity
101	2024-05-10	dashboard_error	5
102	2024-05-11	slow_refresh	3
103	2024-05-12	incorrect_metric	4

usage_id	client_id	event_date	action_name	success_flag
1	101	2024-05-09	open_dashboard	true
2	101	2024-05-10	run_report	false
3	102	2024-05-11	open_dashboard	true
4	103	2024-05-12	export_csv	true

check_id	client_id	check_date	check_name	failed_flag
11	101	2024-05-10	missing_claims_rows	true
12	102	2024-05-10	late_arriving_claims	false
13	103	2024-05-12	duplicate_encounters	true

case_id	client_id	case_date	case_reason	resolved_flag
21	101	2024-05-10	product_defect	false
22	102	2024-05-11	training_question	true
23	103	2024-05-12	data_mapping	false

Expected output

client_id	issue_date	issue_type	issue_signal	signal_score
101	2024-05-10	dashboard_error	product	3
102	2024-05-11	slow_refresh	usage	2
103	2024-05-12	incorrect_metric	data	3

Problem

Task

Schema

table	column	type	description
client_issues	client_id	INT	Client identifier
client_issues	issue_date	DATE	Date the issue was reported
client_issues	issue_type	VARCHAR(50)	Reported issue category
client_issues	severity	INT	Severity score from 1 to 5
tool_usage_events	usage_id	INT	Event identifier
tool_usage_events	client_id	INT	Client identifier
tool_usage_events	event_date	DATE	Usage event date
tool_usage_events	action_name	VARCHAR(100)	Action taken in the Health Catalyst tool
tool_usage_events	success_flag	BOOLEAN	Whether the action completed successfully
data_quality_checks	check_id	INT	Check identifier
data_quality_checks	client_id	INT	Client identifier
data_quality_checks	check_date	DATE	Check date
data_quality_checks	check_name	VARCHAR(100)	Data validation rule name
data_quality_checks	failed_flag	BOOLEAN	Whether the check failed
support_cases	case_id	INT	Support case identifier
support_cases	client_id	INT	Client identifier
support_cases	case_date	DATE	Case open date
support_cases	case_reason	VARCHAR(100)	Reason logged by support
support_cases	resolved_flag	BOOLEAN	Whether the case was resolved

Sample data

client_id	issue_date	issue_type	severity
101	2024-05-10	dashboard_error	5
102	2024-05-11	slow_refresh	3
103	2024-05-12	incorrect_metric	4

usage_id	client_id	event_date	action_name	success_flag
1	101	2024-05-09	open_dashboard	true
2	101	2024-05-10	run_report	false
3	102	2024-05-11	open_dashboard	true
4	103	2024-05-12	export_csv	true

check_id	client_id	check_date	check_name	failed_flag
11	101	2024-05-10	missing_claims_rows	true
12	102	2024-05-10	late_arriving_claims	false
13	103	2024-05-12	duplicate_encounters	true

case_id	client_id	case_date	case_reason	resolved_flag
21	101	2024-05-10	product_defect	false
22	102	2024-05-11	training_question	true
23	103	2024-05-12	data_mapping	false

Expected output

client_id	issue_date	issue_type	issue_signal	signal_score
101	2024-05-10	dashboard_error	product	3
102	2024-05-11	slow_refresh	usage	2
103	2024-05-12	incorrect_metric	data	3

Interview Guides

Problem

Task

Schema

Sample data

Expected output

Problem

Task

Schema

Sample data

Expected output

Diagnose Client Issue Signals in Health Catalyst

Problem

Task

Schema

Sample data

Expected output

Problem

Task

Schema

Sample data

Expected output