start
here →

✦ Open source · Free forever · Community-built for the Indian analytics job market

The analytics career path nobody gave you. We did.

TapInterview is a free, open-source platform that removes the confusion from analytics careers — tells you what each domain actually looks like, teaches you what interviews really test, and gives you a free mock interview when you're ready.

why this exists

Companies don't define your analytics path. You do.

Analytics has three distinct careers inside it — Business, Product, and Data. Most platforms don't explain the difference. We do. Then we teach you the skills. Then we get you interview-ready. All of it, free.

🏢

Know what each analytics role actually looks like

Business, Product, or Data Analytics — we explain what analysts in each domain do on a real Tuesday at a real company, before you pick a direction.

📖

Written to be read, not watched

Not videos you pause at 40%. Not slides from a classroom. Structured written notes — the kind you'd want from the sharpest person in your batch.

🧩

Solve problems that mirror real interviews

Every worked example is built backward from what analytics roles at BFSI, consumer tech, and D2C companies actually ask. Not toy datasets — real scenarios.

🧭

Pick your domain — the platform adjusts to you

After 6 core modules, choose your domain and get case studies specific to that field. The curriculum follows your direction, not the other way around.

🎯

A free mock interview, not a certificate

Complete the curriculum. Request a mock interview. We set it up — free, no upgrade required. A real conversation, honest feedback, and a clear picture of where you stand.

💼

Open source — maintained by working analysts

TapInterview is community-built and open on GitHub. The curriculum is written by people who've been in the roles, sat in the interviews, and know what actually matters.

the
path

the curriculum

6 modules. Every interview will test these.

The foundation every analytics role expects — in the right order, written by people who've been in the interviews. Sign in to access. Sign up takes 2 minutes and costs nothing.

01

Database & DBMS

What a database is, how data is structured, how a DBMS works — the foundation everything else is built on

Foundation4 lessons · ~3h

Mark as complete

02

Basics of Excel

Formulas, functions, sorting, filtering, and basic charts — the first tool every analyst opens every single day

Foundation5 lessons · ~4h

Mark as complete

03

Basics of SQL

SELECT, WHERE, GROUP BY, JOINs — reading data from a database clearly and confidently, from scratch

Core skill5 lessons · ~5h

Mark as complete

04

Advanced Excel

Pivot tables, VLOOKUP, XLOOKUP, dynamic dashboards, data cleaning — the Excel skills that separate analysts from spreadsheet users

Core skill5 lessons · ~5h

Mark as complete

05

Advanced SQL

Subqueries, window functions, CTEs, performance thinking — the SQL that actually shows up in analytics interviews

Core skill6 lessons · ~6h

Mark as complete

06

Data Visualisation

Choosing the right chart, building dashboards, telling a story with numbers — the skill that turns analysis into decisions

Core skill4 lessons · ~4h

Mark as complete

after module 6 — choose your direction

Then choose your analytics domain.

Complete the 6 core modules. Then pick your direction. Each domain includes a day-in-the-life walkthrough of what analysts actually do, 10 fully worked case studies, and 5 problems for you to solve yourself — before requesting your free mock interview.

📊

Business Analytics

Revenue models, cost analysis, stakeholder reporting. What an analyst at a BFSI or D2C company does every single day — and what their interviews test.

10 worked + 5 practice

📱

Product Analytics

Funnels, retention curves, A/B test read-outs, feature adoption. What an analyst at a consumer tech company does when the PM asks "what does the data say?"

10 worked + 5 practice

🔬

Data Analytics

Large dataset queries, pipeline thinking, statistical reasoning. The analyst who builds what everyone else reads — and the one engineering teams most want to hire.

10 worked + 5 practice

let's get you started —

Create your free account

Everything on TapInterview is free. Under 60 seconds to set up.

Your full name

Please enter your name

I am currently —

🎓

A student

In college or university

💼

A professional

Currently employed

Please select one

Mobile number

🇮🇳 +91

Please enter a valid 10-digit number

No credit card. No subscription. We never share your number.

one last thing —

Enter the code

Code sent to your number.

Didn't get it? Resend code

✓

Welcome.

You're all set. Start with Module 1 and work through in order.

📦 Module 1 of 6

Database & DBMS

The foundation every analyst must have. Before you write your first SQL query, you need to understand how data is stored, organised, and managed.

⏱ ~45 min

🎯 Absolute Beginner

🧩 10 sections

💼 All analyst roles

1. What is Data?

Before databases, before SQL — there is just data. Data is raw, unprocessed facts and figures. On its own it has no meaning. Context turns it into information.

🔢

Data

Raw facts. E.g. 91-9876543210, 1200, 2024-03-15. Meaningless without context.

💡

Information

Data + Context. "Customer Rahul placed an order of ₹1,200 on 15 Mar 2024." Now it means something.

📊

Structured Data

Organised in rows and columns. Excel sheets, sales records, user signups. Easy to store and query.

🌀

Unstructured Data

No fixed format. WhatsApp messages, customer reviews, photos, audio calls. Harder to analyse.

🧠 Analyst Analogy

Think of data as a pile of newspapers on your desk. Information is when you read and understand an article. Insights are when you figure out why it matters for your business.

2. What is a Database?

A database is an organised collection of data stored and accessed electronically — designed to make data easy to retrieve, update, and manage even at massive scale.

⚠️ Common Misconception

Many beginners think Excel is a database. It's not. Excel is a spreadsheet tool — great for small datasets but it breaks under scale, has no concurrency, no access controls, and is prone to human error.

✅ Database (MySQL, PostgreSQL)

Handles millions to billions of rows
Multiple users simultaneously
Enforces rules (e.g. "age ≥ 0")
SQL queries in milliseconds
Data always consistent and reliable

❌ Excel File

Lags beyond 100,000 rows
One person edits at a time
Anyone can type anything — no rules
Manual lookups and pivot tables
Easy to accidentally corrupt data

📌 Why This Matters

As an analyst you'll spend most of your time querying databases using SQL. Understanding what a database is — and how it works — makes you 10× more effective.

3. What is a DBMS? Must Know

A database is the storage. A DBMS (Database Management System) is the software that lets you interact with it — the middleman between you and the data on disk.

1

You write a query

SELECT * FROM orders WHERE city = 'Delhi'

2

DBMS receives and parses it

Validates, optimises and plans execution.

3

Data fetched from storage

Only the relevant rows and columns — efficiently.

4

Result returned to you

A clean result table, ready for analysis. All in milliseconds.

DBMS	Type	Analyst Relevance
MySQL	Relational (RDBMS)	Very High — common in most companies
PostgreSQL	Relational (RDBMS)	Very High — advanced features, analyst-friendly
BigQuery	Cloud Data Warehouse	High — large-scale analytical workloads
Redshift	Cloud Data Warehouse	High — D2C, Fintech, E-commerce
SQL Server	Relational (RDBMS)	High — enterprises and banks

4. Types of Databases

🗂

Relational (RDBMS)

Tables with rows and columns linked via relationships. Query with SQL. Most common for analytics.

📄

Document (NoSQL)

JSON-like documents. Flexible schema for apps where data shape changes. MongoDB, Firestore.

📊

Data Warehouse

Optimised for analytical queries on large datasets. Columnar storage. BigQuery, Redshift, Snowflake.

🔑

Key-Value Store

Simplest form. Value accessed by unique key. Blazing fast. Used in caching. Redis, DynamoDB.

🎯 For 90% of Your Analyst Work

You'll work with Relational Databases or Data Warehouses. SQL is your core weapon — learn it and you're equipped for almost every analytics role.

5. The Relational Model — Tables, Rows & Columns

In a relational database, data is organised into tables. Each table represents one type of entity — customers, orders, products. This is the mental model you'll use every single day.

customers table

customer_id	name	city	signup_date	is_premium
1001	Rahul Sharma	Delhi	2023-01-15	TRUE
1002	Priya Mehta	Mumbai	2023-02-08	FALSE
1003	Arjun Nair	Bangalore	2023-02-20	TRUE

orders table

order_id	customer_id	order_date	amount	status
5001	1001	2024-01-10	₹55,000	Delivered
5002	1002	2024-01-11	₹3,998	Cancelled
5003	1001	2024-01-15	₹42,000	Delivered

🧠 See the Relationship?

customer_id appears in both tables. Customer 1001 (Rahul) placed orders 5001 and 5003. That shared column link is the entire magic of relational databases — and it's why SQL JOIN is your most powerful skill.

6. Keys & Constraints

Keys are how databases uniquely identify records and maintain relationships. Knowing them helps you write correct joins.

Key Type	What It Means	Analyst Relevance
Primary Key (PK)	Uniquely identifies every row. Must be unique and non-null.	High — always the join column
Foreign Key (FK)	Column referencing another table's PK. Enforces relationships.	High — foundation of JOINs
Composite Key	PK made of two or more columns together	High — common in transaction tables
Surrogate Key	Auto-generated ID (AUTO_INCREMENT)	High — most modern databases use this

SQLCREATE TABLE orders (
  order_id     INT PRIMARY KEY,
  customer_id  INT NOT NULL,
  amount       DECIMAL(10,2) CHECK (amount > 0),
  status       VARCHAR(20) DEFAULT 'Pending',
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

7. Normalization

Normalization reduces redundancy and improves data integrity by splitting data into related tables instead of repeating it everywhere.

🧠 Simple Analogy

Imagine writing a customer's city in every single order row. If the city changes, you update 10,000 rows. Normalization says: store the city once in the customers table, reference customer_id everywhere else.

Normal Form	Rule	Fix Applied
1NF	Each cell holds one value. No repeating groups.	Split multi-value cells into separate rows.
2NF	1NF + every non-key column depends on the whole PK.	Move product info to a products table.
3NF	2NF + no column depends on another non-key column.	Move customer info to a customers table.

8. ACID Properties

ACID is a set of four properties that guarantee a database transaction is processed reliably — the reason databases are trusted for critical operations.

⚛️

A — Atomicity

All or nothing. A ₹500 transfer must debit AND credit — if either fails, both roll back. No half-completed transactions.

✅

C — Consistency

A transaction brings the DB from one valid state to another. All rules and constraints are respected.

🔒

I — Isolation

Concurrent transactions happen independently. Two people booking the last flight seat won't both succeed.

💾

D — Durability

Once committed, data persists — even if the system crashes immediately after. Your UPI payment is saved permanently.

9. Real-World Scenarios

🛒 E-Commerce (Flipkart) — Top 5 cities by revenue

orders has customer_id, amount, order_date. customers has customer_id, city. JOIN on customer_id → filter last month → GROUP BY city → SUM amount. An Excel sheet with 10M rows would crash doing this.

📱 Product Analytics (Swiggy) — First session to first order

sessions table: user_id + session_start_time. orders table: user_id + order_created_at. ACID ensures timestamps are accurate even during Diwali night peak traffic.

📊 Data Analytics (Paytm) — Payment failure analysis

500M rows in a Data Warehouse (BigQuery/Redshift) — not MySQL. OLTP for live transactions, OLAP for analytics. Knowing the difference is what makes you dangerous.

10. Quick Recap Quiz

Question 1 of 4

A table has 5 columns and 1,000 rows. How many cells does it have?

A

1,000

B

5,000

C

5,005

D

Cannot be determined

✅ Correct! Cells = Rows × Columns = 1,000 × 5 = 5,000.

❌ Cells = Rows × Columns = 1,000 × 5 = 5,000.

Question 2 of 4

In the orders table, customer_id references customer_id in customers. What kind of key is this?

A

Primary Key

B

Foreign Key

C

Composite Key

D

Candidate Key

✅ Correct! A Foreign Key references the Primary Key of another table — the foundation of SQL JOINs.

❌ A column referencing another table's PK is a Foreign Key. The PK of orders is order_id.

Question 3 of 4

Analytics queries on live MySQL are taking 20 minutes. What do you recommend?

A

Move everything to Excel

B

Ask developers to write faster code

C

Migrate analytics to a Data Warehouse like BigQuery or Redshift

D

Delete old data

✅ Correct! OLTP databases like MySQL are for transactions. For analytics workloads — a Data Warehouse (OLAP) is the right solution.

❌ Migrate to a Data Warehouse. BigQuery or Redshift is the industry-standard solution for analytical workloads.

Question 4 of 4

A UPI payment is initiated. Server crashes mid-transaction. The user is debited but recipient doesn't get the money. Which ACID property was violated?

A

Atomicity

B

Consistency

C

Isolation

D

Durability

✅ Correct! Atomicity = all or nothing. The debit without the credit is a classic Atomicity violation.

❌ This is an Atomicity violation — either all parts of a transaction complete, or none do.

🎯 Module 1 — What You've Learned

Data vs Information: Raw facts vs data in context.
Database vs Excel: Databases handle scale, concurrency, and rules that spreadsheets can't.
DBMS: The software (MySQL, BigQuery) that executes your queries.
Relational Model: Tables of rows and columns, linked via keys.
Keys: Primary Keys identify rows; Foreign Keys link tables. JOINs work because of these.
Normalization: Avoid redundancy by splitting data into related tables.
ACID: Atomicity, Consistency, Isolation, Durability — the four guarantees that make databases trustworthy.
OLTP vs OLAP: Transactional databases for operations; Data Warehouses for analytics.

Up next — Module 2

Basics of Excel — The Analyst's First Toolkit

→

📊 Module 2 of 6

Basics of Excel

Before SQL, before Python — every analyst lives in Excel. Learn the formulas, pivots, and workflows that make analysts 10× faster than everyone else in the room.

⏱ ~60 min

🎯 Beginner

🧩 12 sections

💼 All analyst roles

1. Why Excel for Analysts?

⚡

Speed

For datasets under 100K rows, Excel is often faster than writing a SQL query. Instant calculations and ad-hoc analysis in seconds.

🤝

Universality

Every stakeholder — your manager, the CFO, the sales team — can open an Excel file. It's the bridge between your analysis and their understanding.

🔗

SQL Output → Excel

You'll query a database, export to CSV, then clean, model, and present in Excel. The two skills are complementary, not competing.

🎯

Interview Reality

Most analyst interviews include an Excel round — a take-home assignment with a messy CSV. VLOOKUP, pivot tables, and a clean dashboard are what they're testing.

2. Navigating the Excel Interface

Element	What It Is	Analyst Use
Cell	Intersection of row and column (e.g. A1)	Where every value, formula, and label lives
Row	Horizontal series, numbered 1, 2, 3…	Each row = one record (one order, one customer)
Column	Vertical series, lettered A, B, C…	Each column = one field (name, amount, date)
Worksheet	A single tab within a workbook	Separate sheets for Raw Data, Calculations, Dashboard
Formula Bar	Shows the content of the selected cell	View and edit formulas here — not in the cell
Name Box	Shows the current cell reference (top-left)	Navigate directly to any cell by typing its address

3. Essential Formulas Every Analyst Must Know Must Know

Formula	What It Does	Example
SUM()	Adds all values in a range	=SUM(C2:C1000) → Total revenue
COUNT()	Counts numeric values	=COUNT(A2:A1000) → Number of orders
COUNTA()	Counts non-empty cells	=COUNTA(B2:B1000) → Number of customers
AVERAGE()	Mean of a range	=AVERAGE(C2:C1000) → Average order value
SUMIF()	Sum where condition is true	=SUMIF(D:D,"Delhi",C:C) → Delhi revenue
SUMIFS()	Sum with multiple conditions	=SUMIFS(C:C,D:D,"Delhi",E:E,"Delivered")
COUNTIF()	Count where condition is true	=COUNTIF(E:E,"Delivered") → Delivered orders
COUNTIFS()	Count with multiple conditions	=COUNTIFS(D:D,"Mumbai",E:E,"Cancelled")
MIN() / MAX()	Smallest / largest value	=MAX(C:C) → Highest order
ROUND()	Round to decimal places	=ROUND(C2/1000,1) → Revenue in thousands

📌 Always Use SUMIFS over SUMIF

SUMIFS handles multiple conditions AND works with just one condition — making SUMIF redundant. Learn SUMIFS (with an S) always. Same logic applies to COUNTIFS.

4. Logical Functions — IF, AND, OR, IFS

Excel-- Basic IF: categorise order size
=IF(C2>10000,"High Value","Standard")

-- IFS: cleaner multi-condition (Excel 2019+)
=IFS(C2>50000,"Premium",C2>10000,"High",C2>0,"Standard")

-- AND: both conditions must be true
=IF(AND(D2="Delhi",E2="Delivered"),"Delhi Delivered","Other")

-- OR: at least one condition must be true
=IF(OR(D2="Delhi",D2="Mumbai"),"Metro","Non-Metro")

5. Lookup Functions — VLOOKUP, INDEX-MATCH, XLOOKUP Must Know

Lookup functions enrich one table with data from another — the spreadsheet equivalent of a SQL JOIN.

Excel-- VLOOKUP: lookup customer city by ID
=VLOOKUP(B2,Customers!A:C,3,0)
-- B2=lookup value, A:C=table, 3=return 3rd column, 0=exact match

-- XLOOKUP: modern standard (Excel 365/2021+)
=XLOOKUP(B2,Customers!A:A,Customers!C:C,"Not Found",0)
-- Cleaner syntax, works left-to-right or right-to-left

-- INDEX-MATCH: professional's choice, always works
=INDEX(Customers!C:C,MATCH(B2,Customers!A:A,0))

❌ VLOOKUP Weaknesses

Only looks right — can't fetch columns to the left
Insert/delete columns breaks col_index_num
Returns only the first match

✅ XLOOKUP / INDEX-MATCH

Works in any direction
Doesn't break on column inserts
More readable and flexible

6. Text & Date Functions

Function	What It Does	Example
TRIM()	Removes leading/trailing spaces	=TRIM(A2) — fixes copy-paste whitespace
UPPER() / LOWER()	Changes case	=UPPER(A2) — standardise city names
LEN()	Returns character count	=LEN(A2) — flag wrong-length phone numbers
SUBSTITUTE()	Replaces text	=SUBSTITUTE(A2,"₹","INR")
IFERROR()	Hides errors	=IFERROR(VLOOKUP(...),"Not Found")
TODAY()	Today's date	=TODAY()-A2 → days since signup
YEAR() / MONTH()	Extracts year/month from a date	=YEAR(A2), =MONTH(A2)
DATEDIF()	Difference between two dates	=DATEDIF(A2,TODAY(),"M") → months elapsed
EOMONTH()	Last day of the month	=EOMONTH(A2,0) → for month-end reports

📌 Always Extract YEAR and MONTH

When you get a raw dataset with an order_date column, immediately create two helper columns: =YEAR(order_date) and =MONTH(order_date). This lets you group by month in pivot tables instantly.

7. Pivot Tables — The Analyst's Superpower Must Know

🧠 What a Pivot Table Actually Does

A pivot table is a drag-and-drop version of SQL's GROUP BY. "Group my data by City and sum Revenue for each group." What takes 20 minutes of formulas takes 20 seconds in a pivot table.

1

Click anywhere inside your data table

Make sure headers are in Row 1 with no blank rows or columns within the data.

2

Insert → PivotTable → New Worksheet

Excel auto-selects your data range. New worksheet keeps things clean.

3

Drag fields into the four areas

Rows = group by (City, Category). Values = calculate (SUM of Revenue). Filters = slice the entire pivot.

4

Change the Value calculation

Default SUMs numbers and COUNTs text. Value Field Settings → change to Average, Max, Count, etc.

5

Right-click → Refresh when data changes

Pivots do NOT auto-refresh. Right-click → Refresh or Alt+F5 after adding new data.

🔪

Slicers

Insert → Slicer. Clickable filter buttons for your pivot. Great for interactive dashboards shared with non-analysts.

📅

Timeline

Insert → Timeline. Date-based slider to filter by month/quarter/year. Perfect for time-series analysis.

📊

PivotChart

Right-click pivot → PivotChart. A chart that auto-updates with the pivot — the starting point of Excel dashboards.

🔢

Calculated Field

Add custom formulas inside a pivot. Revenue ÷ Orders = AOV, computed dynamically within the pivot.

8. Charts — Making Data Visual

Chart Type	Use When	Analyst Example
📊 Bar / Column	Comparing categories	Revenue by city, Sales by product
📈 Line	Trends over time	Monthly DAU, Weekly orders
🍕 Pie	Parts of a whole (max 5 slices)	Revenue share by category
💧 Waterfall	Cumulative effect of components	Revenue bridge: last month → this month
🌡️ Heatmap	Two-dimensional patterns	Hour × day order volume grid

9. Data Cleaning — The Unglamorous but Critical Skill

Problem	How to Spot It	Fix
Extra whitespace	COUNTIF gives unexpected results	=TRIM(A2) on the whole column
Inconsistent case	"Delhi" and "delhi" treated differently	=UPPER(A2) or =PROPER(A2)
Dates stored as text	Date column shows left-aligned text	Text to Columns wizard → Date format
Numbers as text	SUM returns 0, numbers left-aligned	=VALUE(A2) or =A2*1
Duplicate rows	COUNTIF shows count > 1	Data → Remove Duplicates
#N/A errors	VLOOKUP can't find a match	=IFERROR(VLOOKUP(...),"Missing")

10. Power Shortcuts — Work 3× Faster

Shortcut	What It Does
Ctrl + T	Convert range to Excel Table (auto-expands)
Ctrl + Shift + L	Add/remove filters on headers
Alt + F5	Refresh all pivot tables
F4	Toggle absolute/relative references ($A$1 ↔ A1)
Ctrl + D	Fill down — copy formula from cell above
Ctrl + `	Show all formulas in the sheet
Alt + =	Auto-SUM a column instantly
Ctrl + Shift + End	Jump to last used cell
Ctrl + 1	Open Format Cells dialog

11. Real-World Scenarios

🛒 Business Analyst — Monthly Revenue Dashboard

You receive 50,000 rows of raw orders. Step 1: TRIM all text, fix date formats. Step 2: Add YEAR() and MONTH() helpers. Step 3: XLOOKUP city names from customer list. Step 4: Pivot table — rows: city, columns: month, values: SUM of revenue. Step 5: Slicers for Category and Status, PivotChart for trend. Result: a 5-tab workbook delivered in 2 hours.

📱 Product Analyst — Feature Adoption Tracking

Track new "Reorder" button adoption in first 14 days by city and device. Export event logs → COUNTIFS to count "reorder_clicked" events per user per day → Pivot table: rows=city, columns=device, values=COUNTA of unique users → Line chart showing daily adoption with 7-day rolling average.

12. Quiz

Question 1 of 4

You want to sum revenue only for orders from Delhi that are "Delivered". Which formula is correct?

A

=SUMIF(D:D,"Delhi",C:C)

B

=SUMIFS(C:C,D:D,"Delhi",E:E,"Delivered")

C

=SUM(IF(D:D="Delhi",C:C))

D

=COUNTIFS(D:D,"Delhi",E:E,"Delivered")

✅ Correct! SUMIFS: sum_range first, then pairs of (criteria_range, criteria). Handles multiple conditions.

❌ Use SUMIFS for multiple conditions: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2).

Question 2 of 4

Your VLOOKUP returns #N/A for some rows even though the customer exists in the lookup table. Most likely cause?

A

The col_index_num is wrong

B

Extra whitespace — "Delhi" ≠ " Delhi"

C

VLOOKUP doesn't work on large datasets

D

The table array isn't sorted

✅ Correct! Extra whitespace is the #1 cause of #N/A errors. Always TRIM() your lookup column and lookup table before using VLOOKUP.

❌ The most common cause is extra whitespace. Wrap in TRIM(). The table doesn't need to be sorted when using exact match (0).

Question 3 of 4

Numbers in Amount column are stored as text (left-aligned). Your pivot table totals are wrong. Fastest fix?

A

Retype all numbers manually

B

Apply Number format from Format Cells

C

Helper column: =VALUE(A2) or =A2*1, then paste values

D

Use IFERROR to ignore the text values

✅ Correct! =VALUE(A2) or =A2*1 converts text "1200" to number 1200. Then paste as values to replace the original.

❌ Changing format doesn't convert text to numbers. Use =VALUE(A2) or multiply by 1 to actually convert.

Question 4 of 4

You add new rows to your pivot table's source data. How do you get the pivot to reflect the new data?

A

Nothing — pivot tables update automatically

B

Delete and rebuild the pivot from scratch

C

Right-click the pivot → Refresh (or Alt+F5)

D

Save and reopen the file

✅ Correct! Pivot tables require a manual refresh. Right-click → Refresh or Alt+F5. Pro tip: use Ctrl+T (Excel Table) on your source so the range auto-expands.

❌ Pivots require manual refresh. Right-click → Refresh or Alt+F5.

🎯 Module 2 — What You've Learned

SUMIFS / COUNTIFS: Multi-condition aggregation — use these always, not their singular variants.
Logical functions: IF, AND, OR, IFS — build business rules directly into your spreadsheet.
XLOOKUP: The modern standard for enriching one table with data from another.
Text & Date: TRIM, UPPER, YEAR, MONTH — the cleaning toolkit every analyst needs daily.
Pivot Tables: Drag-and-drop GROUP BY. Summarise 100K rows in 20 seconds.
Data cleaning: TRIM, VALUE(), Remove Duplicates, IFERROR. 80% of analyst time lives here.
Shortcuts: Ctrl+T, Alt+F5, F4, Ctrl+D — they compound over a career.

Up next — Module 3

Basics of SQL — Query Data from Any Database

→

🗄️ Module 3 of 6

Basics of SQL

SQL is on every analytics job description, tested in every interview, and used every day in every analytics role. Learn it once — use it everywhere.

⏱ ~75 min

🎯 Beginner to Intermediate

🧩 13 sections

💼 All analyst roles

1. What is SQL — and Why Does Every Analyst Need It?

🗣️

SQL is English-like

SELECT name FROM customers WHERE city = 'Delhi' — you can almost read it as a sentence. One of the most accessible technical skills.

🌍

Universal

SQL works on MySQL, PostgreSQL, BigQuery, Redshift, SQL Server. Learn it once, use it everywhere.

⚡

Scale

Where Excel breaks at 100K rows, SQL handles billions of rows in seconds. No size limits.

🎯

Interview Reality

SQL is the most tested skill in analytics interviews. Every company — startup to enterprise — tests SQL. Non-negotiable.

2. Your Practice Schema

All examples in this module use a simple e-commerce schema. Two tables, real-world structure.

customer_id	name	city	signup_date	is_premium
1001	Rahul Sharma	Delhi	2023-01-15	TRUE
1002	Priya Mehta	Mumbai	2023-02-08	FALSE
1003	Arjun Nair	Bangalore	2023-02-20	TRUE
1004	Sneha Patel	Ahmedabad	2023-03-01	FALSE

order_id	customer_id	amount	order_date	status
5001	1001	55000	2024-01-10	Delivered
5002	1002	3998	2024-01-11	Cancelled
5003	1001	42000	2024-01-15	Delivered
5004	1003	55000	2024-01-18	Delivered

3. SELECT & FROM — The Foundation Must Know

SQL-- Select all columns
SELECT * FROM orders;

-- Select specific columns only
SELECT order_id, customer_id, amount FROM orders;

-- Rename columns with AS (aliases)
SELECT
  order_id,
  amount * 1.18 AS amount_with_gst,
  status AS order_status
FROM orders;

⚠️ Avoid SELECT * in Production

On columnar databases (BigQuery, Redshift), SELECT * scans every column. Very expensive. Always name the columns you need explicitly.

4. WHERE — Filtering Rows Must Know

SQL-- Single condition
SELECT * FROM orders WHERE status = 'Delivered';

-- AND: both must be true
SELECT * FROM orders WHERE status = 'Delivered' AND amount > 10000;

-- IN: cleaner than multiple ORs
SELECT * FROM customers
WHERE city IN ('Delhi', 'Mumbai', 'Bangalore');

-- BETWEEN for ranges
SELECT * FROM orders WHERE amount BETWEEN 1000 AND 50000;

-- LIKE for pattern matching
SELECT * FROM customers WHERE name LIKE 'Rahul%';

-- IS NULL: find missing values
SELECT * FROM orders WHERE status IS NULL;

⚠️ AND vs OR Precedence — The #1 Logic Bug

WHERE city = 'Delhi' AND status = 'Delivered' OR amount > 50000 reads as: "(Delhi AND Delivered) OR (amount > 50000)" — pulling ALL high-amount orders regardless of city. Always use parentheses to be explicit.

5. ORDER BY & LIMIT

SQL — Top 3 highest-value delivered ordersSELECT order_id, customer_id, amount
FROM orders
WHERE status = 'Delivered'
ORDER BY amount DESC -- highest first
LIMIT 3; -- show only top 3

6. Aggregate Functions — Summarising Data Must Know

SQL — Key business metrics in one querySELECT
  COUNT(*)                     AS total_orders,
  COUNT(DISTINCT customer_id) AS unique_customers,
  SUM(amount)                 AS total_revenue,
  AVG(amount)                 AS avg_order_value,
  MIN(amount)                 AS smallest_order,
  MAX(amount)                 AS largest_order
FROM orders WHERE status = 'Delivered';

📌 COUNT(*) vs COUNT(column)

COUNT(*) counts ALL rows including NULLs. COUNT(column_name) counts only rows where that column is NOT NULL. This matters when your data has missing values.

7. GROUP BY & HAVING Must Know

SQL — Revenue by statusSELECT status, COUNT(*) AS num_orders, SUM(amount) AS total_revenue
FROM orders
GROUP BY status
ORDER BY total_revenue DESC;

⚠️ The Golden Rule of GROUP BY

Every column in SELECT must either be in GROUP BY, or wrapped in an aggregate function (SUM, COUNT, AVG, etc.).

SQL — HAVING: customers who spent more than ₹50,000SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS ltv
FROM orders
WHERE status = 'Delivered' -- WHERE filters ROWS (before grouping)
GROUP BY customer_id
HAVING SUM(amount) > 50000 -- HAVING filters GROUPS (after aggregating)
ORDER BY ltv DESC;

8. JOINs — Combining Tables Must Know

🔗

INNER JOIN

Returns only rows that match in both tables. Most common. Customers with no orders won't appear.

⬅️

LEFT JOIN

All rows from the left table + matched rows from right. NULLs where no match. Use to find non-matching records.

🔄

FULL OUTER JOIN

All rows from both tables. NULLs where no match on either side. Used for reconciliation.

✖️

CROSS JOIN

Every combination of rows from both tables. Useful for generating test data or pairing options.

SQL — INNER JOIN: enrich orders with customer detailsSELECT o.order_id, c.name AS customer_name, c.city, o.amount, o.status
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id
ORDER BY o.order_id;

SQL — LEFT JOIN: customers who have NEVER placed an orderSELECT c.customer_id, c.name, c.city, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL; -- NULL = no order for this customer

9. Subqueries & CTEs Pro Level

SQL — CTE: orders above the average order valueWITH avg_order AS (
SELECT AVG(amount) AS avg_amt FROM orders
)
SELECT o.order_id, o.customer_id, o.amount
FROM orders o, avg_order a
WHERE o.amount > a.avg_amt
ORDER BY o.amount DESC;

✅ CTEs vs Subqueries

Use CTEs when the logic is complex, when you reference the same subquery multiple times, or when readability matters (it always does). Use subqueries for simple one-off filtering in a WHERE clause.

10. Useful SQL Functions

Function	What It Does	Example
ROUND()	Round to decimal places	ROUND(AVG(amount),2)
COALESCE()	Return first non-NULL value	COALESCE(phone, email, 'Unknown')
NULLIF()	Return NULL if two values are equal	NULLIF(amount, 0) — avoids divide-by-zero
CASE WHEN	If-then-else logic in SQL	CASE WHEN amount > 10000 THEN 'High' ELSE 'Low' END
CONCAT()	Join strings together	CONCAT(first_name,' ',last_name)
DATE_TRUNC()	Truncate date to month/week/year	DATE_TRUNC('month', order_date)
EXTRACT()	Get part of a date	EXTRACT(MONTH FROM order_date)

11. SQL Execution Order

SQL doesn't execute in the order you write it. Memorise the actual order — it explains most SQL errors.

1

FROM & JOINs

Identify tables and combine them.

2

WHERE

Filter individual rows. Aggregate functions NOT available here yet.

3

GROUP BY

Group the filtered rows by specified columns.

4

HAVING

Filter groups. Aggregate functions ARE available here.

5

SELECT

Choose which columns (or expressions) to include.

6

ORDER BY → LIMIT

Sort the output, then cap row count.

📌 Why This Matters

This is why you can't use SELECT aliases in a WHERE clause (SELECT hasn't run yet), but CAN use them in ORDER BY. And why aggregates work in HAVING but not WHERE.

12. Real-World SQL Scenarios

🛒 Top 5 Cities by Revenue Last Month

SELECT c.city, COUNT(*) AS num_orders, SUM(o.amount) AS revenue
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'Delivered'
AND o.order_date >= '2024-01-01' AND o.order_date < '2024-02-01'
GROUP BY c.city ORDER BY revenue DESC LIMIT 5;

📱 Cancellation Rate by City

SELECT c.city, COUNT(*) AS total,
ROUND(100.0 * SUM(CASE WHEN o.status = 'Cancelled' THEN 1 ELSE 0 END) / COUNT(*), 1) AS cancel_pct
FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.city ORDER BY cancel_pct DESC;

13. Quiz

Question 1 of 4

You want to count only orders where status is NOT NULL. Which is correct?

A

COUNT(*)

B

COUNT(status)

C

COUNT(1)

D

SUM(status)

✅ Correct! COUNT(column_name) skips NULL values and counts only non-null rows.

❌ COUNT(*) counts all rows including NULLs. COUNT(column_name) counts only non-NULL values.

Question 2 of 4

You want to filter groups where total revenue exceeds ₹100,000. Which clause?

A

WHERE SUM(amount) > 100000

B

HAVING SUM(amount) > 100000

C

FILTER SUM(amount) > 100000

D

ORDER BY SUM(amount) > 100000

✅ Correct! HAVING filters after GROUP BY — the only place you can use aggregate functions as filter conditions.

❌ WHERE filters rows before grouping and can't use aggregates. HAVING filters groups after aggregation.

Question 3 of 4

Which JOIN returns all customers, even if they've placed no orders (showing NULL for order columns)?

A

INNER JOIN

B

LEFT JOIN (customers on the left)

C

RIGHT JOIN (customers on the right)

D

CROSS JOIN

✅ Correct! LEFT JOIN keeps all rows from the left table (customers) and fills NULLs where no matching order exists.

❌ INNER JOIN would exclude customers with no orders. LEFT JOIN (customers on left) keeps all customers and fills NULLs.

Question 4 of 4

In which order does SQL actually execute query clauses?

A

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY

B

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

C

WHERE → FROM → GROUP BY → SELECT → HAVING → ORDER BY

D

FROM → SELECT → WHERE → GROUP BY → HAVING → ORDER BY

✅ Correct! FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. This is why SELECT aliases can be used in ORDER BY but not WHERE.

❌ Correct order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

🎯 Module 3 — What You've Learned

SELECT & FROM: The foundation of every query. Name columns explicitly — avoid SELECT *.
WHERE: Filter rows before aggregation. Use parentheses with AND/OR to avoid logic bugs.
Aggregates: COUNT, SUM, AVG, MIN, MAX — the building blocks of every business metric.
GROUP BY & HAVING: GROUP BY = pivot in SQL. HAVING filters the results of GROUP BY.
JOINs: INNER for matching rows, LEFT to keep all rows from the left including non-matches.
CTEs: WITH clause makes complex queries readable. Prefer over nested subqueries.
Execution Order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Memorise this.

Up next — Module 4

Advanced Excel — Power Query, Dynamic Arrays & Dashboards

→

📊 Module 4 of 6

Advanced Excel

Beyond formulas. Power Query, dynamic arrays, and dashboard architecture — the skills that turn an Excel user into an Excel analyst.

⏱ ~60 min

🎯 Intermediate

🧩 10 sections

💼 Business & Product Analysts

1. What "Advanced" Actually Means

🔄

Automation

Reports that update with one click. Not rebuilding pivots and charts from scratch every month.

🏗️

Architecture

Separate sheets for raw data, calculations, and the dashboard. A structure anyone can maintain.

📐

Scalability

Workbooks that handle 1 million rows without lagging, using Power Query instead of manual paste.

🎨

Presentation

Dashboards non-analysts can interact with using slicers, dropdowns, and clean design.

2. Named Ranges & Excel Tables

📌 Why Excel Tables (Ctrl+T) Change Everything

When you press Ctrl+T, Excel converts your data to a structured Table that auto-expands when you add rows. Column names become usable in formulas (Orders[Amount] instead of C2). Pivot tables automatically include new data. The single biggest productivity upgrade for analysts.

Excel-- Range formula (breaks when rows added)
=SUM(C2:C1000)

-- Table formula (auto-expands forever)
=SUM(Orders[Amount])
=SUMIFS(Orders[Amount], Orders[City], "Delhi")

-- Named ranges make formulas self-documenting
=IF(C2 > Premium_Threshold, "Premium", "Standard")
=C2 * (1 + GST_Rate)

3. Power Query — Your Data Pipeline Engine Must Know

🧠 What Power Query Does

Think of Power Query as a recording of all your data cleaning steps. Month 1: you clean the CSV manually. Month 2 onward: drop the new CSV, press Ctrl+Alt+F5 — all steps replay in seconds.

1

Connect — Data → Get Data

Connect to CSV, Excel, database, SharePoint, or web URL. See a preview of raw data.

2

Transform — Clean visually

Change types, filter rows, remove columns, unpivot, merge. Each step is recorded in "Applied Steps."

3

Load — Close & Load to worksheet

Load to an Excel Table. Pivot tables reference this — they update when data does.

4

Refresh — Ctrl+Alt+F5

Drop new file in same folder, press Ctrl+Alt+F5. All queries refresh, all pivots update. 10 seconds.

Transformation	What It Does	When to Use
Change Type	Set correct data type (date, number, text)	Every time — always set types explicitly
Remove Duplicates	Keep only unique rows	Deduplicating customer/product lists
Unpivot Columns	Convert wide to long format	Restructuring data for pivot tables
Merge Queries	JOIN two tables (like SQL JOIN)	Enriching orders with customer data
Group By	Aggregate data (like SQL GROUP BY)	Pre-aggregate before loading to Excel

4. Dynamic Array Functions Excel 365

One formula can return multiple values that "spill" into adjacent cells — no more Ctrl+Shift+Enter or helper columns.

Function	What It Does	Example
FILTER()	Return rows matching a condition	=FILTER(Orders, Orders[City]="Delhi")
SORT()	Sort a range or array	=SORT(Orders[Amount],1,-1)
UNIQUE()	Return distinct values	=UNIQUE(Orders[City])
XLOOKUP()	Flexible lookup returning arrays	=XLOOKUP(A2,Customers[ID],Customers[[City]:[Email]])
SEQUENCE()	Generate a number sequence	=SEQUENCE(12,1,1) → months 1–12

5. Advanced Pivot Techniques

Technique	How	When to Use
Calculated Field	PivotTable Analyze → Fields, Items & Sets	Add Revenue ÷ Orders = AOV inside the pivot
Show Values As	Right-click a value → Show Values As	% of Grand Total, Running Total, Rank
GETPIVOTDATA	=GETPIVOTDATA("Amount",A3,"City","Delhi")	Pull specific pivot values into KPI cards
Report Connections	Right-click slicer → Report Connections	One slicer controls multiple pivots simultaneously

6. Data Validation & Form Controls

Control	Insert Via	Use Case
Dropdown List	Data → Data Validation → List	City selector driving SUMIFS formulas in a KPI card
Slicer	Insert → Slicer	Visual filter buttons for pivots — better UX than row filters
Timeline	Insert → Timeline	Date range slider for time-series analysis

7. Dashboard Architecture

1

RAW_DATA sheet (hidden)

Power Query output. Never touch manually. Single source of truth. Hide from stakeholders.

2

CALCULATIONS sheet (hidden)

All pivot tables, SUMIFS, intermediate calculations. Logic lives here. Hide from stakeholders.

3

DASHBOARD sheet (visible)

KPI cards via GETPIVOTDATA. Charts via pivot tables. Slicers connected via Report Connections. What stakeholders see.

8. Building the Dashboard — Step by Step

1

Define the questions first

Write them down before opening Excel. "What's this month's revenue vs last month?" These become your KPI cards and charts.

2

Load clean data via Power Query → RAW_DATA

Connect, transform, load to RAW_DATA as an Excel Table.

3

Build pivot tables in CALCULATIONS

One pivot per question. Revenue by month, by city, cancellations by category.

4

Build DASHBOARD sheet

KPI cards via GETPIVOTDATA. PivotCharts. Slicers with Report Connections.

5

Test refresh

Add a new row → Ctrl+Alt+F5 → verify everything updates. Fix broken references.

9. What-If Analysis

Tool	What It Does	Analyst Use Case
Goal Seek	Set output target, find required input	"We need ₹10Cr. AOV = ₹800. How many orders?"
Scenario Manager	Save multiple named input sets	Best / Base / Worst case revenue projections
Data Table (1-var)	Output for every value of one input	Revenue as discount changes 0% → 30%
Data Table (2-var)	Output for combinations of two inputs	Revenue across all combinations of price and volume

10. Real-World Scenarios

🛒 Monthly Revenue Report Automation

Before: 6 hours combining 4 regional CSVs manually every month.
After: Power Query connects to the folder. All transformations applied automatically. Drop new CSVs → Ctrl+Alt+F5 → done in 10 seconds.

📱 User Retention Cohort Analysis

Situation: Product team wants cohort retention — users who signed up in January, what % were active in February, March, April?
Solution: Power Query loads events, groups by user_id + month. COUNTIFS populates the cohort matrix. Conditional formatting heatmap on DASHBOARD — green for high retention, red for low.

🎯 Module 4 — What You've Learned

Excel Tables (Ctrl+T): Auto-expanding ranges. Never use raw ranges in production workbooks.
Power Query: GUI ETL. Connect → Transform → Load → Refresh. Monthly automation with zero VBA.
Dynamic Arrays: FILTER, SORT, UNIQUE, XLOOKUP, SEQUENCE — one formula, spilled results.
Dashboard Architecture: RAW_DATA → CALCULATIONS → DASHBOARD. Always three layers.
Report Connections: One slicer controlling all pivot tables simultaneously.
What-If Analysis: Goal Seek and Scenario Manager for business planning and target-setting.

Up next — Module 5

Advanced SQL — Window Functions, CTEs & Query Optimisation

→

🗄️ Module 5 of 6

Advanced SQL

Window functions, CTEs, query optimisation, and the patterns every senior analyst uses daily. This is what separates a junior analyst from someone who can answer any data question.

⏱ ~75 min

🎯 Intermediate to Advanced

🧩 10 sections

💼 All analyst roles

1. Why Advanced SQL Matters

📊

Window Functions

Running totals, rankings, period-over-period comparisons — without GROUP BY collapsing your rows. The most interview-tested advanced SQL topic.

🏗️

CTEs

Break complex queries into named, readable steps. The difference between a query that takes 10 minutes to understand and one that's instantly clear.

⚡

Optimisation

Write SQL that doesn't destroy performance on 100M-row tables. What gets you respected by the data engineering team.

🔁

Patterns

Deduplication, sessionisation, funnel analysis — recurring patterns that appear in nearly every analytics job.

2. Window Functions — The Anatomy Must Know

A window function performs a calculation across rows related to the current row — without collapsing those rows like GROUP BY does.

SQL — Window function anatomyFUNCTION_NAME(column) OVER (
  PARTITION BY group_column -- reset window for each group (optional)
  ORDER BY sort_column -- order rows within window (required for ranking)
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- frame (optional)
)

📌 PARTITION BY vs GROUP BY

GROUP BY collapses all rows in a group into one result row. PARTITION BY keeps ALL rows but computes the function within each partition. To see every order AND its customer's total spend — use a window function.

3. Ranking Functions Must Know

SQL — Rank customers by lifetime valueSELECT customer_id, SUM(amount) AS ltv,
  ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS row_num, -- always unique
  RANK() OVER (ORDER BY SUM(amount) DESC) AS rank_pos, -- gaps after ties: 1,1,3
  DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dense_pos -- no gaps: 1,1,2
FROM orders WHERE status = 'Delivered' GROUP BY customer_id;

Function	Ties?	Use When
ROW_NUMBER()	Always unique — no ties possible	Deduplication, picking exactly top N rows
RANK()	Same rank, then gaps (1,1,3)	Sports-style leaderboards
DENSE_RANK()	Same rank, no gaps (1,1,2)	Most analyst use cases
NTILE(n)	Divides into n equal buckets	Quartile/decile analysis

4. Aggregate Window Functions Must Know

SQL — Running total + 3-day moving averageSELECT order_date, SUM(amount) AS daily_rev,
SUM(SUM(amount)) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
AVG(SUM(amount)) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3d
FROM orders WHERE status = 'Delivered'
GROUP BY order_date ORDER BY order_date;

5. LAG & LEAD — Period-Over-Period Must Know

SQL — Month-over-month revenue growthWITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
  FROM orders WHERE status = 'Delivered' GROUP BY 1
)
SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  ROUND(100.0*(revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS mom_growth_pct
FROM monthly ORDER BY month;

📌 NULLIF() to Avoid Division by Zero

NULLIF(value, 0) returns NULL instead of 0 — dividing by NULL returns NULL (not an error), which is correct for a missing prior period.

6. CTEs — Deep Dive & Chained Patterns Must Know

SQL — Full analytical pipeline: LTV with MoM growthWITH
monthly_spend AS (
  SELECT customer_id, DATE_TRUNC('month', order_date) AS month, SUM(amount) AS rev
  FROM orders WHERE status = 'Delivered' GROUP BY 1,2
),
with_growth AS (
  SELECT *, ROUND(100.0*(rev - LAG(rev) OVER (PARTITION BY customer_id ORDER BY month))
    / NULLIF(LAG(rev) OVER (PARTITION BY customer_id ORDER BY month), 0), 1) AS mom_pct
  FROM monthly_spend
),
ltv AS (
  SELECT customer_id, SUM(rev) AS total_ltv,
    DENSE_RANK() OVER (ORDER BY SUM(rev) DESC) AS ltv_rank
  FROM monthly_spend GROUP BY 1
)
SELECT c.name, c.city, l.total_ltv, l.ltv_rank, g.month, g.rev, g.mom_pct
FROM with_growth g
JOIN ltv l ON g.customer_id = l.customer_id
JOIN customers c ON g.customer_id = c.customer_id
ORDER BY l.ltv_rank, g.month;

7. Recursive CTEs Pro Level

SQL — Generate every date in Q1 2024WITH RECURSIVE date_series AS (
  SELECT DATE('2024-01-01') AS dt
  UNION ALL
  SELECT dt + INTERVAL 1 DAY FROM date_series WHERE dt < '2024-03-31'
)
SELECT ds.dt, COALESCE(SUM(o.amount),0) AS revenue
FROM date_series ds
LEFT JOIN orders o ON ds.dt = o.order_date AND o.status = 'Delivered'
GROUP BY ds.dt ORDER BY ds.dt;

8. Query Optimisation

❌ SLOW — Correlated Subquery

SELECT customer_id, amount
FROM orders o1
WHERE amount > (
SELECT AVG(amount) FROM orders o2
WHERE o2.customer_id = o1.customer_id
);
-- Runs once per row. 1M rows = 1M executions

✅ FAST — CTE Pre-aggregation

WITH avg_c AS (
  SELECT customer_id, AVG(amount) AS avg_amt
  FROM orders GROUP BY 1
)
SELECT o.customer_id, o.amount
FROM orders o JOIN avg_c a
  ON o.customer_id = a.customer_id
WHERE o.amount > a.avg_amt;
-- CTE runs ONCE. Then fast JOIN

Rule	Why It Matters
Filter early — WHERE before JOIN	Join smaller filtered tables, not full tables
Never SELECT * in production	Columnar storage scans every column — very expensive
Replace correlated subqueries with CTE+JOIN	Subqueries run once per row; CTEs run once total
Use date partition filters	On warehouses, skips entire file partitions
Use EXPLAIN / EXPLAIN ANALYZE	See what the database is actually doing

9. Common SQL Patterns

Deduplication — Keep Only the Latest Record

SQLWITH deduped AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
FROM order_events
)
SELECT * FROM deduped WHERE rn = 1;

Funnel Analysis

SQL — E-commerce funnelSELECT
  COUNT(DISTINCT CASE WHEN event='product_viewed' THEN user_id END) AS viewers,
  COUNT(DISTINCT CASE WHEN event='add_to_cart' THEN user_id END) AS cart_adders,
  COUNT(DISTINCT CASE WHEN event='order_placed' THEN user_id END) AS purchasers
FROM user_events WHERE event_date >= '2024-01-01';

10. Quiz

Question 1 of 4

What's the key difference between RANK() and DENSE_RANK() with ties?

A

RANK() is faster to compute

B

RANK() leaves gaps after ties (1,1,3); DENSE_RANK() doesn't (1,1,2)

C

DENSE_RANK() requires ORDER BY; RANK() doesn't

D

They produce identical results

✅ Correct! RANK() skips the next rank after ties: (1,1,3). DENSE_RANK() continues consecutively: (1,1,2). Use DENSE_RANK() in most analyst contexts.

❌ RANK() leaves gaps (1,1,3). DENSE_RANK() doesn't (1,1,2). Both require ORDER BY in the OVER clause.

Question 2 of 4

You want each order's amount as % of that customer's total lifetime spend, while keeping individual order rows. Which approach?

A

GROUP BY customer_id and divide each amount by SUM(amount)

B

SUM(amount) OVER (PARTITION BY customer_id) to get total, then divide each row's amount

C

A correlated subquery in the SELECT clause

D

This is not possible in SQL

✅ Correct! SUM() OVER (PARTITION BY customer_id) computes the total per customer without collapsing rows. GROUP BY would lose individual order data.

❌ GROUP BY collapses all orders per customer — losing individual rows. Use a window function: SUM(amount) OVER (PARTITION BY customer_id).

Question 3 of 4

Most efficient pattern to deduplicate a table, keeping only the latest record per order_id?

A

GROUP BY order_id and MAX(updated_at)

B

ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC), then WHERE rn = 1

C

SELECT DISTINCT *

D

Delete all rows except those with the latest date

✅ Correct! ROW_NUMBER() PARTITION BY order_id ORDER BY updated_at DESC assigns rank 1 to the latest row per order. WHERE rn = 1 keeps only that row.

❌ GROUP BY with MAX() only returns the date — you lose all other columns. ROW_NUMBER() + WHERE rn = 1 is the standard deduplication pattern.

Question 4 of 4

A correlated subquery on a 1M row table takes 30 minutes. Best fix?

A

Add more RAM to the database server

B

Replace with a CTE that pre-aggregates once, then JOIN

C

Use SELECT * to bring in all columns first

D

Add ORDER BY to speed up the query

✅ Correct! A correlated subquery runs once per row. On 1M rows = 1M executions. A CTE pre-aggregates once, then a JOIN finds the matching value — orders of magnitude faster.

❌ Pre-aggregate in a CTE and JOIN to it. SELECT * makes things worse. ORDER BY doesn't speed up queries. Add RAM treats the symptom, not the cause.

🎯 Module 5 — What You've Learned

Window functions: Calculations across related rows without GROUP BY collapsing them.
Ranking: ROW_NUMBER() for deduplication, DENSE_RANK() for leaderboards, NTILE() for segmentation.
Running totals & moving averages: SUM/AVG OVER with ROWS BETWEEN.
LAG & LEAD: Access previous/next row values — essential for period-over-period analysis.
Chained CTEs: Break complex logic into named readable steps.
Optimisation: Filter early, avoid SELECT *, replace correlated subqueries with CTE+JOIN.
Deduplication: ROW_NUMBER() PARTITION BY + WHERE rn = 1 — the universal pattern.

Up next — Module 6

Data Visualisation — From Charts to Decisions

→

📊 Module 6 of 6

Data Visualisation

The final layer between analysis and action. Data that isn't understood isn't acted on. Learn to turn numbers into narratives that drive decisions.

⏱ ~60 min

🎯 Intermediate

🧩 12 sections

💼 All analyst roles

1. Why Data Visualisation Is a Core Analyst Skill

🧠

The Brain Processes Visuals 60,000× Faster

A table of numbers requires working memory. A well-designed chart delivers the insight instantly. Stakeholders make better decisions faster when data is visual.

🗣️

Viz Is Communication, Not Decoration

A chart's job is to answer one specific question in under 5 seconds. If it can't, it needs to be redesigned — not decorated.

💼

What Analysts Are Evaluated On

Managers rarely see your SQL. They see your dashboards and slides. This determines your reputation as an analyst.

🎯

Interview Reality

Analytics interviews increasingly include a "take-home + present" round. Visualisation skill decides the outcome.

2. Chart Types — The Analyst's Map Must Know

Chart	Best For	Example	Avoid When
📊 Bar / Column	Comparing categories	Revenue by city	More than 15 categories
📈 Line	Trends over time	Monthly DAU, weekly orders	Categorical X-axis
🍕 Pie / Donut	Parts of a whole	Revenue share by category	More than 5 slices
🌡️ Heatmap	Two-dimensional patterns	Hour × day order volume	Too many cells
💧 Waterfall	Cumulative components	Revenue bridge: Jan → Feb	Many small components
🔵 Scatter Plot	Correlation	Marketing spend vs conversions	Many duplicate points

3. Choosing the Right Chart Must Know

📊

Comparison

"Which city has the highest revenue?" → Bar chart. Sort bars by value, not alphabetically.

📈

Trend

"Is revenue growing?" → Line chart. Time on X-axis. Multiple lines for multiple series.

🧩

Composition

"What share does each category contribute?" → Stacked bar or pie (max 5 slices).

🔵

Relationship

"Does spend drive conversions?" → Scatter plot. One variable per axis.

4. Design Principles Must Know

1

The One-Question Rule

Every chart should answer exactly one question. If it needs 3 footnotes to explain, split it into multiple charts.

2

Maximise the Data-Ink Ratio (Tufte)

Remove gridlines, borders, 3D effects, and decorations that don't add information. The chart should be mostly data.

3

Start the Y-axis at Zero for Bar Charts

Bar length encodes value. A truncated Y-axis makes the chart lie. Only line charts can start above zero.

4

Annotate directly — remove legends

Legends force the reader to look back and forth. Label each line directly at its end.

5

Sort bars by value

Alphabetical bars force readers to scan. Sort highest to lowest and the pattern is instantly visible.

5. Colour — The Most Misused Element

Colour Type	When to Use	Example
Categorical	Different categories, no inherent order	Delhi=blue, Mumbai=orange, Bangalore=green
Sequential	Quantitative data from low to high	Revenue heatmap — light to dark blue
Diverging	Data with a meaningful midpoint	Growth — red (negative) → white (0%) → green (positive)

⚠️ The 5 Rules of Colour

1. One colour per metric.
2. Green = good/up, Red = bad/down — universal conventions. Don't reverse.
3. Grey out non-focus data. Highlight only what matters.
4. Max 5–6 colours per chart.
5. Always check colour-blind safety (8% of men have red-green colour blindness).

6. KPI Cards & Scorecards

📌

Always show the delta

"₹42L revenue" is incomplete. "₹42L (↑12% vs last month)" is meaningful. Always include comparison period and direction.

🎯

Include target attainment

"₹42L (84% of ₹50L target)" tells you where you stand. A metric without a target is a number, not a KPI.

🟢

Colour by desirability

Cancellation rate going up is bad — colour it red even though the delta is positive. Colour based on good/bad, not up/down.

📊

Sparkline for context

A small inline trend line next to the KPI value shows stability or volatility without taking much space.

7. Dashboard Design — Layout & Hierarchy

1

KPI cards at the top

4–5 headline numbers. Answers "how are we doing?" in 3 seconds.

2

Trend chart top-left of main area

Humans read left-to-right, top-to-bottom. The most important chart goes top-left — usually the revenue/metric trend over time.

3

Breakdown chart top-right

A bar chart breaking down the trend by category. Answers "where is the growth/problem coming from?"

4

Detail table at the bottom

For drill-in. Collapsible or on a separate tab. Main dashboard should never be dominated by a table.

5

Filters at top or left

Date range, segment, region. Keep consistent across all views. A filter at the top affects everything below.

⚠️ The 3-Second Rule

A stakeholder should answer their primary question within 3 seconds. If they can't, the design is failing. Test this: show the dashboard to someone unfamiliar and ask "what's the key insight?" If they need more than 3 seconds, simplify.

8. Tools Tools

Tool	Best For	Cost	Analyst Relevance
Looker Studio	Free dashboards, BigQuery integration	Free	Start here — free, powerful, easy
Power BI	Enterprise reporting, Microsoft stack	~₹700/mo	Most common in corporates and banks
Tableau	Complex analytics, large datasets	~₹5,000/mo	Common in tech; Tableau Public is free
Excel Charts	Ad-hoc, sharing with non-technical stakeholders	Included	Universal — everyone can open it
Metabase	SQL-first BI, self-hosted	Free (self-hosted)	Common in startups

✅ Which Tool First?

Start with Looker Studio (free, no install). Learn dashboarding concepts that transfer to Power BI and Tableau. Use Tableau Public (free) to build portfolio dashboards. Learn Power BI when your company uses it — easy to pick up once you know Looker Studio.

9. Storytelling with Data Must Know

S

Situation

"Revenue in Q1 2024 was ₹48Cr, up 12% YoY. Growth consistent across all cities."

C

Complication

"However, cancellation rate rose from 8% to 14%, eroding 18% of gross revenue."

Q

Question

"What's driving the cancellation spike, and which segment is most affected?"

A

Answer + Action

"75% of cancellations are in Electronics, within 2 hours of order. Recommendation: add 1-hour cancellation cooldown for Electronics orders above ₹20,000."

📌 Lead with the Recommendation (Pyramid Principle)

Start with "I recommend X because Y" — not 10 slides of data building to a recommendation at the end. If they run out of time, they still have your recommendation. If they want more detail, the supporting data is right there.

10. Common Mistakes — And How to Fix Them

Mistake	Why It's Wrong	Fix
Y-axis not starting at 0 on bar chart	Makes small differences look huge	Always start at 0 for bar/column charts
3D charts	Perspective distorts visual ratios	Use 2D always
Pie chart with too many slices	Eye can't distinguish slices <5%	Max 5 slices; merge rest into "Other"
Too many colours	Everything competes for attention	Max 5–6 colours; grey out non-focus
Chart title = chart type	"Bar Chart of Revenue" says nothing	Title = insight: "Delhi Revenue Leads All Cities in Q1"
Dashboard overload	12 KPIs + 8 charts = cognitive overwhelm	4–5 KPIs + 2 primary charts; details on drill-through
Correlation = causation	Ice cream sales and sunscreen both rise in summer — ice cream doesn't cause sunscreen purchases	Always say "correlated with" not "causes"

11. Real-World Scenarios

📊 Business Analyst — Monthly Business Review Deck (5 slides, 8 minutes)

Slide 1: 4 KPI cards (Revenue, Orders, AOV, Cancellation Rate) with MoM delta and RAG status.
Slide 2: Bar chart of daily revenue with 7-day moving average. Annotated with key events.
Slide 3: Top 3 cities and products with growth numbers. One chart, three callouts.
Slide 4: Cancellation rate by category heatmap. March spike highlighted. Cause in subtitle.
Slide 5: 3 recommendations. Each: Observation → Impact → Action. No charts — just words.
This is the standard MBR format at companies like Swiggy, Meesho, and Urban Company.

📱 Product Analyst — Feature Launch Impact Dashboard

Panel 1: Conversion rate before vs after. Two big numbers with deltas.
Panel 2: Funnel chart — each checkout step. Drop-off %, before vs after side by side.
Panel 3: Line chart of conversion rate by day, with vertical reference line on launch date.
Panel 4: Segment breakdown — Mobile vs Desktop? Android vs iOS? New vs Returning?
This is the standard A/B test / feature launch dashboard — every product team wants this after every launch.

12. Quiz

Question 1 of 4

You want to show how 3 product categories performed in revenue growth over 12 months. Which chart?

A

Three separate pie charts, one per category

B

A multi-line chart with one line per category, time on the X-axis

C

A 3D bar chart

D

A scatter plot with months on X and revenue on Y

✅ Correct! A multi-line chart is ideal for trends over time across multiple categories. 3D charts always distort data.

❌ For trends over time with multiple series, use a multi-line chart. Time on X-axis, one line per category.

Question 2 of 4

Your bar chart's Y-axis starts at ₹80,000. Delhi (₹95,000) looks 3× larger than Mumbai (₹88,000). What do you do?

A

Keep it — truncation makes differences more visible

B

Start the Y-axis at ₹0

C

Switch to a 3D chart

D

Add a note saying "axis is truncated"

✅ Correct! Bar length encodes value. A truncated Y-axis makes bars lie about proportions. Always start at zero for bar charts.

❌ Start at ₹0. Bar length encodes value — truncation misrepresents the data. A note doesn't fix a misleading chart.

Question 3 of 4

You're presenting to a VP with 8 minutes, 45 data points, 6 charts, and 3 recommendations. How do you structure it?

A

All 6 charts first, then data, then recommendations

B

Context → all data → analysis → recommendations

C

Recommendations first → 3 key supporting charts → backup data if asked

D

Alphabetical order of slides

✅ Correct! Pyramid Principle — lead with the conclusion. If they run out of time, they still have your recommendation.

❌ Lead with recommendations (Pyramid Principle). Senior stakeholders want the conclusion first, then evidence.

Question 4 of 4

Ice cream sales and sunscreen sales both rise in summer. Your colleague says "ice cream causes sunscreen purchases." What's wrong?

A

Nothing — if two things correlate, one causes the other

B

Correlation does not imply causation — both are driven by a third variable (hot weather)

C

The chart type is wrong

D

The Y-axis should start at 0

✅ Correct! Correlation ≠ causation. Both are driven by hot weather — a confounding variable. Never claim causation without proper causal analysis.

❌ Correlation ≠ causation. Both are driven by hot weather. Never claim one causes the other without controlled experiments or regression analysis.

🎯 Module 6 — What You've Learned

Viz is communication: Data that isn't understood isn't acted on.
One-Question Rule: Every chart answers one question in under 5 seconds. If not — redesign.
Chart choice: Comparison → bar, Trend → line, Composition → stacked bar/pie, Relationship → scatter.
Design principles: Maximise data-ink ratio. Zero Y-axis for bars. Annotate directly. Sort bars by value.
Colour: Green = good, Red = bad. Max 6 colours. Always colour-blind safe.
Dashboard hierarchy: KPIs → Trend → Breakdown → Details. 3-second rule.
Storytelling: Situation → Complication → Question → Answer. Lead with the recommendation.
Correlation ≠ causation: The most important distinction in analytics.

🎓

You've completed all 6 core modules.

You've built the foundation every analyst needs — from how data is stored, to querying it, to presenting insights that drive decisions. Now choose your specialisation.

✅ Module 1: Database & DBMS ✅ Module 2: Basics of Excel ✅ Module 3: Basics of SQL ✅ Module 4: Advanced Excel ✅ Module 5: Advanced SQL ✅ Module 6: Data Visualisation

Sign in to access modules

The analytics career path nobody gave you. We did.

Companies don't define your analytics path. You do.

6 modules. Every interview will test these.

Then choose your analytics domain.

Module complete.

Complete all 6 modules first

You're ready. Request your mock.

Sign in with your number

Enter the 4-digit code

Create your free account

Enter the code

Welcome.

Database & DBMS

1. What is Data?

2. What is a Database?

3. What is a DBMS? Must Know

4. Types of Databases

5. The Relational Model — Tables, Rows & Columns

customers table

orders table

6. Keys & Constraints

7. Normalization

8. ACID Properties

9. Real-World Scenarios

10. Quick Recap Quiz

🎯 Module 1 — What You've Learned

Basics of Excel

1. Why Excel for Analysts?

2. Navigating the Excel Interface

3. Essential Formulas Every Analyst Must Know Must Know

4. Logical Functions — IF, AND, OR, IFS

5. Lookup Functions — VLOOKUP, INDEX-MATCH, XLOOKUP Must Know

6. Text & Date Functions

7. Pivot Tables — The Analyst's Superpower Must Know

8. Charts — Making Data Visual

9. Data Cleaning — The Unglamorous but Critical Skill

10. Power Shortcuts — Work 3× Faster

11. Real-World Scenarios

12. Quiz

🎯 Module 2 — What You've Learned

Basics of SQL

1. What is SQL — and Why Does Every Analyst Need It?

2. Your Practice Schema

3. SELECT & FROM — The Foundation Must Know

4. WHERE — Filtering Rows Must Know

5. ORDER BY & LIMIT

6. Aggregate Functions — Summarising Data Must Know

7. GROUP BY & HAVING Must Know

8. JOINs — Combining Tables Must Know

9. Subqueries & CTEs Pro Level

10. Useful SQL Functions

11. SQL Execution Order

12. Real-World SQL Scenarios

13. Quiz

🎯 Module 3 — What You've Learned

Advanced Excel

1. What "Advanced" Actually Means

2. Named Ranges & Excel Tables

3. Power Query — Your Data Pipeline Engine Must Know

4. Dynamic Array Functions Excel 365

5. Advanced Pivot Techniques

6. Data Validation & Form Controls

7. Dashboard Architecture

8. Building the Dashboard — Step by Step

9. What-If Analysis

10. Real-World Scenarios

🎯 Module 4 — What You've Learned

Advanced SQL

1. Why Advanced SQL Matters

2. Window Functions — The Anatomy Must Know

3. Ranking Functions Must Know

4. Aggregate Window Functions Must Know

5. LAG & LEAD — Period-Over-Period Must Know

6. CTEs — Deep Dive & Chained Patterns Must Know

7. Recursive CTEs Pro Level

8. Query Optimisation

9. Common SQL Patterns

Deduplication — Keep Only the Latest Record

Funnel Analysis