Presence vs. Participation

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

L3

Asked in

Meta

Problem

The platform team is reviewing the machines in the us-east-1 region and wants to see how hard each one is working. For every node in us-east-1, list its hostname and CPU load, and label it 'busy' when CPU is at 50 percent or higher and 'idle' otherwise. Alongside each node, show the current number of active user accounts so demand sits next to the fleet at a glance.

Being in the region and being active are two very different things.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

infra_nodes

Columns: node_id INTEGER, hostname TEXT, region TEXT, node_type TEXT, cpu_pct REAL, mem_pct REAL, status TEXT

Expected output

Worked solution and explanation

What this really tests

This is a per-row classification with a fleet-wide number bolted onto every row, dressed up as a capacity check. The skill being probed: can you label each infra_nodes row inline AND carry the active-user total next to it, when the two facts live in tables that share no key? Anyone can filter to us-east-1. The trick is that the busy/idle verdict is a CASE on each node's own cpu_pct, while the active-user count is an uncorrelated scalar subquery, so there is nothing to join and nothing to group. Reach for a JOIN and you fan every node out by every active user; reach for a GROUP BY and you hand back one summary row instead of one per machine.

Walk it through

Step 1: See that there is no join

users and infra_nodes share no meaningful key: a user is not deployed on a node. If you join them, each us-east-1 node row multiplies by the number of active users and every count downstream is wrong. Say it out loud in an interview: these tables do not relate, so the active-user figure has to come in as a scalar subquery, not a join.

Step 2: Filter the region and label each node

Scope the listing with WHERE region = 'us-east-1', then label each node with CASE WHEN cpu_pct >= 50 THEN 'busy' ELSE 'idle' END. The boundary is inclusive, so a node sitting at exactly 50 is 'busy'. Writing > 50 here silently flips the label on every 50 percent node, which is the classic off-by-one an interviewer looks for.

Step 3: Carry the active-user total with a scalar subquery

Attach demand with (SELECT COUNT(*) FROM users WHERE account_status = 'active') as its own column. It is uncorrelated, so the engine computes it once and repeats the value on every node row. The account_status = 'active' filter is exact: 'suspended', 'pending_verification' and 'inactive' are not active, and counting them inflates the number.

The solution

SELECT
    hostname,
    cpu_pct,
    CASE WHEN cpu_pct >= 50 THEN 'busy' ELSE 'idle' END AS load_state,
    (SELECT COUNT(*) FROM users WHERE account_status = 'active') AS active_users
FROM infra_nodes
WHERE region = 'us-east-1'

Common follow-up questions

• What changes if the active-user count should be specific to each node's region rather than a single global number? (Tests whether the candidate reaches for a correlated subquery or a windowed COUNT when the aggregate must vary by the outer row. The active-user total is global today, but per-region demand would need correlation.)
• Add a middle 'warm' tier for nodes between 30 and 50 percent CPU. How do the CASE branches and their boundaries change? (Tests CASE fluency and boundary reasoning. A third tier forces them to order the WHEN branches correctly and decide where the inclusive boundaries fall.)
• If this were embedded in a much larger query, would the active-user subquery be re-evaluated per row, and how would you guarantee it runs only once? (Tests understanding that an uncorrelated scalar subquery is evaluated once. Some older engines re-run it per row; the candidate should know how to hoist it or when the planner already does.)