Analytical Sql

By the end of this lesson you'll be able to turn a flat table into a real BI report: multi-level totals with subtotals and a grand total, percentiles and median, quartile buckets, running totals, and month-over-month growth — the exact toolkit behind every dashboard.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Every query in this lesson runs against this tiny sales table. It's deliberately small so you can add the numbers in your head and confirm each report by hand — that's the fastest way to trust what a subtotal or percentile is doing.

Handy totals to memorise: North = 600 , South = 1500 , Grand total = 2100 . By month: Jan 500 , Feb 700 , Mar 900 .

A plain GROUP BY gives you exactly one level of summary — one row per region, say. A report , though, almost always wants more: a subtotal for each region and a grand total at the bottom. ROLLUP produces that whole hierarchy in a single query.

ROLLUP is the Subtotal button in a spreadsheet. You group by region, and it inserts a subtotal line after each region plus one grand-total line at the very end — without you copying formulas around. GROUP BY ROLLUP (region, month) reads left to right: month rolls up into region, region rolls up into the grand total.

Now add ROLLUP . You get the detail rows, a subtotal per region (where month is NULL ), and one grand-total row (where both are NULL ):

Those NULL s are the part beginners misread. A NULL in a ROLLUP result does not mean "missing data" — it means "this column was rolled up; this row is a subtotal". The GROUPING(col) function makes that explicit: it returns 1 for a rolled-up (subtotal) column and 0 for a normal value, so you can swap in a readable label.

Fill in the blanks to produce a per-region total plus a grand-total row. The expected result is in the comments so you can check yourself.

ROLLUP always rolls up left to right . Sometimes you want totals that aren't a strict hierarchy — a region summary and a month summary side by side, with no detail rows. GROUPING SETS lets you list precisely the combinations you want; the empty set () is the grand total.

CUBE goes the other way and gives you every combination at once — detail, per-region, per-month, and grand total — which is ideal when a dashboard slices the same numbers in any direction.

CUBE on the same two columns adds the detail rows back on top of those summaries — every cross-section of the data in one result:

(That's 12 of the 13 rows; the table above lists every grouping. CUBE of n columns produces 2^n grouping sets, so it grows fast — prefer ROLLUP unless you genuinely need every axis.)

An average is a single number that outliers can drag around; percentiles describe the whole spread. The median (the 50th percentile) is the middle value — half the data sits below it. In SQL these are ordered-set functions: you write PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) , where WITHIN GROUP names the column to rank.

PERCENTILE_CONT ("continuous") interpolates between rows, so it can return a value that isn't in the data — the true statistical median. PERCENTILE_DISC ("discrete") returns an actual value from a row. With our six amounts the continuous median is 350 (halfway between 300 and 400) while the discrete median is 300 .

NTILE(n) is a window function that sorts your rows and slices them into n equally-sized buckets, numbered 1..n . NTILE(4) gives quartiles, NTILE(10) deciles, NTILE(100) percentile bands — perfect for "which tier does this customer fall in?" segmentation.

Two blanks: split the amounts into 3 buckets and show the overall median on every row. The expected result is in the comments.

A running total accumulates a value from the first row down to the current one — month-to-date revenue, a year-to-date count, a balance. You get it from a window SUM with an ORDER BY and an explicit frame: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means "everything so far, including this row".

To compare a period with the one before it, LAG(value, 1) reaches back one row in the ordered window. Subtract to get the change; divide by the previous value for a growth percentage. The first row has nothing before it, so LAG returns NULL there — and NULLIF(prev, 0) keeps your percentage from dividing by zero.

Want year-over-year instead of month-over-month? Use LAG(revenue, 12) to reach back twelve rows. LEAD is the mirror image — it looks forward to the next period.

Cohort and funnel analyses sound advanced, but they're built entirely from the tools you just learned — grouped aggregates, CASE WHEN tagging, window functions, and NULLIF for safe division. The query below shows the shape of each (against imagined event tables) so you can recognise the pattern when you meet it.

Q: When should I use ROLLUP vs. GROUPING SETS vs. CUBE?

Use ROLLUP for a natural hierarchy (region month grand total). Use GROUPING SETS when you want a specific list of summaries that isn't a strict hierarchy. Use CUBE only when you truly need every cross-section — it can produce a lot of rows.

Q: Why is my median a value that isn't in the table?

That's PERCENTILE_CONT doing its job — it interpolates between the two middle rows. If you need a real row value, use PERCENTILE_DISC instead.

Q: What's the difference between NTILE and a percentile?

NTILE(n) labels each row with which of n equal-sized buckets it falls in. PERCENTILE_CONT returns the boundary value at a given fraction. Buckets vs. cut-points — related, but answering different questions.

Q: My ROLLUP totals vanished after I added a WHERE clause — why?

You probably filtered on region IS NOT NULL , which removes the subtotal/grand-total rows (their grouped columns are NULL by design). Use GROUPING(region) = 1 to identify totals, and keep filters on the raw data columns only.

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a Postgres playground to confirm.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary