Part 2: Mastering the MAX() Function | SQL Challenges & Capstone
SQL Aggregation & Grouping Practice · Part 2 · Lecture Notes
Mastering the MAX() Function
How to identify the largest values and most recent dates in business datasets — maximum prices, fees, transaction peaks, and the latest operational activity.
Part 2 of 728% Complete
MAX()
Numeric Maximums
Latest Dates
Revenue Peaks
8 Practice Questions
Learning Objectives
Use MAX() to return the largest numeric value from a column
Apply MAX() to date columns to find the most recent date in a dataset
Pair MAX() with ROUND() for clean decimal output on financial columns
Combine MAX() and MIN() to report the full range of a business metric
Click any section header to open or close it
MAX() returns the largest value from a column. It is the direct counterpart of MIN() — where MIN() finds the floor, MAX() finds the ceiling. It works on the same data types: numbers return the highest amount, dates return the most recent, text returns the alphabetically last value.
In industry, analysts use MAX() to identify the highest-priced products, find peak transaction amounts, measure revenue peaks, discover the most recent customer signup or order date, and identify the top business metrics across any dataset.
The Basic Structure
MAX() goes inside the SELECT clause, wrapping the column you want the maximum of. Give the result a clear alias with AS. For decimal columns, always pair with ROUND(…, 2) for clean financial output.
Basic MAX() Syntax
SELECTMAX(column_name)
FROM table_name;
MAX() on a numeric column returns the single highest number — the most expensive product, the largest transaction, the highest subscription fee. Like MIN(), it collapses the entire column into one output row. Pair with ROUND() for clean financial output.
MAX() as a Revenue Ceiling
MAX(amount) tells you the highest single transaction ever recorded — your revenue ceiling. This is a useful KPI anchor: it tells analysts which transaction type, product, or customer reaches the highest value, and provides a target benchmark for sales and revenue goals.
Maximum Transaction Amount
SELECTROUND(MAX(amount), 2) AS maximum_transaction
FROM transactions;
Maximum Product Price
SELECTROUND(MAX(unit_price), 2) AS maximum_price
FROM products;
MAX() on a date column returns the most recent date — the latest signup, the newest order, the most recent campaign end. This is one of the most common date queries in business analytics — “what is the latest activity?” tells analysts how current their data is and whether operations are still active.
Latest Date = MAX()
When you need the most recent date in a column, MAX() is always the correct function. ORDER BY date DESC LIMIT 1 produces the same result, but MAX() is shorter, more readable, and more efficiently optimised by the database.
Latest Signup Date
SELECTMAX(signup_date) AS latest_signup
FROM users;
Latest Order Date
SELECTMAX(order_date) AS latest_order
FROM orders;
The maximum value reveals the peak of business activity — the most any customer has ever spent, the highest price in the catalogue, the busiest recorded date. Analysts use MAX() in combination with MIN() and AVG() to describe the full distribution of any business metric.
Always Report MIN and MAX Together
A maximum subscription fee of $49.99 gains analytical context when paired with a minimum of $19.99 and an average of $32.49. The full range — floor, ceiling, middle — is the standard business summary that appears in dashboards and executive reports.
Maximum Subscription Fee
SELECTROUND(MAX(monthly_fee), 2) AS maximum_fee
FROM subscriptions;
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message.
Practice 1Retrieve the maximum transaction amount rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve the maximum transaction amount rounded to 2 decimal places.
SQL Answer
SELECTROUND(MAX(amount), 2) AS maximum_transaction
FROM transactions;
Practice 2Retrieve the maximum product price rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve the maximum product price rounded to 2 decimal places.
SQL Answer
SELECTROUND(MAX(unit_price), 2) AS maximum_price
FROM products;
Practice 3Retrieve the maximum subscription monthly_fee rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve the maximum subscription monthly_fee rounded to 2 decimal places.
SQL Answer
SELECTROUND(MAX(monthly_fee), 2) AS maximum_fee
FROM subscriptions;
Practice 4Retrieve the latest signup_date from the users table
AI Prompt
Write a MySQL query to retrieve the latest signup_date from the users table.
SQL Answer
SELECTMAX(signup_date) AS latest_signup
FROM users;
Practice 5Retrieve the latest order_date from the orders table
AI Prompt
Write a MySQL query to retrieve the latest order_date from the orders table.
SQL Answer
SELECTMAX(order_date) AS latest_order
FROM orders;
Practice 6Retrieve the latest campaign end_date from the campaigns table
AI Prompt
Write a MySQL query to retrieve the latest campaign end_date from the campaigns table.
SQL Answer
SELECTMAX(end_date) AS latest_campaign_end
FROM campaigns;
Practice 7Retrieve the maximum quantity from the order_items table
AI Prompt
Write a MySQL query to retrieve the maximum quantity from the order_items table.
SQL Answer
SELECTMAX(quantity) AS maximum_quantity
FROM order_items;
Practice 8Retrieve the latest support ticket created_date from the support_tickets table
AI Prompt
Write a MySQL query to retrieve the latest support ticket created_date from the support_tickets table.
SQL Answer
SELECTMAX(created_date) AS latest_ticket
FROM support_tickets;
Knowledge Check
Five questions — select the best answer for each.
1. What does MAX() return from a column?
AThe last row inserted into the table
BThe largest value in the column
CThe sum of all values
DThe most frequently occurring value
Correct. MAX() scans the entire column and returns the single largest value — the highest number, the most recent date, or the alphabetically last text value.
Not quite. MAX() returns the largest value in the column — not the last row, not the sum, not the most common value.
2. Which query correctly finds the most recent order date?
ASELECT MAX(order_date) AS latest_order FROM orders;
BSELECT ROUND(MAX(order_date), 2) AS latest_order FROM orders;
CSELECT MIN(order_date) AS latest_order FROM orders;
DSELECT LAST(order_date) AS latest_order FROM orders;
Correct. MAX() on a date column returns the most recent date. ROUND() is not used on dates. MIN() would return the earliest. LAST() is not a standard SQL function.
Not quite. MAX(order_date) returns the most recent date. ROUND() is for numeric columns only. MIN() returns the earliest date, not the latest.
3. What is the key difference between MIN() and MAX()?
AMIN() works on numbers; MAX() works on dates
BMIN() returns the smallest value; MAX() returns the largest value
CMIN() returns one row; MAX() returns all rows
DThey are identical — both return the same result
Correct. MIN() and MAX() are complementary — MIN() is the floor (smallest), MAX() is the ceiling (largest). Both always return exactly one row.
Not quite. MIN() and MAX() are opposites — MIN() returns the smallest value, MAX() returns the largest. Both work on any orderable column type.
4. Why do analysts typically report MIN() and MAX() together?
ASQL requires both to be written in the same query
BTogether they reveal the full range — the floor and ceiling — of a business metric
CMAX() can only function if MIN() has been run first
DThey cancel each other out to produce the average
Correct. MIN() alone shows the floor; MAX() alone shows the ceiling. Together they reveal the full range of the metric — essential context for pricing analysis, revenue benchmarking, and performance reporting.
Not quite. Reporting both gives the full range of the metric — the lowest and highest values — which is more informative than either alone.
5. An analyst wants to know the most recent customer signup. Which function should they use?
ASUM(signup_date)
BCOUNT(signup_date)
CMIN(signup_date)
DMAX(signup_date)
Correct. The most recent date is the largest chronological value. MAX() on a date column returns the latest date — in this case, the most recent customer signup.
Not quite. The most recent date is the largest date value. MAX(signup_date) returns the latest signup date.
Part 2 Summary
The key ideas from this lecture — keep these in mind as you move forward.
MAX() helps analysts identify the largest values, latest dates, and highest business metrics inside operational datasets. Use ROUND() with decimal columns. Pair with MIN() for full-range reporting. MAX() always returns exactly one row.
