SQL Window Ranking Per Host | Dataford Interview Questions

Write a SQL query using window functions to rank security alerts by severity within each host and return the top alert per host at ReliaQuest.

Use the security_alerts table. Exclude alerts without a host. For equal severities, select the alert with the lower alert_id.

Output

One row per host, containing host_id, alert_id, severity, alert_type, and severity_rank.
Include only the highest-severity alert for each host, with ties resolved by the lower alert_id.
Order by host_id ascending.

security_alerts

Column	Type	Description
alert_idPK	INT	Unique identifier for the security alert
host_id	VARCHAR(50)	Host associated with the alert
severity	INT	Alert severity score, where higher values indicate greater severity
alert_type	VARCHAR(100)	Classification of the security alert
status	VARCHAR(30)	Current handling status of the alert

Your question is SQL Window Ranking Per Host. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Write a SQL query using window functions to rank security alerts by severity within each host and return the top alert per host at ReliaQuest.

Use the security_alerts table. Exclude alerts without a host. For equal severities, select the alert with the lower alert_id.

One row per host, containing host_id, alert_id, severity, alert_type, and severity_rank.
Include only the highest-severity alert for each host, with ties resolved by the lower alert_id.
Order by host_id ascending.

security_alerts

Column	Type	Description
alert_idPK	INT	Unique identifier for the security alert
host_id	VARCHAR(50)	Host associated with the alert
severity	INT	Alert severity score, where higher values indicate greater severity
alert_type	VARCHAR(100)	Classification of the security alert
status	VARCHAR(30)	Current handling status of the alert

Your question is SQL Window Ranking Per Host. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.