O04Advancedmultiple CTEs · COALESCE

Customer health triage

As of the dataset snapshot, return every customer as customer_id, name, open_incidents, failed_deployments, and risk_score, where risk_score = 3 × open sev1 incidents + open non-sev1 incidents + 2 × failed deployments. Sort highest risk first, then customer_id.

Data available

Use these tables

3 tables · 3 relationships

Table 1

incidents

Operational incidents; unresolved rows have no resolved_at.

incident_idintegerPK
customer_idintegerFK
deployment_idintegerNULLFK
severitytext
opened_attimestamptz
resolved_attimestamptzNULL
root_causetextNULL

Table 2

deployments

Customer releases, including failures and rollbacks.

deployment_idintegerPK
customer_idintegerFK
engineer_idintegerFK
versiontext
statustext
deployed_attimestamptz

Table 3

customers

Organizations using the deployed platform.

customer_idintegerPK
nametext
industrytext
regiontext
tiertext
onboarded_attimestamptz

How the tables connect

deployments.customer_id=customers.customer_idincidents.customer_id=customers.customer_idincidents.deployment_id=deployments.deployment_id

Query editor

PostgreSQL

⌘↵ Run query

Result stream

Output

Run your query to inspect its result.

Contextual coach

Ask about this attempt

Run or check a query first. The coach only comments on a real error or grader mismatch.

Field notes

Hints & explanation

Try to solve it cold. Two progressive hints are available.