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

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

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

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

Hint

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

Practice

1. What does the GREATEST function do in PostgreSQL?

easy

A. Counts the number of values in a list

B. Returns the smallest value from a list of values

C. Returns the largest value from a list of values

D. Calculates the average of values in a list

Solution

1. Step 1: Understand the function purpose

   The GREATEST function compares all given values and finds the largest one.

2. Step 2: Compare with other options

   LEAST returns the smallest, counting and averaging are different functions.

3. Final Answer:

   Returns the largest value from a list of values -> Option C

4. Quick Check:

   GREATEST = largest value [OK]

Hint: GREATEST picks the biggest value from all inputs [OK]

Common Mistakes:

• Confusing GREATEST with LEAST
• Thinking it counts or averages values
• Assuming it returns a boolean

2. Which of the following is the correct syntax to find the smallest value among 5, 10, and 3 using PostgreSQL?

easy

A. SELECT LEAST[5, 10, 3];

B. SELECT LEAST(5, 10, 3);

C. SELECT LEAST{5, 10, 3};

D. SELECT LEAST 5, 10, 3;

Solution

1. Step 1: Recall correct function call syntax

   PostgreSQL functions use parentheses with comma-separated values.

2. Step 2: Check each option

   Only SELECT LEAST(5, 10, 3); uses parentheses correctly; others use brackets, braces, or no parentheses.

3. Final Answer:

   SELECT LEAST(5, 10, 3); -> Option B

4. Quick Check:

   Function calls need parentheses [OK]

Hint: Use parentheses () for function arguments in SQL [OK]

Common Mistakes:

• Using square brackets or braces instead of parentheses
• Omitting parentheses around arguments
• Separating arguments incorrectly

3. What will be the result of this query?

SELECT GREATEST(7, 2, 9, 4) AS max_val, LEAST(7, 2, 9, 4) AS min_val;

medium

A. max_val = 9, min_val = 2

B. max_val = 7, min_val = 4

C. max_val = 4, min_val = 2

D. max_val = 9, min_val = 4

Solution

1. Step 1: Evaluate GREATEST function

   Among 7, 2, 9, 4, the largest value is 9.

2. Step 2: Evaluate LEAST function

   Among the same values, the smallest is 2.

3. Final Answer:

   max_val = 9, min_val = 2 -> Option A

4. Quick Check:

   GREATEST=9, LEAST=2 [OK]

Hint: GREATEST picks max, LEAST picks min from values [OK]

Common Mistakes:

• Mixing up max and min values
• Choosing middle values instead of extremes
• Ignoring all values except first or last

4. Identify the error in this query:

SELECT GREATEST(4, 8, 'ten') AS max_value;

medium

A. Missing closing parenthesis

B. No error, query runs fine

C. GREATEST function requires at least four arguments

D. Mixing integer and string types causes an error

Solution

1. Step 1: Check argument types

   GREATEST compares values of the same type; mixing integers and strings causes a type error.

2. Step 2: Verify syntax and argument count

   Parentheses are correct and function accepts any number of arguments greater than zero.

3. Final Answer:

   Mixing integer and string types causes an error -> Option D

4. Quick Check:

   Type mismatch causes error [OK]

Hint: All arguments must be same type for GREATEST/LEAST [OK]

Common Mistakes:

• Assuming mixed types work without error
• Thinking function needs fixed number of arguments
• Ignoring type errors in SQL

5. You have a table scores with columns math, science, and english. Which query correctly finds the highest and lowest score for each student?

hard

A. SELECT student_id, GREATEST(math, science, english) AS highest, LEAST(math, science, english) AS lowest FROM scores;

B. SELECT student_id, MAX(math, science, english) AS highest, MIN(math, science, english) AS lowest FROM scores;

C. SELECT student_id, GREATEST(math, science, english) AS lowest, LEAST(math, science, english) AS highest FROM scores;

D. SELECT student_id, MAX(math) AS highest, MIN(english) AS lowest FROM scores;

Solution

1. Step 1: Understand GREATEST and LEAST usage

   They compare multiple columns per row to find max and min values.

2. Step 2: Analyze each option

   SELECT student_id, GREATEST(math, science, english) AS highest, LEAST(math, science, english) AS lowest FROM scores; correctly uses GREATEST and LEAST on columns per student. SELECT student_id, MAX(math, science, english) AS highest, MIN(math, science, english) AS lowest FROM scores; uses MAX/MIN incorrectly with multiple columns. SELECT student_id, GREATEST(math, science, english) AS lowest, LEAST(math, science, english) AS highest FROM scores; swaps highest and lowest. SELECT student_id, MAX(math) AS highest, MIN(english) AS lowest FROM scores; only compares single columns, not all three.

3. Final Answer:

   SELECT student_id, GREATEST(math, science, english) AS highest, LEAST(math, science, english) AS lowest FROM scores; -> Option A

4. Quick Check:

   GREATEST/LEAST compare columns per row [OK]

Hint: Use GREATEST/LEAST to compare multiple columns in one row [OK]

Common Mistakes:

• Using MAX/MIN with multiple columns incorrectly
• Swapping highest and lowest functions
• Comparing only one column instead of all