# The Ones We Watch > Only the services still under watch count. Rank them by where the errors gather. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem We run an observability platform, and the services worth tracking are the ones the team still monitors: every service with at least one alert wired up in alert_events. Among those, total up the error occurrences each has logged and list them from the busiest down to the quietest. ## Worked solution and explanation ### Strip the costume Under the observability story this is a semi-join feeding a grouped SUM. The skill being probed: can you scope err_tracks to the services the team actually monitors, then total each one's occurrences without letting the second table distort the math? Anyone can sum a column. Two things separate the seniors here: reading 'watched' as a membership test against alert_events, so a noisy service with no alert configured never sneaks in, and totaling the occurrence counts instead of counting error rows. ### The scope is a membership test A service is worth tracking only if it has at least one alert in alert_events. That is a question of existence, not a row multiplier, so express it as a membership test (IN a subquery, or EXISTS) that puts each service either in scope or out of it. The service worker logs 36 errors but has no alert wired up anywhere, so it is out. Miss the scope and worker's noise lands right in the middle of your standings. > **The fan-out trap** > > If you JOIN err_tracks to alert_events on svc_name, a service with several alerts duplicates every one of its error rows. payment-api and user-svc each have two alerts, so a plain SUM(count) after the join reports double their real totals. A semi-join asks only whether a match exists, counts each service once, and sidesteps the inflation entirely. ### Total occurrences, not rows Each err_tracks row carries a count of how many times that error fired, so 'the most errors' means the sum of those counts, not the number of rows. COUNT(*) would flatten every service to two and hide the real volume: search-api's two rows are worth 86 occurrences, gateway's two are worth 29. One gateway row has a null count; SUM quietly ignores it, leaving gateway at 29 rather than crashing or zeroing out. > **The tell** > > The count column is a planted distractor. A candidate who sums it, and who notices the null count without being prompted, is showing they read the data before writing SQL. Counting rows instead is the fast tell that they pattern-matched on the word 'errors' and never looked at what a row means. **Watched services by total error volume** ```sql SELECT e.svc_name AS svc_name, SUM(e.count) AS total_errors FROM err_tracks e WHERE e.svc_name IN ( SELECT a.svc_name FROM alert_events a ) GROUP BY e.svc_name ORDER BY total_errors DESC; ``` *The IN subquery scopes to monitored services without fan-out; SUM totals real occurrences and skips the null; the sort puts the busiest first.* #### Step 1: Scope to watched services Build the in-scope set: every svc_name that appears in alert_events. That set is payment-api, user-svc, search-api, gateway, notif-svc, db-primary, cache-01, and auth-svc. worker is deliberately excluded because it logs errors but has no alert configured, and that single exclusion is the whole point of the scope. #### Step 2: Total the occurrences Group the surviving err_tracks rows by svc_name and SUM the count column. search-api totals 86 (22 plus 64), user-svc 72, payment-api 58, auth-svc 43, and gateway 29 (its null-count row contributes nothing). notif-svc, db-primary, and cache-01 are in scope but log no errors, so they never reach the sum. #### Step 3: Order most to least Sort by total_errors descending so the busiest watched service leads. Here the totals are all distinct, so the ordering is unambiguous and search-api sits on top at 86. **Naive: JOIN then SUM** Joining alert_events fans out any service with several alerts, so payment-api and user-svc double their error rows and SUM overstates them. Skip the scope instead and worker's 36 errors quietly join the standings. Either way the numbers are wrong. **Correct: semi-join then SUM** A membership test scopes without fan-out, SUM(count) totals honest occurrences and tolerates the null, and worker stays out. The standings reflect only the services the team actually monitors. > **In production** > > The alert stream usually dwarfs the error table, so a membership test against an index on svc_name is far cheaper than materializing a fan-out join and de-duplicating afterward. It also mirrors how on-call reasons: is this even a service we monitor, yes or no, before anyone totals a single number. ## Common follow-up questions - Now return only the single busiest watched service, but if several tie for the top total, return all of them. How does the query change? _(Pushes them from a plain sort toward a ranking function and away from a blind LIMIT.)_ - Include watched services that logged zero errors, showing them with a total of 0. What do you change? _(Tests an outer join from the scope set with COALESCE instead of an inner membership filter.)_ - Break the totals out by severity while normalizing casing like ERROR and error into one bucket. What do you add? _(Extends the grouping and forces case normalization on a dirty column.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_ones_we_watch) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.