Service Scorecard

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Problem

Services that never fired an alert still belong on the on-call lead's scorecard, with an alert count of zero. For every service with pods in pod_assignments, she wants how many pod assignments it has and how many alerts in alert_events fired for it.

Deploys vs. alerts. One row per service tells the whole story.

Tables

pod_assignments

Columns: assignment_id INTEGER, node_id INTEGER, pod_id INTEGER, svc_name TEXT, assigned_at TEXT, status TEXT

alert_events

Columns: alert_id INTEGER, svc_name TEXT, severity TEXT, status TEXT, fired_at TEXT, ack_by TEXT, resolved TEXT

Expected output

Worked solution and explanation

This is two independent one-to-many relationships hanging off svc_name, dressed up as an on-call scorecard. A service has dozens of rows in pod_assignments and, if it alerts at all, 24 or 26 in alert_events. The two tables share nothing but the name. Anyone can write the join. What separates candidates is collapsing the assignments to one row per service before the alerts touch them, and counting alert_id rather than rows. Join the raw tables and both columns become assignments times alerts. Count rows and analytics reports an alert it never fired.

Getting to the answer

Step 1: Collapse pod_assignments to one row per service

The assignment_counts CTE groups pod_assignments by svc_name with COUNT(*). After this, a service's assignment total is one number on one row, and nothing downstream can multiply it.

Step 2: Attach alerts to that row with a LEFT JOIN

Joining alert_events to the per-service row emits one row per alert for an alerting service, and one null-padded row for analytics and ml-serving, which have pods but no alerts. An inner join would silently drop those two.

Step 3: Count alert.alert_id, not *

COUNT(alert.alert_id) skips the null on a quiet service's padded row, so it scores 0. Grouping by svc.assignment_count alongside svc.svc_name costs nothing, because the count is constant within a service.

WITH assignment_counts AS (
    SELECT svc_name, COUNT(*) AS assignment_count
    FROM pod_assignments
    GROUP BY svc_name
)
SELECT svc.svc_name, svc.assignment_count, COUNT(alert.alert_id) AS alert_count
FROM assignment_counts svc
LEFT JOIN alert_events alert ON svc.svc_name = alert.svc_name
GROUP BY svc.svc_name, svc.assignment_count

Raw join, one aggregation

Every assignment row meets every alert row for its service, so COUNT(*) reports assignments times alerts as the assignment total. There is no way back to either true number from that grain.

Aggregate first, then join

assignment_counts holds one row per service, so the join fans out only over alerts, and assignment_count rides along unchanged.

SELECT asg.svc_name,
       COUNT(*) AS assignment_count,
       COUNT(alert.alert_id) AS alert_count
FROM pod_assignments asg
LEFT JOIN alert_events alert ON asg.svc_name = alert.svc_name
GROUP BY asg.svc_name

Common follow-up questions

• Add a third column counting rows in `svc_health` per service. How do you keep all three counts honest? (Pre-aggregating every side to the service grain before combining.)
• The scorecard must also show services that fired alerts but have no pods (`db-primary`, `cache-01`). What changes? (Keeping unmatched rows from both sides and choosing which `svc_name` to report.)
• Only alerts from the last 30 days should count. Where does that filter go so quiet services still appear? (Filtering the alert side in the join condition or a CTE, not in a `WHERE` after the outer join.)