FREE SQL PRACTICE FOR BUSINESS ANALYSTS

SQL Practice for Business Analysts: Count Open Requests, Including Zeroes

A query is useful only when its result answers the intended business question. In this original fictional exercise, Lumen Print Studio wants an open-reprint-request count for every queue, including queues with none. Try calculating the answer yourself before inspecting the SQL.

This self-guided BA exercise uses invented operational records. It makes no claim about a real studio, customer service performance or learner competence.

1. Agree what one row means

The queue table lists three available queues. The request table contains one row per request, with a unique non-null request ID and its current status. A request can cover several printed items, so counting requests does not count sheets, cost or workload hours.

The proposed metric is: count requests whose current status is OPEN, grouped by queue, and show zero for a queue without open requests. Ask the operations owner whether paused requests count as open before adding a new status to this rule.

2. Build the small SQLite example

Use a scratch SQLite database. The temporary tables below contain only the fictional example. Run this setup once in that session, then run the SELECT that follows.

CREATE TEMP TABLE studio_queues (
  queue_id INTEGER PRIMARY KEY, queue_name TEXT
);
CREATE TEMP TABLE reprint_requests (
  request_id TEXT PRIMARY KEY NOT NULL,
  queue_id INTEGER, status TEXT
);
INSERT INTO studio_queues VALUES
  (1, 'Posters'), (2, 'Cards'), (3, 'Manuals');
INSERT INTO reprint_requests VALUES
  ('R01', 1, 'OPEN'), ('R02', 1, 'OPEN'),
  ('R03', 1, 'CLOSED'), ('R04', 2, 'CANCELLED'),
  ('R05', 2, 'OPEN'), ('R06', 3, 'CLOSED');

3. Inspect the attempted query

SELECT q.queue_name,
       COUNT(r.request_id) AS open_requests
FROM studio_queues AS q
LEFT JOIN reprint_requests AS r
  ON r.queue_id = q.queue_id AND r.status = 'OPEN'
GROUP BY q.queue_id, q.queue_name
ORDER BY q.queue_id;

4. Explain why the zero survives

Start from the queue list because every queue must appear. LEFT JOIN retains a queue even when no open request matches. Keeping the status condition inside the join allows Manuals to remain in the result.

COUNT(r.request_id) counts matching request IDs and ignores the null supplied for an unmatched queue. Using COUNT(*) would incorrectly count that retained row as one. Moving the OPEN condition into WHERE would remove the unmatched Manuals row.

The grouping includes the queue ID as well as its name. Two different queues with the same display name should not silently become one group.

5. Test a change and state the limits

If R05 changes to CLOSED, the expected counts become Posters 2, Cards 0 and Manuals 0. That variation was also checked with SQLite. An interface still needs its own test to establish that it displays the result correctly.

This is a current-status snapshot. It cannot answer how many requests were open last week, whether requests met a deadline or why reprints occurred. Orphaned queue IDs and missing statuses need data-quality checks; this query does not repair them.

Try adding a fourth queue with no requests. Predict its result before running the query. Then explain why a zero count is different from missing data.

Connect the metric to a testable requirement, practise its explanation with the interview guide, or explore the worked project. Find all BA resources and the free demo. Sales remain closed.