SQL Data Analytics • Lesson 19

Advanced Analytics with SQL

Turn SQL into an analytical tool by building running totals, moving averages, rankings and percentage-of-total calculations.

Your progress0%

0 of 8 concepts completed

Complete the activity and related MCQ to unlock the next concept.

Concept 1WHY ANALYTICS

From Rows to Business Insight

Advanced analytics turns raw rows into business answers: how much has accumulated, is performance trending up, who is leading, what share of revenue does each person own.

Interactive Data

Sales table

Nine monthly sales across three salespeople — enough to compute running totals, moving averages, rankings and shares.

SALES

PEOPLE

₹2,13,000

GRAND TOTAL

id	salesperson	month	amount	running_total	moving_avg (3)
1	Rahul	January	₹25,000	₹25,000	₹25,000
2	Rahul	February	₹32,000	₹57,000	₹28,500
3	Rahul	March	₹28,000	₹85,000	₹28,333
4	Priya	January	₹18,000	₹1,03,000	₹26,000
5	Priya	February	₹22,000	₹1,25,000	₹22,667
6	Priya	March	₹30,000	₹1,55,000	₹23,333
7	Amit	January	₹15,000	₹1,70,000	₹22,333
8	Amit	February	₹19,000	₹1,89,000	₹21,333
9	Amit	March	₹24,000	₹2,13,000	₹19,333

Ranked salespeople

rank	salesperson	total_sales	% of total
1	Rahul	₹85,000	39.9%
2	Priya	₹70,000	32.9%
3	Amit	₹58,000	27.2%

Learn by Seeing

Four business questions

How much so far?    → Running total
Trend up or down?   → Moving average
Who is leading?     → Ranking
Share of revenue?   → Percentage of total

Each analytical technique answers a different kind of business question.

Click a technique to see what question it answers:

Running total

Accumulates values row by row — answers 'how much so far?'

Interactive Activity

Which technique answers 'how much has accumulated so far?'

Hint: It adds each row to everything that came before.

SQL Analyst Mindset

Aggregate, rank, compare, then report

Real analytical questions rarely stop at a single aggregation. They ask for trend, share, rank and cumulative value all at once. Combining CTEs with window functions turns those questions into a single readable query.

Aggregate

SUM(amount) per salesperson.

Rank

RANK() over the totals.

Measure

% of total via SUM(...) OVER ().

Report

One row per salesperson, ready for management.

Frequently Asked Questions

Advanced Analytics with SQL — FAQs

What is the difference between a running total and a moving average?

A running total accumulates from the start; a moving average only covers the last N rows and smooths short-term noise.

Why use SUM(...) OVER () with an empty OVER()?

An empty OVER() computes the aggregate across the entire result set, which is what you want for a percentage-of-total calculation.

Should I aggregate first or rank first?

Aggregate first (often in a CTE), then rank the aggregated rows in the outer query — ranking raw rows gives a different result.

Can I combine running totals with PARTITION BY?

Yes. PARTITION BY salesperson with SUM() OVER (ORDER BY month) gives a running total per salesperson rather than across the whole table.

Lesson Summary

What you learned

Running total

SUM(...) OVER (ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

Moving average

AVG(...) OVER (ORDER BY ... ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).

Ranking

RANK() OVER (ORDER BY total_sales DESC) for a leaderboard.

Percentage of total

value * 100.0 / SUM(value) OVER () for share of revenue.