9 programs
Choose the best training program according to your career.
Improve the performance of your business with our innovative Industrial Training Program.
Read More →
Our instructor-led training classes are designed to make you an expert.
Read More →
Real-life case studies are a great way to demonstrate your practical knowledge.
Read More →
Explore blogs, latest technologies and industry updates with practical examples.
Read More →
Browse all available training programs and choose the right one for your career.
Read More →Know more about who we are and what we stand for.
Discover our mission to equip individuals with essential IT skills and industry expertise.
Read More →Learn, practice and plan your career — bilkul free.
Book a free video counselling session and pick the right course for you.
Read More →Download the SQL analytics functions PDF from Uncodemy: ranking, LAG and LEAD, running totals and moving averages with tested queries.
This page offers Uncodemy's SQL analytics functions guide as a PDF download. It is a cookbook of patterns that analysts use to answer business questions: growth over time, running totals, shares, rankings, top-N per group and a Pareto view of what drives revenue. Every pattern is shown with SQL you can run in SQLite.
Download the guide below. Each download is its own file, and everything in it is also explained on this page.
A cookbook of business metric recipes: month-on-month growth, running totals, percent of total, top-N per group, Pareto analysis and moving averages, with a window function chooser and common pitfalls.
A short form asks for your details before the download starts. Your details are used only to share study material and course information.
Learning that SUM, LAG and RANK exist is only the start. Analysts earn their keep by knowing which pattern answers which business question. How did revenue change from last month? Which products make up eighty percent of sales? Who are the top three customers in each city? What is the running total against target? Each of these questions has a standard recipe in SQL, built from aggregation, window functions and a CTE or two. This guide collects the most common recipes so that you can recognise them, adapt them and check them.
The examples use these tables: customers(customer_id, name, city, signup_date), orders(order_id, customer_id, order_date, status, order_total), order_items(order_id, product_id, quantity, unit_price) and products(product_id, product_name, category). Dates are text in YYYY-MM-DD form, so the examples run in SQLite and need small changes in other databases.
Start from the business question, then pick the pattern and the tools it needs.
| Business question | Pattern | Key tools |
|---|---|---|
| How did we change month on month? | Growth rate | GROUP BY month, LAG |
| How are we doing so far this year? | Running total | SUM() OVER (ORDER BY ...) |
| What share does each item contribute? | Percent of total | SUM() OVER () |
| Who are the top N in each group? | Top-N per group | ROW_NUMBER or RANK with PARTITION BY |
| Which items drive most of the revenue? | Pareto or ABC analysis | Cumulative share window |
| Is performance smooth or noisy? | Moving average | AVG() OVER with a frame |
Monthly revenue and the percentage change from the previous month.
WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month, SUM(order_total) AS revenue
FROM orders WHERE status <> 'cancelled'
GROUP BY strftime('%Y-%m', order_date)
)
SELECT month, revenue,
ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0
/ LAG(revenue) OVER (ORDER BY month), 1) AS mom_pct
FROM monthly
ORDER BY month;Cumulative revenue by month and each month's share of the overall total.
WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month, SUM(order_total) AS revenue
FROM orders WHERE status <> 'cancelled'
GROUP BY strftime('%Y-%m', order_date)
)
SELECT month, revenue,
SUM(revenue) OVER (ORDER BY month) AS running_total,
ROUND(revenue * 100.0 / SUM(revenue) OVER (), 1) AS pct_of_total
FROM monthly
ORDER BY month;ROW_NUMBER gives a unique rank inside each partition. Use RANK instead if ties should share a place.
WITH customer_revenue AS (
SELECT c.city, c.customer_id, c.name, SUM(o.order_total) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id AND o.status <> 'cancelled'
GROUP BY c.city, c.customer_id, c.name
)
SELECT *
FROM (
SELECT cr.*,
ROW_NUMBER() OVER (PARTITION BY city ORDER BY revenue DESC) AS rn
FROM customer_revenue cr
)
WHERE rn <= 3
ORDER BY city, rn;Ranks products by revenue and shows the cumulative share, so you can see how few products produce most of the revenue.
WITH product_revenue AS (
SELECT p.product_name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.product_name
)
SELECT product_name, revenue,
ROUND(SUM(revenue) OVER (ORDER BY revenue DESC) * 100.0
/ SUM(revenue) OVER (), 1) AS cumulative_pct
FROM product_revenue
ORDER BY revenue DESC;Smooths month-to-month noise using a window frame of the current and two previous months.
WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month, SUM(order_total) AS revenue
FROM orders WHERE status <> 'cancelled'
GROUP BY strftime('%Y-%m', order_date)
)
SELECT month, revenue,
ROUND(AVG(revenue) OVER (ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS moving_avg_3m
FROM monthly
ORDER BY month;Keep a file of queries that you have written and tested, with a sentence about the question each answers and the grain of the result. Over time it becomes a reference that speeds up new work and helps you explain your approach in interviews.
| Goal | Function |
|---|---|
| Rank with ties and gaps | RANK() |
| Rank with ties and no gaps | DENSE_RANK() |
| Unique row number | ROW_NUMBER() |
| Previous or next row value | LAG() and LEAD() |
| Running total or moving average | SUM() or AVG() with OVER and a frame |
| Split into equal groups | NTILE(n) |
| First or last value in a group | FIRST_VALUE() and LAST_VALUE() |
Want to answer business questions with SQL the way analysts do at work? Explore Uncodemy's Data Analytics course and learn from industry trainers.