FILTER clause for conditional aggregation in PostgreSQL - Mini Project: Build & Apply

Using FILTER Clause for Conditional Aggregation in PostgreSQL

📖 Scenario: You are working with a sales database for a small store. The store wants to analyze sales data to see how many items were sold in different categories and how many sales were made on weekends.

🎯 Goal: Build a SQL query that uses the FILTER clause to count sales conditionally based on product category and day of the week.

📋 What You'll Learn

Create a table called sales with columns id (integer), category (text), amount (integer), and sale_date (date).

Insert the exact rows provided into the sales table.

Write a query that counts total sales and counts sales only for the category 'Books' using the FILTER clause.

Add a conditional count for sales made on weekends using the FILTER clause.

💡 Why This Matters

🌍 Real World

Stores and businesses often want to analyze sales data by different conditions, such as product category or day of the week, to make better decisions.

💼 Career

Knowing how to use conditional aggregation with FILTER clauses is useful for data analysts and database developers to write efficient and clear SQL queries.

1

Create the sales table and insert data

Create a table called sales with columns id (integer), category (text), amount (integer), and sale_date (date). Then insert these exact rows into the sales table:
(1, 'Books', 10, '2024-06-01'), (2, 'Electronics', 200, '2024-06-02'), (3, 'Books', 15, '2024-06-03'), (4, 'Clothing', 50, '2024-06-04'), (5, 'Books', 20, '2024-06-08')

PostgreSQL

-- Create the sales table and insert the given 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 weekend days

Add a helper expression in your query to identify weekend days. Create a boolean expression called is_weekend that is true when sale_date is Saturday or Sunday. Use EXTRACT(DOW FROM sale_date) where 0 = Sunday and 6 = Saturday.

PostgreSQL

CREATE TABLE sales (
    id INTEGER,
    category TEXT,
    amount INTEGER,
    sale_date DATE
);

INSERT INTO sales (id, category, amount, sale_date) VALUES
(1, 'Books', 10, '2024-06-01'),
(2, 'Electronics', 200, '2024-06-02'),
(3, 'Books', 15, '2024-06-03'),
(4, 'Clothing', 50, '2024-06-04'),
(5, 'Books', 20, '2024-06-08');

-- Add a helper expression called is_weekend
-- Your code here

Need a hint?

Use EXTRACT(DOW FROM sale_date) and compare it to 0 or 6 to find weekends.

3

Write a query using FILTER to count total and 'Books' sales

Write a SQL query that counts total sales using COUNT(*) and counts only sales where category = 'Books' using COUNT(*) FILTER (WHERE category = 'Books'). Use the sales table.

PostgreSQL

CREATE TABLE sales (
    id INTEGER,
    category TEXT,
    amount INTEGER,
    sale_date DATE
);

INSERT INTO sales (id, category, amount, sale_date) VALUES
(1, 'Books', 10, '2024-06-01'),
(2, 'Electronics', 200, '2024-06-02'),
(3, 'Books', 15, '2024-06-03'),
(4, 'Clothing', 50, '2024-06-04'),
(5, 'Books', 20, '2024-06-08');

-- Write a query to count total sales and count sales where category = 'Books' using FILTER
-- Your code here

Need a hint?

Use COUNT(*) FILTER (WHERE condition) to count only rows matching the condition.

4

Add a FILTER clause to count weekend sales

Extend the previous query to add a new column weekend_sales that counts sales where the sale was made on a weekend. Use COUNT(*) FILTER (WHERE EXTRACT(DOW FROM sale_date) = 0 OR EXTRACT(DOW FROM sale_date) = 6).

PostgreSQL

CREATE TABLE sales (
    id INTEGER,
    category TEXT,
    amount INTEGER,
    sale_date DATE
);

INSERT INTO sales (id, category, amount, sale_date) VALUES
(1, 'Books', 10, '2024-06-01'),
(2, 'Electronics', 200, '2024-06-02'),
(3, 'Books', 15, '2024-06-03'),
(4, 'Clothing', 50, '2024-06-04'),
(5, 'Books', 20, '2024-06-08');

-- Add a FILTER clause to count weekend sales
-- Your code here

Need a hint?

Use the same FILTER syntax with the weekend condition to count weekend sales.