Creatures of Habit

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

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

Google

Problem

We log every cloud cost allocation by team and service, and we're looking at the teams with real footprints: those with more than 10 allocations on record. Going through those teams in alphabetical order, find each one's most-used fallback service, how many times it landed there, and how many different services the team spreads across in total.

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

Tables

cost_allocs

Columns: alloc_id INTEGER, team_name TEXT, svc_name TEXT, region TEXT, amount REAL, period TEXT, acct_id TEXT, category TEXT

Expected output

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.

Stage one: who makes the cut

Step 1: Filter teams by allocation volume

Group by team and keep only teams whose total allocation count clears the bar. The test depends on an aggregate over the whole group, so it belongs after grouping, not before.

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.

Step 3: Pick the top service per team

Number the services inside each team from most-allocated down, then 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.

The solution

WITH big_teams AS (
  SELECT team_name
  FROM cost_allocs
  GROUP BY team_name
  HAVING COUNT(*) > 10
),
svc_counts AS (
  SELECT team_name, svc_name, COUNT(*) AS cnt
  FROM cost_allocs
  WHERE team_name IN (SELECT team_name FROM big_teams)
  GROUP BY team_name, svc_name
),
breadth AS (
  SELECT team_name, COUNT(DISTINCT svc_name) AS svc_count
  FROM cost_allocs
  GROUP BY team_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 breadth b ON r.team_name = b.team_name
WHERE r.rn = 1
ORDER BY r.team_name

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.)