# Presence vs. Participation > Being in the region and being active are two very different things. Canonical URL: 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. ## Tables ### users - user_id INTEGER - username TEXT - email TEXT - signup_date TEXT - account_status TEXT - age_bucket TEXT Sample rows (10 of 200): | user_id | username | email | signup_date | account_status | age_bucket | | --- | --- | --- | --- | --- | --- | | 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 | | 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 | | 294 | amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 | | 391 | arjun | arjun@example.com | 2025-05-05 | active | 55-64 | | 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ | | 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL | | 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 | | 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 | | 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 | | 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 | ### infra_nodes - node_id INTEGER - hostname TEXT - region TEXT - node_type TEXT - cpu_pct REAL - mem_pct REAL - status TEXT Sample rows (10 of 200): | node_id | hostname | region | node_type | cpu_pct | mem_pct | status | | --- | --- | --- | --- | --- | --- | --- | | 1037 | web-02 | us-west-2 | memory | 13 | 19 | stopped | | 1074 | api-01 | eu-west-1 | storage | 26 | 38 | draining | | 1111 | api-02 | sa-east-1 | gpu | 39 | 57 | offline | | 1148 | db-primary | eu-central-1 | general | 52 | 76 | Running | | 1185 | db-replica | us-east-1 | compute | 65 | 95 | STOPPED | | 1222 | cache-01 | us-west-2 | memory | 78 | 14 | running | | 1259 | worker-01 | eu-west-1 | Storage | NULL | NULL | stopped | | 1296 | worker-02 | ap-south-1 | gpu | 4 | 52 | draining | | 1333 | gpu-node-01 | eu-central-1 | general | 17 | 71 | offline | | 1370 | WEB-03 | us-east-1 | compute | 30 | 90 | Running | ## Expected output First 5 of 40 rows: | hostname | cpu_pct | load_state | active_users | | --- | --- | --- | --- | | db-replica | 65 | busy | 50 | | WEB-03 | 30 | idle | 50 | | api-02 | 95 | busy | 50 | | worker-02 | 60 | busy | 50 | | web-02 | 25 | idle | 50 | ## 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. > **The label is per row, the count is global** > > When each output row needs its OWN label plus the SAME global number, write a per-row `CASE` for the label and drop the aggregate into a scalar subquery in the SELECT list. The subquery is evaluated once and reused for every row. No JOIN, no GROUP BY. --- ### 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 **Per-row label plus an uncorrelated scalar subquery** ```sql 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' ``` > **Cost Analysis** > > The outer query is one filtered scan of `infra_nodes` (5K rows, and only the `us-east-1` slice survives the WHERE). The scalar subquery is a single filtered `COUNT(*)` over `users` (500K rows, 128 MB), run ONCE because it does not reference the outer row; an index on `account_status` would let the planner do an index-only count. No join buffers, no sort, no group state. Expected plan: Scan(Filter) on `infra_nodes`, with a one-shot Aggregate(Filter(Scan)) on users evaluated a single time. > **Interviewers Watch For** > > The tell is whether you write a JOIN. There is no key between `users` and `infra_nodes`, so joining signals you matched on table names instead of reading the prompt. Strong candidates name the trap first ('these do not share a key, so the count comes in as a scalar subquery') and keep the output at one row per node instead of collapsing it with a `GROUP BY`. > **Common Pitfall** > > Two mistakes dominate here. First, `GROUP BY region` to get the count, which throws away the per-node rows the task asked for. Second, `cpu_pct > 50` instead of `>= 50`, which mislabels every node sitting exactly on the boundary. A distant third is counting all users rather than only `account_status = 'active'`. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/presence_vs_participation) - [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.