How can the SQL MIN and MAX Functions be used to Reveal Quick and Useful Data Insights

Data Analysis using SQL (Image created using Adobe Spark/Adobe Express)

In the article- Five Simple SQL Functions to Start With Data Profiling and Analysis, we discussed in brief, 5 SQL functions that can help kickstart your data analysis and data profiling journey. The SQL functions MIN and MAX were amongst them.

MIN and MAX are simple, yet powerful aggregate SQL functions that can be used to find anomalies in data, specifically where a data field can have a range of values, and provide some quick and useful insights into your data. In this article, I will present some examples as to how to use the MIN and MAX function in SQL to get a better understanding of the quality of your data and draw some useful insights.

SQL Statement — Syntax

The SELECT statement can be used to extract or read data from the tables in a relational database.

The general form of the SQL SELECT statement is as follows:

SELECT Column names

FROM Table name

<WHERE Condition

ORDER BY Column names>

The WHERE condition and ORDER BY column names are optional.

The WHERE clause filters rows that meet certain criteria and is followed by conditions or a set of conditions that return either a true or false value.

ORDER BY clause allows sorting by one or more columns. The default sort order is ascending, that is, low to high or A to Z. The keyword DESC denotes descending, that is, reverse order. If the ORDER BY clause is not used, records will not be returned in a specific order.

MIN and MAX functions

The MIN and MAX functions can be used with-

• numeric data type;
• character/string data type;
• date data type.

The MIN function returns the smallest value of the selected column in a set of rows/records. For string or character data, it returns the first value in the alphabetical order.

The MAX function returns the largest value of the selected column in a set of rows/records. For string or character data, it returns the last value in the alphabetical order.

MIN and MAX are supported by Microsoft SQL Server, MySQL, Oracle, and PostgreSQL.

MySQL also supports the functions LEAST( ) and GREATEST( ), providing the same capabilities.

Data Profiling Business Scenario and Sample SQL Queries with the MIN and MAX functions

Business Scenario: Say you are would like to get some insights or assess the quality of the Employee table (see Table 1) of ABZ Ltd. which was established in the year 2000.

Note: This table stores active employee information only.

Table 1: Employee table

Let’s start with assessing the following fields for maximum and minimum values:

1. Date of Birth (Date_of_Birth field in Employee table)
2. Date of Joining (Date_of_Joining field in Employee table)
3. Annual Salary (AnnualSalaryDollars field in Employee table)

Date of Birth Field Analysis

The following SQL query can be used to find the minimum as well as the maximum date of birth recorded in the Employee table.

SELECT MIN(Date_of_Birth) AS Min_Date_of_Birth, MAX(Date_of_Birth) AS Max_Date_of_Birth

FROM Employee

The results of the query are shown in table 2.

Table 2: Maximum and minimum query results for date of birth in employee table

The minimum date of birth shows the birth year as 1900. Since ABZ Ltd. was established in 2000, it is impossible that it could have an employee who is 100 years or more. The maximum date of birth shows the birth year as 2020, which does not look right, as an employee cannot be an infant or a child.

Date of Joining Field Analysis

The following SQL query can be used to find the minimum as well as the maximum date of birth recorded in the Employee table.

SELECT MIN(Date_of_Joining) AS Min_Date_of_Joining, MAX(Date_of_Birth) AS Max_Date_of_Joining

FROM Employee

The results of the query are shown in table 3.

Table 3: Maximum and minimum query results for date of joining in employee table

We see that the minimum date of joining is 12/12/1900 which is 100 years before ABZ Ltd. came into existence. Hence, this value cannot be right. The maximum date of joining shows the year 2015, which means the last hire took place in 2015. However, since ABZ Ltd. hires every year, this value can not be right.

Annual Salary Field Analysis

The following SQL query can be used to find the minimum as well as the maximum date of birth recorded in the Employee table.

SELECT MIN(AnnualSalaryDollars) AS Min_AnnualSalaryDollars, MAX(Date_of_Birth) AS Max_AnnualSalaryDollars

FROM Employee

The results of the query are shown in table 4.

Table 4: Maximum and minimum query results for annual salary in employee table

As per query results, the minimum annual salary is -50 dollars. Since salary should always be a positive value, this value is not correct. The maximum value is $150000. The HR department needs to be consulted to find out the maximum salary to determine whether this value is correct.

Employee Data Analysis — Concluding Thoughts

In this article, I have used some simple examples, to illustrate how to use the MIN and MAX functions. As an analyst, the next steps would involve conducting root cause analysis to find out what went wrong, so that solutions can be proposed to resolve the issue.

I hope the examples in this article have been helpful and you will be able to apply these functions in your data analysis job to assess the quality of your data or derive quick insights into your data.

If you have any questions or any inputs you want to share, comment here or connect on LinkedIn.

To learn more about data quality, data profiling, including how to measure data quality dimensions, implement methodologies for data quality management, data quality strategy, and data quality aspects to consider when undertaking data intensive projects, read Data Quality: Dimensions, Measurement, Strategy, Management and Governance (ASQ Quality Press, 2019).