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 |