Advanced

window functions, performance, and the patterns interviews love.

01window functionsIf there's one advanced topic to really get, it's this one. Window functions show up in basically every data interview, and once they click you'll use them everywhere.
02indexesYour query is correct but takes 40 seconds. Nine times out of ten, the fix is an index. This topic is about what they actually are, so you know when to add one and when not to.
03query optimizationMaking slow queries fast. The main skill here isn't memorizing tricks, it's learning to read what the database is actually doing, then fixing the part that's slow.
04expression indexesA normal index is on a column. An expression index is on the result of something, like LOWER(email). It fixes the "I wrapped my column in a function and now my index is useless" problem.
05non-equi joinsEvery join so far used = in the ON. A non-equi join uses anything else, <, >, BETWEEN, <>. You need it when rows match by range instead of by exact id.
06database design & normalizationEvery other topic is about querying tables. This one's about how the tables should look in the first place. Bad design makes every query harder forever, so it's worth getting right.
07transactions & ACIDA transaction groups several statements into one all-or-nothing unit. Either every step happens, or none of them do. The classic example is moving money, you can't have the debit happen and the credit fail.
08SQL injection & securitySQL injection is when user input ends up being run as sql code. It's one of the oldest bugs on the web and still one of the most common. The fix is simple once you get why it happens.
09recursive CTEsA recursive CTE is a CTE that refers to itself. It's how sql walks through trees and chains when you don't know how deep they go, org charts, category trees, folder structures, "who referred who".
10advanced aggregationYou know COUNT, SUM and GROUP BY. This is the stuff on top, subtotals, pivots, conditional aggregates, stats, and putting things into arrays. Basically everything you need to build a proper report in one query.
11JSON in sqlSometimes data doesn't fit neat columns. Event logs where every event type has different fields, api responses, user settings. Postgres lets you store JSON in a column and still query inside it. Our events table has a payload JSONB column for exactly this.
12real world patternsThis is where everything comes together. These are the problems that keep showing up in actual jobs and interviews, and each one is a combo of CTEs, window functions and joins you've already seen. Once you know these shapes, most "hard" sql questions are just one of them in disguise.