# Creatures of Habit

> Every big team has one service it can't quit.

Canonical URL: <https://datadriven.io/problems/creatures_of_habit>

Domain: SQL · Difficulty: hard · Seniority: L4 · Asked in: Google

## Problem

An analyst auditing `cost_allocs` finds one team logged under several letter casings ('DATA-ENG' and 'data-eng' are the same team), and the `SQS` and `EC2` lines are billed through a separate ledger, so they stay out of the audit entirely. Going alphabetically through the teams left with more than 10 allocations, report each team's most-used service and how many allocations landed there, taking the alphabetically first service when several share the top count, along with how many different services the team spreads across.

## Tables

### cost_allocs

- alloc_id INTEGER
- team_name TEXT
- svc_name TEXT
- region TEXT
- amount REAL
- period TEXT
- acct_id TEXT
- category TEXT

Sample rows (10 of 200):

| alloc_id | team_name | svc_name | region | amount | period | acct_id | category |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 129 | data-eng | S3 | us-west-2 | 83.21 | 2026-02 | acct-101 | storage |
| 158 | ml-team | RDS | eu-west-1 | 156.42 | 2026-03 | acct-102 | network |
| 187 | frontend | Lambda | ap-south-1 | 229.63 | 2026-04 | acct-103 | database |
| 216 | backend | BigQuery | us-central1 | -120.5 | 2026-05 | acct-104 | ml |
| 245 | devops | CloudFront | europe-west1 | 376.05 | 2026-06 | acct-105 | other |
| 274 | security | EKS | ap-southeast-1 | 449.26 | 2026-07 | acct-106 | compute |
| 303 | mobile | SQS | eu-central-1 | 522.47 | 2026-08 | acct-107 | storage |
| 332 | Platform | EC2 | us-east-1 | 595.68 | 2026-09 | NULL | network |
| 361 | DATA-ENG | S3 | us-west-2 | 668.89 | 2026-01 | acct-109 | database |
| 390 | platform | RDS | eu-west-1 | 742.1 | 2026-02 | acct-110 | ml |

## Expected output

First 5 of 8 rows:

| team_name | svc_name | cnt | svc_count |
| --- | --- | --- | --- |
| backend | BigQuery | 3 | 6 |
| data-eng | Lambda | 6 | 6 |
| frontend | CloudFront | 3 | 6 |
| mobile | EKS | 3 | 6 |
| platform | RDS | 6 | 6 |

## Worked solution and explanation

### What this really is

Strip off the cloud-cost costume and this is a per-group argmax reported next to a per-group breadth. For each qualifying team you need the mode of `svc_name` (the service it lands on most) and, separately, how many DIFFERENT services it touches. The obvious read is a global leaderboard with ORDER BY count then LIMIT 1, which hands back one service for the entire company and silently ignores the per-team requirement. And the two counts pull in opposite directions: 'most often' is a count of rows, while breadth is a count of distinct services, so reusing one for the other quietly inflates the answer.

> **Trick to solving**
>
> This is two aggregations bolted onto one filter. A volume filter decides WHO is in (teams with enough allocations on record). Then, inside those teams, one count finds each team's favorite service (rows per team+service, ranked) and a second, DISTINCT count measures how broadly the team spreads. Keep the two counts apart and neither can contaminate the other.

---

### Stage one: who makes the cut

#### Step 1: Fold the casings, then filter teams by volume

Before anything is counted, collapse `team_name` with `LOWER(team_name)` so 'DATA-ENG' and 'data-eng' become one team. In the same pass, drop the 'SQS' and 'EC2' lines with a `NOT IN` list, because they belong to another ledger and must not count toward volume, the favorite service, or breadth. Skip the fold and the merged team's allocations split across two rows, each half may miss the bar, and the top service is picked from half the evidence. Then group by the folded name and keep only teams whose remaining allocation count clears the bar. That test depends on an aggregate over the whole group, so it belongs after grouping.

> **Two spellings, one team**
>
> Grouping on raw `team_name` reports 'DATA-ENG' and 'data-eng' as rivals. Fold the case once, in the first CTE, so every later count, filter and sort sees a single team.

> **WHERE runs too early**
>
> The volume test depends on COUNT(*), which does not exist yet when WHERE runs (WHERE filters individual rows before grouping). It has to live in HAVING. Reaching for WHERE here is the single most common way this stage is written wrong.

### Stage two: the habit and the breadth

#### Step 2: Two counts, kept apart

For the qualifying teams, count rows per (team, service) to get each service's within-team frequency. Separately, count DISTINCT services per team to get breadth. COUNT(*) here answers 'how often'; COUNT(DISTINCT `svc_name`) answers 'how many different'. A team with twenty allocations spread over eight services has a frequency of twenty rows but a breadth of eight, and swapping one for the other reports breadth as twenty.

> **COUNT vs COUNT DISTINCT**
>
> COUNT(`svc_name`) and COUNT(DISTINCT `svc_name`) look almost identical and read almost the same in English, but one tallies allocation rows and the other tallies services. The breadth column is the one place the DISTINCT is load-bearing; drop it and every big team's service count balloons to its row count.

#### Step 3: Pick the top service per team

Restrict the per-service counts to the qualifying teams with `team_name IN (SELECT team_name FROM big_teams)`, then number the services inside each team from most-allocated down and keep the top one. `ROW_NUMBER` partitioned by team gives exactly one winner per team; a global ORDER BY plus LIMIT would give exactly one winner total. The tie-break on `svc_name` inside the ORDER BY makes the pick deterministic when several services share the top count.

**Global mode (wrong)**

Count per service across all qualifying teams, ORDER BY count DESC LIMIT 1. Returns one service for the whole company and drops every team but the accidental winner.

**Per-team mode (right)**

Count per (team, service), then `ROW_NUMBER`() OVER (PARTITION BY team ORDER BY count DESC, `svc_name`). One row per team, each showing that team's own favorite plus its breadth.

> **Interviewers watch for**
>
> A window function ranking pre-aggregated counts is both faster and clearer than a correlated subquery that recomputes each team's max on the fly. Reaching for the window here, pre-shrinking the data with GROUP BY before ranking, and computing breadth as a distinct count rather than a row count are the tells that you have done this at scale.

### The solution

**Folded team names, excluded ledger lines, volume filter, per-team mode, and a separate breadth**

```sql
WITH team_rows AS (
  SELECT LOWER(team_name) AS team_name, svc_name
  FROM cost_allocs
  WHERE svc_name NOT IN ('SQS', 'EC2')
),
big_teams AS (
  SELECT team_name
  FROM team_rows
  GROUP BY team_name
  HAVING COUNT(*) > 10
),
team_breadth AS (
  SELECT team_name, COUNT(DISTINCT svc_name) AS svc_count
  FROM team_rows
  GROUP BY team_name
),
svc_counts AS (
  SELECT team_name, svc_name, COUNT(*) AS cnt
  FROM team_rows
  WHERE team_name IN (SELECT team_name FROM big_teams)
  GROUP BY team_name, svc_name
),
ranked AS (
  SELECT team_name, svc_name, cnt,
         ROW_NUMBER() OVER (PARTITION BY team_name ORDER BY cnt DESC, svc_name) AS rn
  FROM svc_counts
)
SELECT r.team_name, r.svc_name, r.cnt, b.svc_count
FROM ranked r
JOIN team_breadth b ON r.team_name = b.team_name
WHERE r.rn = 1
```

*`LOWER(team_name)` merges the casings before any count, the `NOT IN` list removes the separately billed lines, the `IN` subquery keeps only qualifying teams, and `COUNT(DISTINCT svc_name)` keeps breadth honest.*

> **Why this stays cheap at scale**
>
> On 20M rows the win is that both aggregations shrink the data before the ranking runs: the per-pair counts and the per-team distinct count collapse millions of allocations into a handful of tuples per team, so `ROW_NUMBER` sorts almost nothing. The plan never materializes a per-row ranking over the full table.

---

## Common follow-up questions

- If a team's two services are tied for most allocations, how would you decide to return one, both, or all tied services? _(Tests whether the candidate keeps a deterministic tie-break and understands RANK vs `DENSE_RANK` vs `ROW_NUMBER`.)_
- Where would using COUNT(*) instead of COUNT(DISTINCT `svc_name`) change your answer, and where would it not? _(Tests grasp of when a distinct count matters versus a plain row count.)_
- With sixty teams and hundreds of services over 20M rows, what indexing or partitioning would keep the per-pair counts fast? _(Tests scaling intuition on a high-cardinality grouping key.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/creatures_of_habit)
- [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.