GREATEST and LEAST functions in PostgreSQL - Mini Project: Build & Apply

Using GREATEST and LEAST Functions in PostgreSQL

📖 Scenario: You work in a small company that tracks monthly sales for three products. You want to find the highest and lowest sales values for each month to understand which product performed best and worst.

🎯 Goal: Create a table with monthly sales data for three products. Then write a query using the GREATEST and LEAST functions to find the highest and lowest sales for each month.

📋 What You'll Learn

Create a table named monthly_sales with columns month (text), product_a (integer), product_b (integer), and product_c (integer).

Insert exactly three rows with sales data for January, February, and March.

Write a SELECT query that includes the original columns plus two new columns: highest_sale using GREATEST and lowest_sale using LEAST.

Ensure the query returns all rows with correct highest and lowest sales values per month.

💡 Why This Matters

🌍 Real World

Companies often compare multiple product sales to identify best and worst performers each month.

💼 Career

Knowing how to use GREATEST and LEAST helps data analysts quickly summarize and compare multiple columns in reports.

1

Create the monthly_sales table

Write a SQL statement to create a table called monthly_sales with these columns: month as text, product_a as integer, product_b as integer, and product_c as integer.

PostgreSQL

-- Create the monthly_sales table with columns month, product_a, product_b, product_c
-- Your code here

Need a hint?

Use CREATE TABLE followed by the table name and define each column with its data type.

2

Insert sales data into monthly_sales

Insert these exact rows into monthly_sales: ('January', 100, 150, 120), ('February', 130, 90, 160), and ('March', 110, 140, 130). Use three separate INSERT INTO statements.

PostgreSQL

CREATE TABLE monthly_sales (
    month TEXT,
    product_a INTEGER,
    product_b INTEGER,
    product_c INTEGER
);
-- Insert the three rows of sales data below
-- Your code here

Need a hint?

Use INSERT INTO monthly_sales VALUES (...) for each row with the exact values.

3

Write a query using GREATEST and LEAST

Write a SELECT query on monthly_sales that returns all columns plus two new columns: highest_sale using GREATEST(product_a, product_b, product_c) and lowest_sale using LEAST(product_a, product_b, product_c).

PostgreSQL

CREATE TABLE monthly_sales (
    month TEXT,
    product_a INTEGER,
    product_b INTEGER,
    product_c INTEGER
);

INSERT INTO monthly_sales VALUES ('January', 100, 150, 120);
INSERT INTO monthly_sales VALUES ('February', 130, 90, 160);
INSERT INTO monthly_sales VALUES ('March', 110, 140, 130);

-- Write the SELECT query with GREATEST and LEAST below
-- Your code here

Need a hint?

Use GREATEST and LEAST functions in the SELECT clause to find the highest and lowest sales.

4

Complete the query with ordering

Add an ORDER BY month clause at the end of the SELECT query to sort the results by month alphabetically.

PostgreSQL

CREATE TABLE monthly_sales (
    month TEXT,
    product_a INTEGER,
    product_b INTEGER,
    product_c INTEGER
);

INSERT INTO monthly_sales VALUES ('January', 100, 150, 120);
INSERT INTO monthly_sales VALUES ('February', 130, 90, 160);
INSERT INTO monthly_sales VALUES ('March', 110, 140, 130);

SELECT month, product_a, product_b, product_c,
       GREATEST(product_a, product_b, product_c) AS highest_sale,
       LEAST(product_a, product_b, product_c) AS lowest_sale
FROM monthly_sales
-- Add ORDER BY month below
-- Your code here

Need a hint?

Use ORDER BY month at the end of the query to sort results alphabetically by month.