Skip to content

Advanced Analytical Functions

A. Subqueries & Common Table Expressions (CTEs)

Showing the exact evolution from a basic nested query to a highly readable, reusable CTE is a massive help for learners.

-- Nested Subquery Approach
SELECT name, marks 
FROM student1 
WHERE marks > (SELECT AVG(marks) FROM student1);

-- Clean, Reusable CTE equivalent
WITH ClassMetrics AS (
    SELECT AVG(marks) AS overall_avg FROM student1
)
SELECT s.name, s.marks 
FROM student1 s, ClassMetrics c
WHERE s.marks > c.overall_avg;

B. Analytical Window Functions (OVER, RANK, ROW_NUMBER)

Window functions allow developers to calculate running totals, moving averages, or rank rows without collapsing the underlying records into a single row.

-- Add this to show analytics over categories without row-collapse
SELECT 
    name, 
    category, 
    amount,
    SUM(amount) OVER(ORDER BY amount DESC) as running_total,
    DENSE_RANK() OVER(PARTITION BY category ORDER BY amount DESC) as rank_in_dept
FROM payments;

C. Indexes and Query Performance Tuning

This is critical for production optimization. Teach them how to add indexes to columns that are heavily queried to speed up execution times, and show them how to inspect execution queries.

-- Create an index to optimize searching by student name
CREATE INDEX idx_student_name ON student1(name);

-- Analyze how the query planner engine executes a retrieval statement
EXPLAIN SELECT * FROM student1 WHERE name = "anil";