Data & Analytics•SOTAAZ Lab•December 3, 2025•KR

"Can SQL Do This?" — Escaping Subquery Hell with Window Functions

LAG, LEAD, RANK for month-over-month, rankings, and running totals

"Can SQL Do This?" — Escaping Subquery Hell with Window Functions

Escaping Subquery Hell

"Get me month-over-month growth rate", "Top 3 products in each category", "Cohort retention analysis"...

Ever received these requests and ended up nesting subqueries inside subqueries, inside more subqueries, until you created a query even you couldn't understand? Master Window functions properly, and complex analytical queries become clean and fast.

1. What Are Window Functions? — 1 Minute Explanation

Subquery Approach vs Window Function Approach

"Show me each employee's salary alongside their department average salary"

With subquery:

sql

SELECT
    e.name,
    e.department,
    e.salary,
    (SELECT AVG(salary) FROM employees e2
     WHERE e2.department = e.department) as dept_avg
FROM employees e;

The subquery executes for every row. 10,000 rows means 10,000 executions.

The rest of this series is paid

✓ The whole “SQL analytics” series: 9 posts in English, 9 in Korean
✓ 7 Jupyter notebooks
✓ One payment, no expiry. Opens when you sign in with the email you pay with.

The series pass needs no account to pay. The subscription needs a free account.

"Can SQL Do This?" — Escaping Subquery Hell with Window Functions

Escaping Subquery Hell

1. What Are Window Functions? — 1 Minute Explanation

The rest of this series is paid

Files for this post