Advanced SQL Techniques: Mastering Window Functions and Common Table Expressions

SQL Window Functions and CTEs

Brian Muriithi explores advanced SQL techniques that solve complex analytical queries where standard SELECT and GROUP BY statements fail. These methods allow for precise calculations across row sets while maintaining individual row data integrity.

Why This Matters

In standard SQL operations, aggregating data through GROUP BY collapses individual rows, causing a loss of granular detail. Window functions and CTEs address this technical limitation by enabling engineers to run sophisticated calculations across data partitions without sacrificing row-level visibility, which is essential for accurate reporting and performance tracking in production environments.

Key Insights

• Window functions use the OVER() clause to define specific data subsets and ordering for calculations without collapsing results.
• Ranking functions handle ties differently: RANK skips positions following a tie (e.g., 1, 2, 2, 4), while DENSE_RANK maintains a continuous sequence.
• The LAG() function facilitates time-series analysis by retrieving values from previous rows to calculate deltas such as performance improvement over time.
• Common Table Expressions (CTEs) use the WITH keyword to define named temporary result sets, transforming complex subqueries into readable logic blocks.
• NTILE(n) enables statistical banding by dividing a dataset into a specified number of equal groups for performance tiering.

Working Examples

Comparison of ranking functions and how they handle tied scores.

SELECT
student_id,
marks,
ROW_NUMBER() OVER (ORDER BY marks DESC) AS row_num,
RANK() OVER (ORDER BY marks DESC) AS rank_num,
DENSE_RANK() OVER (ORDER BY marks DESC) AS dense_rank_num
FROM exam_results;

Using PARTITION BY to calculate group averages without collapsing the result set.

SELECT
student_id,
marks,
ROUND(AVG(marks) OVER (PARTITION BY student_id), 2) AS student_avg
FROM exam_results;

A practical CTE example for filtering and joining aggregated data.

WITH appointment_counts AS (
SELECT
patient_id,
COUNT(appointment_id) AS total_appointments
FROM appointments
GROUP BY patient_id
)
SELECT
p.full_name,
ac.total_appointments
FROM patients p
JOIN appointment_counts ac
ON p.patient_id = ac.patient_id
WHERE ac.total_appointments > 1
ORDER BY ac.total_appointments DESC;

Practical Applications

• Academic Performance Tracking: Use PARTITION BY to compare individual student marks against their personal or class averages without losing individual exam records.
• Healthcare Appointment Analysis: Implement CTEs to isolate patients with high appointment frequencies before joining with master records, preventing the ‘spaghetti code’ associated with deeply nested subqueries.
• Performance Tiering: Apply NTILE to automatically categorize large datasets into performance bands or percentiles for resource allocation.

References:

• https://dev.to/brian_muriithi/sql-window-functions-and-ctes-m4g