The window functions that replace half your subqueries

A lot of SQL that looks like it needs a correlated subquery or a self-join is really just a window function that hasn't been recognized yet. The tell is any query where you're computing something "per row, relative to other rows in the same group."

Latest-per-group without a self-join

The classic version: "get the most recent order per customer." The subquery version joins the orders table to itself on a MAX(created_at) per customer. The window version skips the join entirely:

select * from (
  select *,
    row_number() over (partition by customer_id order by created_at desc) as rn
  from orders
) t
where rn = 1;

One pass over the table, no self-join, and it generalizes immediately to "top 3 per group" by changing rn = 1 to rn <= 3 — which is a much uglier rewrite in the subquery version.

Running totals without a correlated subquery

SUM(...) OVER (ORDER BY ...) gives you a running total in one line, where the naive approach is a correlated subquery that re-sums everything up to the current row for every single row — quadratic work for something that's linear with a window function:

select date, amount,
  sum(amount) over (order by date) as running_total
from transactions;

Comparing a row to its neighbor

LAG and LEAD answer "what was the previous/next row's value" without a self-join on an adjacent row, which is the pattern behind things like "days since last login" or "change from previous month":

select month, revenue,
  revenue - lag(revenue) over (order by month) as month_over_month_change
from monthly_revenue;

The common thread across all three is the same: PARTITION BY replaces the "group" a correlated subquery would filter to, and ORDER BY inside OVER (...) replaces the ordering logic you'd otherwise hand-roll with a subquery or self-join. Once that mapping clicks, most "per-group, relative-to-other-rows" SQL stops needing anything more exotic than a window function.