Conditional aggregation pattern in SQL - Mini Project: Build & Apply

Conditional Aggregation Pattern in SQL

📖 Scenario: You work for a small online store. The store keeps track of orders in a table called orders. Each order has a status which can be 'completed', 'pending', or 'cancelled'. You want to find out how many orders are in each status for each customer.

🎯 Goal: Create an SQL query that uses conditional aggregation to count the number of orders by status for each customer.

📋 What You'll Learn

Create a table called orders with columns order_id (integer), customer_id (integer), and status (text).

Insert exactly these rows into orders: (1, 101, 'completed'), (2, 101, 'pending'), (3, 102, 'completed'), (4, 103, 'cancelled'), (5, 101, 'completed').

Write a query that selects customer_id and counts of orders with status 'completed', 'pending', and 'cancelled' using conditional aggregation.

Use SUM(CASE WHEN ... THEN 1 ELSE 0 END) for counting each status.

💡 Why This Matters

🌍 Real World

Stores and businesses often need to summarize data by categories, like counting orders by status for each customer.

💼 Career

Knowing conditional aggregation is essential for data analysts and database developers to create meaningful reports and insights.

1

Create the orders table and insert data

Write SQL statements to create a table called orders with columns order_id (integer), customer_id (integer), and status (text). Then insert these exact rows: (1, 101, 'completed'), (2, 101, 'pending'), (3, 102, 'completed'), (4, 103, 'cancelled'), (5, 101, 'completed').

SQL

-- Create the orders table and insert the rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Add a helper variable for counting

Define a helper variable or expression in your SQL query to count orders. For now, just select customer_id and status from orders to see the data.

SQL

CREATE TABLE orders (
    order_id INTEGER,
    customer_id INTEGER,
    status TEXT
);

INSERT INTO orders (order_id, customer_id, status) VALUES
(1, 101, 'completed'),
(2, 101, 'pending'),
(3, 102, 'completed'),
(4, 103, 'cancelled'),
(5, 101, 'completed');

-- Select customer_id and status from orders
-- Your code here

Need a hint?

Use SELECT customer_id, status FROM orders to view the data before aggregation.

3

Write the conditional aggregation query

Write an SQL query that selects customer_id and counts the number of orders with status 'completed', 'pending', and 'cancelled' using SUM(CASE WHEN status = '...' THEN 1 ELSE 0 END). Group the results by customer_id.

SQL

CREATE TABLE orders (
    order_id INTEGER,
    customer_id INTEGER,
    status TEXT
);

INSERT INTO orders (order_id, customer_id, status) VALUES
(1, 101, 'completed'),
(2, 101, 'pending'),
(3, 102, 'completed'),
(4, 103, 'cancelled'),
(5, 101, 'completed');

-- Write the conditional aggregation query here
-- Your code here

Need a hint?

Use SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) to count completed orders, and similarly for other statuses.

4

Complete the query with ordering

Add an ORDER BY customer_id clause at the end of your query to sort the results by customer_id.

SQL

CREATE TABLE orders (
    order_id INTEGER,
    customer_id INTEGER,
    status TEXT
);

INSERT INTO orders (order_id, customer_id, status) VALUES
(1, 101, 'completed'),
(2, 101, 'pending'),
(3, 102, 'completed'),
(4, 103, 'cancelled'),
(5, 101, 'completed');

SELECT
    customer_id,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count
FROM orders
GROUP BY customer_id
-- Add ORDER BY customer_id here
-- Your code here

Need a hint?

Use ORDER BY customer_id to sort the results by customer.