SELF-GUIDED BA PRACTICE Packages available · Access after confirmed paymentSee packages ↗

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.

Six invented requests; queue 1 = Posters, 2 = Cards, 3 = Manuals
RequestQueueStatus
R011OPEN
R021OPEN
R031CLOSED
R042CANCELLED
R052OPEN
R063CLOSED

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.

The studio_queues and reprint_requests tables linked by queue_idTable studio_queues with columns queue_id (PK), queue_name. Table reprint_requests with columns request_id (PK), queue_id (FK), status. studio_queues.queue_id links to reprint_requests.queue_id: 1 to 0 or many.studio_queuesqueue_idPKqueue_namereprint_requestsrequest_idPKqueue_idFKstatus10 or manystudio_queues.queue_id links to reprint_requests.queue_id
One row means one request. Each queue can have many requests, or none, which is exactly the case the count must survive.
Diagram in words
  • studio_queues: queue_id (PK), queue_name
  • reprint_requests: request_id (PK), queue_id (FK), status
  • One row in studio_queues can match 0 or many rows in reprint_requests through queue_id.

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.

Show the complete example data setup
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;
Expected result, checked with SQLite on these six rows
QueueOpen requests
Posters2
Cards1
Manuals0

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.

As written

LEFT JOIN, OPEN in ON, COUNT(r.request_id)

  • Posters 2
  • Cards 1
  • Manuals 0

COUNT(*) instead

The retained empty row is counted

  • Posters 2
  • Cards 1
  • Manuals 1 (wrong)

OPEN moved to WHERE

The unmatched queue is filtered out

  • Posters 2
  • Cards 1
  • Manuals missing
Three similar queries, three answers. Only the first keeps Manuals at zero.

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.

Silent clip, about 20 seconds The same habit in the BA Lab: run a query against fictional company tables and read the automatic check before trusting the number. Recorded with a synthetic test account.

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. See packages and prices on the pricing page.

Glossary terms in this guide

Browse the full glossary

CHECK BEFORE CONTINUING

Keep your work safe