Brightlane's revenue team is identifying customers with significant high-value delivery activity.
Write a query to return every customer ID, their total order count, and the number of orders that are both delivered and have total_amount greater than $200.
Assumptions:
- The
orderstable has one row per order with acustomer_id, astatus, and atotal_amount. - Each
customer_idwith at least one order should appear once. - For each customer, the total count covers every order. The high-value-delivered count covers only orders with both
status = 'delivered'andtotal_amountgreater than$200.
Output:
- One row per customer with at least one order, with columns
customer_id,total_orders, andhigh_value_delivered.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
customer_id,
COUNT(*) AS total_orders,
COUNT(*) FILTER (
WHERE
status = 'delivered'
AND total_amount > 200
) AS high_value_delivered
FROM
orders
GROUP BY
customer_idThe shape
A single FILTER clause can carry a multi-condition boolean. FILTER (WHERE status = 'delivered' AND total_amount > 200) restricts the COUNT to orders that satisfy both rules at once, inside each customer's partition. One aggregate, one filter, two predicates joined by AND.
Clause by clause
SELECT customer_id, COUNT(*) AS total_orders, COUNT(*) FILTER (WHERE status = 'delivered' AND total_amount > 200) AS high_value_deliveredreturns the customer, their total order count, and the count of orders that are both delivered and above$200. The unfiltered total still covers every order in the partition; the filtered count only sees rows where both conditions hold.FROM ordersreads the order records.GROUP BY customer_idpartitions the rows per customer. The two counts evaluate inside each customer's partition.
Why one FILTER with AND and not two separate FILTERs
Two separate filters would produce two separate counts — delivered orders and high-value orders — not their intersection. The intersection is the goal here: only the orders that pass both rules. AND inside a single FILTER joins the two predicates so that the aggregate sees exactly the rows where both are true. A row that is delivered but cheap, or pricey but pending, drops out of the filtered count and stays in the unfiltered total.
The trap
FILTER (WHERE A AND B) excludes a row if either predicate is false or if either evaluates to NULL. For this problem the columns involved (status, total_amount) are populated on every order, so the NULL case does not surface in the data. The rule still binds: an AND inside a FILTER is three-valued, so any missing value on either side silently drops the row. When either predicate column can be NULL, decide explicitly whether NULL should count as a fail or be handled separately before relying on the AND.
You practiced FILTER (WHERE A AND B) — combine two conditions inside a single filter, restricting one aggregate to the intersection of both criteria.
