Advanced SQL Functions: String, Date/Time, Window & CASE

Prerequisite: You should be comfortable with aggregate functions (COUNT, SUM, AVG) and GROUP BY from the Beginner course before starting this chapter.

In This Chapter

String Functions
Date and Time Functions
Window Functions
CASE Statement

We'll use this employees table throughout:

Setup

CREATE TABLE employees (
    id         INT PRIMARY KEY,
    name       VARCHAR(100),
    department VARCHAR(50),
    salary     DECIMAL(10,2),
    hire_date  DATE
);
INSERT INTO employees VALUES
    (1, 'Sara Ahmed',     'Engineering', 95000,  '2020-03-15'),
    (2, 'James Wilson',   'Engineering', 88000,  '2021-07-01'),
    (3, 'Priya Sharma',   'Marketing',   72000,  '2022-01-10'),
    (4, 'David Chen',     'Engineering', 105000, '2019-06-20'),
    (5, 'Emma Johnson',   'Marketing',   68000,  '2023-03-01'),
    (6, 'Ali Hassan',     'Sales',       78000,  '2021-11-15'),
    (7, 'Maria Garcia',   'Sales',       82000,  '2020-09-01');

1 String Functions

Top

Beyond the basics (UPPER, LOWER, LENGTH), advanced string functions let you extract, transform, and clean text data.

SQL

SELECT
    name,
    SUBSTRING_INDEX(name, ' ', 1)       AS first_name,
    SUBSTRING_INDEX(name, ' ', -1)      AS last_name,
    CONCAT(LEFT(name, 1), '.',
           SUBSTRING_INDEX(name, ' ', -1)) AS short_name,
    REVERSE(name)                        AS reversed,
    LPAD(id, 5, '0')                    AS padded_id
FROM employees WHERE id <= 3;

Result

name          | first_name | last_name | short_name | reversed        | padded_id
--------------|------------|-----------|------------|-----------------|----------
Sara Ahmed    | Sara       | Ahmed     | S.Ahmed    | demhA araS      | 00001
James Wilson  | James      | Wilson    | J.Wilson   | nosliW semaJ    | 00002
Priya Sharma  | Priya      | Sharma    | P.Sharma   | amrahS ayirP    | 00003

Function	What It Does	Practical Use
`SUBSTRING_INDEX(str, delim, n)`	Split string by delimiter, return first n parts	Extract first/last name from full name
`LEFT(str, n)` / `RIGHT(str, n)`	First/last n characters	Initials, file extensions
`LPAD(str, len, pad)` / `RPAD`	Pad to fixed length	Zero-padded IDs: `00042`
`REPLACE(str, old, new)`	Replace all occurrences	Clean data: `REPLACE(phone, '-', '')`
`REVERSE(str)`	Reverse characters	Palindrome checks, domain extraction
`LOCATE(substr, str)`	Find position of substring	Check if email contains '@'

Key Takeaways

SUBSTRING_INDEX splits strings by delimiter — essential for parsing names, emails, URLs
LPAD/RPAD for fixed-width formatting (invoice numbers, IDs)
REPLACE for data cleaning (removing dashes, spaces, special characters)
Combine multiple string functions with CONCAT for complex transformations

2 Date and Time Functions

Top

Beyond NOW() and DATEDIFF(), advanced date functions let you calculate tenure, group by periods, and handle time zones.

SQL

-- Employee tenure analysis
SELECT
    name,
    hire_date,
    TIMESTAMPDIFF(YEAR, hire_date, CURDATE())  AS years_employed,
    TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) AS months_employed,
    DATE_ADD(hire_date, INTERVAL 1 YEAR)       AS first_anniversary,
    LAST_DAY(hire_date)                         AS month_end,
    DAYNAME(hire_date)                           AS hired_on_day
FROM employees WHERE id <= 3;

Result

name          | hire_date  | years | months | anniversary | month_end  | hired_on_day
--------------|------------|-------|--------|-------------|------------|----------
Sara Ahmed    | 2020-03-15 | 6     | 72     | 2021-03-15  | 2020-03-31 | Sunday
James Wilson  | 2021-07-01 | 4     | 57     | 2022-07-01  | 2021-07-31 | Thursday
Priya Sharma  | 2022-01-10 | 4     | 50     | 2023-01-10  | 2022-01-31 | Monday

Grouping by Time Periods

SQL

-- Hires by year
SELECT
    YEAR(hire_date) AS year,
    COUNT(*) AS hires,
    GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS employees
FROM employees
GROUP BY YEAR(hire_date)
ORDER BY year;

Result

year | hires | employees
-----|-------|---------------------------------
2019 | 1     | David Chen
2020 | 2     | Maria Garcia, Sara Ahmed
2021 | 2     | Ali Hassan, James Wilson
2022 | 1     | Priya Sharma
2023 | 1     | Emma Johnson

Function	What It Does	Example
`TIMESTAMPDIFF(unit, start, end)`	Difference in specified units	Years of tenure, months since order
`DATE_ADD(d, INTERVAL n unit)`	Add time to a date	Subscription expiry, SLA deadlines
`LAST_DAY(d)`	Last day of the month	Monthly billing cutoffs
`QUARTER(d)`	Quarter number (1-4)	Quarterly reporting
`GROUP_CONCAT(col)`	Concatenate values from a group	List of names per department

Key Takeaways

TIMESTAMPDIFF calculates exact differences in YEAR, MONTH, DAY, HOUR, etc.
DATE_ADD/DATE_SUB for date arithmetic (add 30 days, subtract 1 year)
Group by YEAR(), MONTH(), or QUARTER() for time-period reports
GROUP_CONCAT (MySQL) combines values into comma-separated lists

3 Window Functions

Top

Window functions are the most powerful feature in modern SQL. They perform calculations across a set of rows related to the current row — without collapsing them into groups like GROUP BY does. You get both the detail rows and the aggregate in the same result.

The OVER() Clause

Every window function uses OVER() to define the "window" of rows it operates on:

SQL

-- Each employee's salary AND the department average, side by side
SELECT
    name, department, salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

Result

name          | department  | salary  | dept_avg  | diff_from_avg
--------------|-------------|---------|-----------|-------------
Sara Ahmed    | Engineering | 95000   | 96000.00  | -1000.00
James Wilson  | Engineering | 88000   | 96000.00  | -8000.00
David Chen    | Engineering | 105000  | 96000.00  | 9000.00
Priya Sharma  | Marketing   | 72000   | 70000.00  | 2000.00
Emma Johnson  | Marketing   | 68000   | 70000.00  | -2000.00
Ali Hassan    | Sales       | 78000   | 80000.00  | -2000.00
Maria Garcia  | Sales       | 82000   | 80000.00  | 2000.00

Notice: no GROUP BY. Every row is preserved. The department average appears next to each employee. This is impossible with regular aggregate functions.

ROW_NUMBER, RANK, DENSE_RANK

SQL

SELECT
    name, department, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC)                          AS row_num,
    RANK()       OVER (ORDER BY salary DESC)                          AS rank_all,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Result

name          | department  | salary  | row_num | rank_all | dept_rank
--------------|-------------|---------|---------|----------|----------
David Chen    | Engineering | 105000  | 1       | 1        | 1
Sara Ahmed    | Engineering | 95000   | 2       | 2        | 2
James Wilson  | Engineering | 88000   | 3       | 3        | 3
Maria Garcia  | Sales       | 82000   | 4       | 4        | 1
Ali Hassan    | Sales       | 78000   | 5       | 5        | 2
Priya Sharma  | Marketing   | 72000   | 6       | 6        | 1
Emma Johnson  | Marketing   | 68000   | 7       | 7        | 2

Function	Ties	Gap After Tie	Use Case
`ROW_NUMBER()`	No ties — always unique	N/A	Pagination, deduplication
`RANK()`	Same rank for ties	Yes (1, 2, 2, 4)	Competition rankings
`DENSE_RANK()`	Same rank for ties	No (1, 2, 2, 3)	Top-N per group

LEAD and LAG: Look at Adjacent Rows

SQL

-- Compare each employee's salary to the next hired person
SELECT
    name, hire_date, salary,
    LAG(salary)  OVER (ORDER BY hire_date) AS prev_salary,
    LEAD(salary) OVER (ORDER BY hire_date) AS next_salary,
    salary - LAG(salary) OVER (ORDER BY hire_date) AS change
FROM employees;

Result

name          | hire_date  | salary | prev_salary | next_salary | change
--------------|------------|--------|-------------|-------------|-------
David Chen    | 2019-06-20 | 105000 | NULL        | 95000       | NULL
Sara Ahmed    | 2020-03-15 | 95000  | 105000      | 82000       | -10000
Maria Garcia  | 2020-09-01 | 82000  | 95000       | 88000       | -13000
James Wilson  | 2021-07-01 | 88000  | 82000       | 78000       | 6000
Ali Hassan    | 2021-11-15 | 78000  | 88000       | 72000       | -10000
Priya Sharma  | 2022-01-10 | 72000  | 78000       | 68000       | -6000
Emma Johnson  | 2023-03-01 | 68000  | 72000       | NULL        | -4000

Running Totals with SUM OVER

SQL

-- Running total of salary spend by hire date
SELECT
    name, hire_date, salary,
    SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;

Window functions are interview favorites. "Get the top 2 earners per department" is a classic SQL interview question. Answer: WITH ranked AS (SELECT *, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rk FROM employees) SELECT * FROM ranked WHERE rk <= 2

Key Takeaways

Window functions add aggregate/ranking columns without collapsing rows
OVER (PARTITION BY col ORDER BY col) defines the window
ROW_NUMBER for unique numbering; RANK/DENSE_RANK for rankings
LAG/LEAD to compare with previous/next rows
SUM() OVER (ORDER BY ...) for running totals

4 CASE Statement

Top

The CASE statement is SQL's IF-THEN-ELSE. It lets you add conditional logic directly in your queries — categorize data, create labels, handle special cases.

Simple CASE

SQL

SELECT
    name, salary,
    CASE
        WHEN salary >= 100000 THEN 'Senior'
        WHEN salary >= 80000  THEN 'Mid-Level'
        WHEN salary >= 60000  THEN 'Junior'
        ELSE 'Entry'
    END AS level
FROM employees
ORDER BY salary DESC;

Result

name          | salary  | level
--------------|---------|----------
David Chen    | 105000  | Senior
Sara Ahmed    | 95000   | Mid-Level
James Wilson  | 88000   | Mid-Level
Maria Garcia  | 82000   | Mid-Level
Ali Hassan    | 78000   | Junior
Priya Sharma  | 72000   | Junior
Emma Johnson  | 68000   | Junior

CASE Inside Aggregate Functions

Combine CASE with COUNT or SUM for pivot-style reports:

SQL

-- Count employees per level per department
SELECT
    department,
    COUNT(CASE WHEN salary >= 90000 THEN 1 END) AS senior,
    COUNT(CASE WHEN salary < 90000 THEN 1 END)  AS junior,
    ROUND(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY department;

Result

department  | senior | junior | avg_salary
------------|--------|--------|----------
Engineering | 2      | 1      | 96000
Marketing   | 0      | 2      | 70000
Sales       | 0      | 2      | 80000

CASE in UPDATE Statements

SQL

-- Different raise percentages by department
UPDATE employees
SET salary = salary * CASE
    WHEN department = 'Engineering' THEN 1.10
    WHEN department = 'Sales'       THEN 1.08
    ELSE 1.05
END;

CASE is the Swiss Army knife of SQL. Use it in SELECT (create columns), WHERE (conditional filters), ORDER BY (custom sort orders), UPDATE (conditional updates), INSERT (conditional values), and inside aggregate functions (pivot tables).

Key Takeaways

CASE WHEN condition THEN result ELSE default END = SQL's IF-THEN-ELSE
Use inside SELECT to categorize/label data, inside UPDATE for conditional changes
COUNT(CASE WHEN ...) creates pivot-table-style reports
CASE works in SELECT, WHERE, ORDER BY, UPDATE, and aggregate functions

What's next? The next chapter covers Stored Procedures & Functions — learn to create reusable SQL functions and understand when to use functions vs procedures.

Advanced SQL Functions

In This Chapter

1 String Functions

Key Takeaways

2 Date and Time Functions

Grouping by Time Periods

Key Takeaways

3 Window Functions

The OVER() Clause

ROW_NUMBER, RANK, DENSE_RANK

LEAD and LAG: Look at Adjacent Rows

Running Totals with SUM OVER

Key Takeaways

4 CASE Statement

Simple CASE

CASE Inside Aggregate Functions

CASE in UPDATE Statements

Key Takeaways

All Chapters in SQL Advanced Course

Related Topics

Courses

Services

Support

Connect