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

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.

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

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

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.

The solution

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

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