SQLMaxx
Sign in Start free
N049-E1 Tier 4 · Advanced · easy ecommerce · Brightlane

Return the total number of orders, the number of delivered orders, and the number of pending orders as a single row

Part of FILTER Clause on Aggregates in SQL

The problem

Brightlane's operations team is preparing an order status summary for the weekly review.

Write a query to return the total number of orders, the number of delivered orders, and the number of pending orders as a single row.

Assumptions:

  • The orders table has one row per order with a status.
  • The total count covers every order. The delivered count covers only orders with status = 'delivered'. The pending count covers only orders with status = 'pending'.

Output:

  • A single row with columns total_orders, delivered_orders, and pending_orders.
Schema · ecommerce 5 tables
categories
id integer
name text
parent_id? integer
products
id integer
name text
category_id integer
price numeric
stock_qty integer
attributes? jsonb
order_items
id integer
order_id integer
product_id integer
quantity integer
unit_price numeric
customers
id integer
name text
email text
city? text
country text
created_at timestamptz
is_active boolean
orders
id integer
customer_id integer
ordered_at timestamptz
status text
total_amount numeric
Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.
Worked solution Try it yourself first
Solution query
SELECT
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (
    WHERE
      status = 'delivered'
  ) AS delivered_orders,
  COUNT(*) FILTER (
    WHERE
      status = 'pending'
  ) AS pending_orders
FROM
  orders

The shape

Three counts over the same orders table, each restricted to a different rule. COUNT(*) with no filter counts every order; the two COUNT(*) FILTER (...) expressions each count only the orders matching their own condition. One pass over the table, three independent counts.

Clause by clause

  • SELECT COUNT(*) AS total_orders counts every row in orders. No condition attached, so every order contributes one.
  • COUNT(*) FILTER (WHERE status = 'delivered') AS delivered_orders counts only the orders whose status is 'delivered'. The FILTER clause sits next to the aggregate it constrains; rows where the condition is false are excluded from this count and this count only.
  • COUNT(*) FILTER (WHERE status = 'pending') AS pending_orders does the same for orders whose status is 'pending'. Pending and delivered are independent filters; an order excluded by one stays visible to the other and to the unfiltered count.
  • FROM orders reads the order records. No GROUP BY because the prompt asks for a single summary row, and aggregate functions without GROUP BY collapse the whole table into one row.

Why this and not COUNT(CASE WHEN status = 'delivered' THEN 1 END)

The CASE WHEN form produces the same numbers. FILTER is easier to scan when several conditional counts sit side by side in one SELECT list, because each condition reads on its own line right next to the aggregate it constrains. For three parallel counts like the ones the weekly review needs, FILTER keeps each one self-contained instead of nesting a per-row decision inside each function.

You practiced COUNT(*) FILTER (WHERE ...) for parallel conditional counts — three counts over the same record set, each restricted to its own condition, in a single pass.

How you actually get good at SQL

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.

A stack of SQL practice problem cards, the top card showing an employees table.
615 problems · 66 concepts

Real problems. Not toy examples.

615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.

A retro computer showing a SQL query marked correct with a green checkmark.
Instant AI feedback

Write a query. Know if it's right in one second.

No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.

A circular mastery progress dial filling from blue to green, the SQLMaxx diamond at its center.
Mastery tracking

Stop guessing whether you actually know it.

SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.

A SQL query editor circled by a blue return arrow with a clock, scheduled to come back for review.
Spaced review

Learn it once. Keep it for good.

Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.

Practice, feedback, mastery, review. That's the loop that turns reading into real skill.

Start free

No account, no credit card. Start solving in under a minute.