DATA ANALYTICS

Complete Learning Path

From Zero Knowledge to 7 LPA+

A Hyperskill-style progressive guide — every function, every syntax, every example,

practice questions and hands-on projects.

7 Modules 60+ Topics 200+ Questions 25+ Projects 6 Months

SQL • Python • Excel • Power BI • Statistics • EDA

Freshers | Zero prior knowledge required | Industry-ready curriculum

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 1

 Table of Contents

# Module Key Topics Job Weight

1 Microsoft Excel Formulas, SUMIF, VLOOKUP, Pivot Tables, DashboardsHigh

2 SQL SELECT, JOINs, GROUP BY, Window Functions, CTEsCritical

3 Python Pandas, NumPy, Matplotlib, Seaborn, EDA Very High

4 Statistics Descriptive Stats, Distributions, Hypothesis Testing High

5 Power BI Power Query, DAX, Dashboards, Data Models Very High

6 Data Cleaning & EDA Missing Values, Outliers, EDA Framework High

7 Projects & Interview Prep 3 Capstones, 6-Month Roadmap, Interview Q&A Critical

Note
How to use this guide: Each topic follows the pattern — Concept → Syntax → Example → Output →
Questions.
Complete all questions before moving to the next topic. Build projects as you finish each module.
Recommended time: 1-2 hours per topic. Do not rush — depth beats speed.

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 2

 MODULE 1

Microsoft Excel for Data Analytics

The universal starting point — used in every analyst role, every company, every day.

1.1 SUMIF — Conditional Sum

SUMIF adds up values in a range only when a condition is met. It is one of the most used functions in
business reporting.

Syntax — SUMIF
=SUMIF(range, criteria, sum_range)
range — the column to check the condition against
criteria — the condition (value, text, or expression)
sum_range — the column to add up (can be same as range)

Example — Total sales for Region = 'West'

A B
Region Sales
East 12000
West 18000
West 22000
North 9000
=SUMIF(A2:A5, "West", B2:B5)
>> Result: 40000 (18000 + 22000)

Example — Sum sales greater than 15000

=SUMIF(B2:B5, ">15000", B2:B5)
>> Result: 40000 (18000 + 22000)

Note
Use wildcard * in criteria: =SUMIF(A2:A10, "*West*", B2:B10) matches NorthWest, Southwest, West etc.
For dates: =SUMIF(C2:C10, ">"&DATE;(2024,1,1), B2:B10) sums sales after Jan 1 2024.

Practice Questions
1. Write a SUMIF to find total revenue from Product Category = 'Electronics'.
Hint: =SUMIF(CategoryColumn, "Electronics", RevenueColumn)

2. Sales data has a Discount column (Yes/No). Sum all sales where Discount = 'Yes'.
Hint: =SUMIF(DiscountCol, "Yes", SalesCol)

3. Sum all values in column B that are greater than the average of column B.
Hint: =SUMIF(B2:B100, ">"&AVERAGE;(B2:B100), B2:B100)

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 3

 1.2 SUMIFS — Multiple Condition Sum

SUMIFS extends SUMIF to support multiple conditions simultaneously. ALL conditions must be true for a row
to be included.

Syntax — SUMIFS
=SUMIFS(sum_range, range1, criteria1, range2, criteria2, ...)
sum_range — column to add
range1 — first column to check
criteria1 — condition for range1
range2 — second column to check (optional, can add more pairs)

Example — Total sales in West region AND in March

=SUMIFS(C2:C100, A2:A100, "West", B2:B100, "March")
>> Adds C only where A=West AND B=March

Practice Questions
1. Find total salary paid to employees in Department='IT' and Level='Senior'.
Hint: =SUMIFS(SalaryCol, DeptCol, "IT", LevelCol, "Senior")

2. Sum orders where Region='South' AND Status='Delivered' AND Amount > 5000.
Hint: =SUMIFS(AmountCol, RegionCol,"South", StatusCol,"Delivered", AmountCol,">5000")

1.3 COUNTIF & COUNTIFS — Conditional Count

Count how many cells meet one or more conditions.

Syntax — COUNTIF / COUNTIFS

=COUNTIF(range, criteria)
=COUNTIFS(range1, criteria1, range2, criteria2, ...)

Example — Count orders from City = 'Mumbai'

=COUNTIF(A2:A100, "Mumbai")
>> Result: 34
Count orders from Mumbai AND Status = 'Pending'
=COUNTIFS(A2:A100, "Mumbai", B2:B100, "Pending")
>> Result: 7

Common Mistake
COUNTIF counts cells, not values. =COUNTIF(A1:A10, ">0") counts cells with positive numbers.
Do not confuse COUNTIF (count matching) with SUMIF (add matching).

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 4

 Practice Questions
1. How many employees have salary above 60000?
Hint: =COUNTIF(SalaryCol, ">60000")

2. Count rows where Department='HR' and Gender='Female'.

Hint: =COUNTIFS(DeptCol,"HR", GenderCol,"Female")

3. Count cells in column A that contain the word 'urgent' anywhere.

Hint: =COUNTIF(A2:A100, "*urgent*")

1.4 IF, IFS & Nested IF — Conditional Logic

IF returns one value when a condition is true, another when false. Nest IFs for multiple conditions. IFS is the
cleaner modern alternative.

Syntax — IF / IFS
=IF(logical_test, value_if_true, value_if_false)
=IFS(condition1, value1, condition2, value2, ..., TRUE, default)

Example — Grade students based on marks

-- Nested IF (older approach)
=IF(A2>=90,"A",IF(A2>=75,"B",IF(A2>=60,"C","Fail")))
-- IFS (cleaner, recommended)
=IFS(A2>=90,"A", A2>=75,"B", A2>=60,"C", TRUE,"Fail")
>> If A2 = 82, result: "B"

Example — Flag high-value orders

=IF(B2>50000, "High Value", "Regular")
>> If B2 = 75000, result: "High Value"

Common Mistake
Limit nested IFs to 3 levels max — beyond that, use IFS or SWITCH for readability.
Always wrap text values in double quotes. IF(A2="Yes") not IF(A2=Yes).

Practice Questions
1. An employee gets 'Bonus' if sales > 100000, else 'No Bonus'. Write the formula.
Hint: =IF(SalesCell>100000, "Bonus", "No Bonus")

2. Categorise age: <18='Minor', 18-60='Adult', >60='Senior'.

Hint: =IFS(A2<18,"Minor", A2<=60,"Adult", TRUE,"Senior")

3. Write a formula that returns 'Pass' only if marks >= 40 AND attendance >= 75.
Hint: =IF(AND(MarksCell>=40, AttendCell>=75), "Pass", "Fail")

1.5 VLOOKUP — Vertical Lookup

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 5

 VLOOKUP searches for a value in the first column of a range and returns a value from a specified column in
the same row. It is one of the most tested Excel functions in interviews.

Syntax — VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value — what you are searching for
table_array — the table/range to search in (lookup col MUST be first)
col_index_num — which column number to return (1=first col, 2=second...)
range_lookup — FALSE for exact match (use this always), TRUE for approximate

Example — Find employee department by Employee ID

A (EmpID) B (Name) C (Department)
101 Riya Marketing
102 Arjun Engineering
103 Priya HR
=VLOOKUP(102, A2:C4, 3, FALSE)
>> Result: "Engineering"
=VLOOKUP(101, A2:C4, 2, FALSE)
>> Result: "Riya"

Common Mistake
VLOOKUP only looks RIGHT — it cannot return a column to the LEFT of the lookup column. Use
INDEX-MATCH for that.
Always use FALSE (exact match) unless you specifically need approximate match.
If the lookup value is not found, VLOOKUP returns #N/A. Wrap in IFERROR:
=IFERROR(VLOOKUP(...), "Not Found")

Practice Questions
1. You have a product price list (columns: ProductID, Name, Price). Write VLOOKUP to get price for
ProductID 'P105'.
Hint: =VLOOKUP("P105", PriceTable, 3, FALSE)

2. A student table has RollNo, Name, Marks, Grade. Find the grade for Roll No 55.
Hint: =VLOOKUP(55, StudentTable, 4, FALSE)

3. The VLOOKUP returns #N/A for some IDs. How do you show 'Not Found' instead?
Hint: =IFERROR(VLOOKUP(A2, Table, 2, FALSE), "Not Found")

1.6 INDEX-MATCH — Flexible Lookup

INDEX-MATCH is the professional alternative to VLOOKUP. It can look in any direction, is faster on large
datasets, and doesn't break when columns are inserted.

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 6

 Syntax — INDEX-MATCH
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
INDEX(range, row_num) — returns the value at position row_num in range
MATCH(value, range, 0) — returns the position of value in range (0=exact)
Combined: MATCH finds the row number, INDEX uses it to fetch the value

Example — Look up salary by employee name (name is NOT in first column)

A (Dept) B (Name) C (Salary)
IT Riya 75000
HR Arjun 55000
IT Priya 80000
-- Find salary for 'Arjun' (name is column B, NOT column A)
=INDEX(C2:C4, MATCH("Arjun", B2:B4, 0))
>> Result: 55000
-- VLOOKUP would fail here because Name is not the first column

Note
INDEX-MATCH can look LEFT: find Dept for a given Name — =INDEX(A2:A4, MATCH("Riya", B2:B4,
0)) returns 'IT'.
In Excel 365, XLOOKUP replaces both VLOOKUP and INDEX-MATCH with simpler syntax.

Practice Questions
1. Table: City | StoreID | Revenue. Find Revenue for City='Chennai'. Name is NOT in first column.
Hint: =INDEX(RevenueCol, MATCH("Chennai", CityCol, 0))

2. Why is INDEX-MATCH preferred over VLOOKUP in large datasets?

Hint: INDEX-MATCH does not scan the entire table from left — it is faster and more flexible.

3. Use INDEX-MATCH to do a two-way lookup: find the value at row matching Name='Riya' and column
matching Month='March'.
Hint: =INDEX(DataRange, MATCH("Riya",NameCol,0), MATCH("March",MonthRow,0))

1.7 Text Functions — Clean & Extract Text

Real data has messy text — extra spaces, mixed case, combined fields. These functions clean and extract
what you need.

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 7

 Syntax — Key Text Functions
=TRIM(text) — removes extra spaces
=UPPER(text) — converts to UPPERCASE
=LOWER(text) — converts to lowercase
=PROPER(text) — Converts To Title Case
=LEN(text) — counts characters
=LEFT(text, n) — extracts n characters from left
=RIGHT(text, n) — extracts n characters from right
=MID(text, start, n) — extracts n characters from position start
=FIND(find_text, within) — finds position of text (case-sensitive)
=SUBSTITUTE(text, old, new) — replaces old text with new
=CONCATENATE(t1,t2,...) — joins text (or use & operator)
=TEXT(value, format) — formats number as text

Example — Real-world text cleaning scenarios

A2 = " john doe " (messy with spaces)
=TRIM(A2) >> "john doe"
=PROPER(TRIM(A2)) >> "John Doe"
A2 = "CUST-2024-001" (extract year)
=MID(A2, 6, 4) >> "2024"
A2 = "9876543210" (phone, add prefix)
="+91-"&A2; >> "+91-9876543210"
A2 = "[Link]@[Link]" (extract name)
=LEFT(A2, FIND("@",A2)-1) >> "[Link]"

Practice Questions
1. Column A has employee full names (First Last). Extract only first name.
Hint: =LEFT(A2, FIND(" ",A2)-1)

2. A product code 'PRD-ELEC-00123' has the category in positions 5-8. Extract it.
Hint: =MID(A2,5,4) returns 'ELEC'

3. How do you count the number of words in a cell? (Hint: count spaces + 1)
Hint: =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1

1.8 Date Functions — Work with Dates

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 8

 Syntax — Key Date Functions
=TODAY() — returns today's date
=NOW() — returns current date and time
=YEAR(date) — extracts year
=MONTH(date) — extracts month number (1-12)
=DAY(date) — extracts day number
=DATE(year,month,day)— creates a date from parts
=DATEDIF(start,end,"D") — days between dates
=DATEDIF(start,end,"M") — months between dates
=DATEDIF(start,end,"Y") — years between dates
=EOMONTH(date,0) — last day of the month
=WEEKDAY(date) — day of week (1=Sun, 7=Sat)
=TEXT(date,"mmm-yyyy") — format date as 'Jan-2024'

Example — Calculate employee tenure

A2 = 15/03/2021 (joining date)
Years of service:
=DATEDIF(A2, TODAY(), "Y") >> 3
Complete months:
=DATEDIF(A2, TODAY(), "M") >> 37
Age from DOB:
=DATEDIF(DOBCell, TODAY(), "Y") >> age in years
Extract month name from order date:
=TEXT(OrderDate, "mmmm") >> "March"

Practice Questions
1. Calculate how many days are left until 31-Dec-2025.
Hint: =DATE(2025,12,31)-TODAY()

2. An employee joined on 05/06/2019. How many complete years have they worked?
Hint: =DATEDIF("05/06/2019", TODAY(), "Y")

3. Group orders by quarter using month number. Q1=months 1-3, Q2=4-6, etc.
Hint: =IF(MONTH(A2)<=3,"Q1",IF(MONTH(A2)<=6,"Q2",IF(MONTH(A2)<=9,"Q3","Q4")))

1.9 Pivot Tables — Summarise Thousands of Rows Instantly

Pivot Tables are the most powerful Excel feature for data analysts. They let you summarise, group, and
analyse large datasets with drag-and-drop — no formulas needed.

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 9

 Syntax — How to Create a Pivot Table
1. Click anywhere in your data
2. Insert tab → PivotTable → OK
3. Drag fields to four areas:
Rows — categories to group by (e.g., Region, Product)
Columns — sub-categories (e.g., Month, Year)
Values — numbers to summarise (e.g., Sum of Sales, Count of Orders)
Filters — slice the whole table (e.g., show only 2024 data)

Example — Sales by Region and Product

Source data: 10,000 rows of orders (Date, Region, Product, Qty, Revenue)
Drag Region -> Rows
Drag Product -> Columns
Drag Revenue -> Values (Sum of Revenue)
Result instantly shows:
Electronics Clothing Furniture
East 120,000 45,000 78,000
West 95,000 62,000 34,000
North 140,000 28,000 55,000

Note
Right-click any value in Values area → Value Field Settings → change from Sum to Average, Count,
Max, Min, etc.
Add slicers: PivotTable Analyze tab → Insert Slicer → click a slicer button to filter instantly.
Group dates: right-click a date in Rows → Group → select Year/Quarter/Month for time analysis.

Practice Questions
1. You have 50,000 rows of sales data with columns: Date, Salesperson, Region, Product, Revenue.
Build a pivot to find top 5 salespersons by total revenue.
Hint: Drag Salesperson to Rows, Revenue to Values (Sum). Sort by Sum of Revenue descending.

2. How do you show percentage of total instead of raw numbers in a Pivot Table?
Hint: Value Field Settings → Show Values As → % of Grand Total

3. What is a Calculated Field in Pivot Tables? Create one for Profit Margin = Revenue - Cost.
Hint: PivotTable Analyze → Fields, Items & Sets → Calculated Field → enter formula

1.10 Charts & Dashboards — Visualise Your Analysis

Choosing the right chart type is as important as building it correctly. Wrong chart type can mislead the
audience.

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 10

 Syntax — Chart Type Selection Guide
Bar / Column chart — Compare categories (e.g., sales by region)
Line chart — Show trends over time (e.g., monthly revenue)
Pie / Donut chart — Show part-to-whole (use only for 2-5 categories)
Scatter plot — Show relationship between two variables
Histogram — Show distribution of a single variable
Combo chart — Two metrics on same chart (e.g., Revenue + Growth%)
Rule: If comparing categories → Bar. If showing time trend → Line.
Never use 3D charts — they distort data perception.

Example — Build an interactive dashboard

Step 1: Create 3 Pivot Tables (Sales by Region, by Product, by Month)
Step 2: Create 3 Pivot Charts (Column, Line, Donut)
Step 3: Insert → Slicer → Year, Region (shared slicers)
Step 4: Add KPI cells: Total Revenue, Total Orders, Avg Order Value
Step 5: Format: remove gridlines, use consistent colours, add title
Clicking a slicer now filters ALL charts and KPIs simultaneously.

Practice Questions
1. You want to show how revenue changed month-over-month across 2 years. Which chart type? Why?
Hint: Line chart — it shows trend and continuity over time clearly.

2. A CEO wants to see total sales by region, top 5 products, and monthly trend all on one screen. How
do you structure this dashboard?
Hint: 3-panel layout: KPI cards at top, region bar chart left, product chart right, trend line at bottom.

Tools & Resources

Microsoft Excel 365 / 2019 — main tool (free via college Microsoft subscription)
Google Sheets — free cloud alternative, most formulas work the same
Practice data: [Link] on Kaggle (search 'superstore sales dataset')
YouTube: Leila Gharani Excel — best free Excel tutorials
[Link] — quick function reference with examples for every formula

Project — Excel Capstone — Sales Performance Dashboard

Dataset: Download Superstore Sales from Kaggle (9,994 rows)
Task 1: Clean data — check for blanks, fix date formats, remove duplicates
Task 2: Create SUMIFS formulas to calculate revenue by Region+Category
Task 3: Add a column 'Profit Margin %' using formula
Task 4: Build 3 Pivot Tables — by Region, by Product Sub-Category, by Month
Task 5: Create an interactive dashboard with slicers for Year and Region
Task 6: Add KPI cards — Total Revenue, Total Orders, Top Region, Avg Discount
Deliverable: Single Excel file with separate sheets for Data, Analysis, Dashboard

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 11

 MODULE 2

SQL — Structured Query Language

The #1 most tested skill in data analyst interviews. Every company uses SQL every day.

2.1 SELECT — Retrieve Data from a Table

SELECT is the foundation of SQL. Every query starts with SELECT. It retrieves rows and columns from a
database table.

Syntax — SELECT
SELECT column1, column2 FROM table_name;
SELECT * FROM table_name; -- * means all columns
SELECT DISTINCT city FROM customers; -- unique values only

Example — Select specific columns from employees table

Table: employees
| emp_id | name | dept | salary | city |
| 1 | Riya | Engineering | 75000 | Pune |
| 2 | Arjun | Marketing | 55000 | Mumbai |
| 3 | Priya | Engineering | 80000 | Pune |
| 4 | Karan | HR | 45000 | Delhi |
SELECT name, dept, salary FROM employees;
>> Returns 3 columns for all 4 rows
SELECT DISTINCT dept FROM employees;
>> Engineering
>> Marketing
>> HR

Practice Questions
1. Write a query to select only the name and city from a customers table.
Hint: SELECT name, city FROM customers;

2. How do you get all unique product categories from a products table?
Hint: SELECT DISTINCT category FROM products;

3. What is the difference between SELECT * and SELECT col1, col2?

Hint: SELECT * returns all columns (avoid in production — slow, brittle). Explicit columns are better practice.

2.2 WHERE — Filter Rows with Conditions

WHERE filters rows based on conditions. Only rows where the condition is TRUE are returned.

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 12

 Syntax — WHERE with all operators
SELECT * FROM table WHERE condition;
Comparison: = != > < >= <=
Range: BETWEEN 25 AND 35
List: IN ('Mumbai', 'Pune', 'Delhi')
Pattern: LIKE 'A%' -- starts with A
LIKE '%Ltd' -- ends with Ltd
LIKE '%Corp%'-- contains Corp
Null check: IS NULL / IS NOT NULL
Combine: AND / OR / NOT

Example — Multiple WHERE condition examples

-- Employees in Engineering with salary > 70000
SELECT name, salary FROM employees
WHERE dept = 'Engineering' AND salary > 70000;
>> Riya (75000), Priya (80000)
-- Employees in Mumbai OR Delhi
SELECT name, city FROM employees
WHERE city IN ('Mumbai', 'Delhi');
>> Arjun (Mumbai), Karan (Delhi)
-- Salary between 50000 and 80000
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;
-- Names starting with 'R'
SELECT * FROM employees WHERE name LIKE 'R%';
>> Riya

Common Mistake
NULL comparisons: use IS NULL, never = NULL. The query WHERE manager_id = NULL returns
nothing.
String values must be in single quotes: WHERE city = 'Pune' not WHERE city = Pune.

Practice Questions
1. Find all products with price less than 500 and stock quantity greater than 100.
Hint: SELECT * FROM products WHERE price < 500 AND stock > 100;

2. Get all customers whose email is NULL (not provided).

Hint: SELECT * FROM customers WHERE email IS NULL;

3. Find orders placed between Jan 1 2024 and Mar 31 2024.

Hint: SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

4. Find all employees whose name contains 'kumar' (case: any).

Hint: SELECT * FROM employees WHERE LOWER(name) LIKE '%kumar%';

2.3 ORDER BY & LIMIT — Sort and Restrict Results

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 13

 Syntax — ORDER BY / LIMIT
SELECT * FROM table ORDER BY column ASC; -- ascending (default)
SELECT * FROM table ORDER BY column DESC; -- descending
SELECT * FROM table ORDER BY col1 ASC, col2 DESC; -- multi-column sort
LIMIT n; -- MySQL/PostgreSQL: return first n rows
FETCH FIRST n ROWS ONLY; -- SQL Server / Oracle
TOP n -- SQL Server: SELECT TOP 10 * FROM table

Example — Top 3 highest paid employees

SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 3;
>> Priya 80000
>> Riya 75000
>> Arjun 55000
-- Order by department (A-Z) then salary (highest first)
SELECT name, dept, salary FROM employees
ORDER BY dept ASC, salary DESC;

Practice Questions
1. Find the 5 most recent orders from an orders table (has order_date column).
Hint: SELECT * FROM orders ORDER BY order_date DESC LIMIT 5;

2. Get the cheapest product in each category. (Hint: for now, just get overall cheapest.)
Hint: SELECT * FROM products ORDER BY price ASC LIMIT 1;

3. Write a query to get the 2nd highest salary. (Classic interview question!)
Hint: SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

2.4 Aggregate Functions — COUNT, SUM, AVG, MIN, MAX

Aggregate functions perform calculations on a set of rows and return a single value. They are the backbone
of analytical queries.

Syntax — Aggregate Functions

COUNT(*) — count all rows
COUNT(column) — count non-NULL values in column
COUNT(DISTINCT col) — count unique non-NULL values
SUM(column) — sum of all values
AVG(column) — arithmetic mean
MIN(column) — smallest value
MAX(column) — largest value
ROUND(AVG(col), 2) — round result to 2 decimal places

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 14

 Example — Salary statistics across all employees
SELECT
COUNT(*) AS total_employees,
COUNT(DISTINCT dept) AS num_departments,
SUM(salary) AS total_salary_bill,
ROUND(AVG(salary),0) AS avg_salary,
MIN(salary) AS lowest_salary,
MAX(salary) AS highest_salary
FROM employees;
>> total_employees: 4
>> num_departments: 3
>> total_salary_bill: 255000
>> avg_salary: 63750
>> lowest_salary: 45000
>> highest_salary: 80000

Practice Questions
1. Find total revenue, number of orders, and average order value from an orders table.
Hint: SELECT SUM(amount), COUNT(*), ROUND(AVG(amount),2) FROM orders;

2. How many distinct cities do our customers come from?

Hint: SELECT COUNT(DISTINCT city) FROM customers;

3. What is the difference between COUNT(*) and COUNT(column)?

Hint: COUNT(*) counts all rows including NULLs. COUNT(col) skips NULL values in that column.

2.5 GROUP BY & HAVING — Aggregate by Category

GROUP BY groups rows with the same value in a column and applies aggregate functions to each group.
HAVING filters groups (like WHERE but applied after grouping).

Syntax — GROUP BY / HAVING

SELECT col, AGG_FUNC(col2)
FROM table
WHERE condition -- filters rows BEFORE grouping
GROUP BY col
HAVING AGG_FUNC(col2) condition -- filters groups AFTER aggregation
ORDER BY AGG_FUNC(col2) DESC;

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 15

 Example — Headcount and average salary by department
SELECT
dept,
COUNT(*) AS headcount,
ROUND(AVG(salary),0) AS avg_salary,
MAX(salary) AS top_salary
FROM employees
GROUP BY dept
ORDER BY avg_salary DESC;
>> Engineering 2 77500 80000
>> Marketing 1 55000 55000
>> HR 1 45000 45000
-- Find departments with avg salary > 60000
SELECT dept, ROUND(AVG(salary),0) avg_sal
FROM employees
GROUP BY dept
HAVING AVG(salary) > 60000;
>> Engineering 77500

Common Mistake
Every column in SELECT that is NOT inside an aggregate function MUST appear in GROUP BY.
WHERE filters rows before grouping. HAVING filters after. Use WHERE for individual row conditions,
HAVING for group conditions.

Practice Questions
1. Find the total sales amount and order count for each city, only show cities with more than 50 orders.
Hint: SELECT city, SUM(amount), COUNT(*) FROM orders GROUP BY city HAVING COUNT(*) > 50;

2. Find which product category generates the highest average revenue per order.
Hint: SELECT category, AVG(revenue) FROM orders GROUP BY category ORDER BY AVG(revenue) DESC
LIMIT 1;

3. Find months where total sales exceeded 500000.

Hint: SELECT MONTH(order_date), SUM(amount) FROM orders GROUP BY MONTH(order_date) HAVING
SUM(amount)>500000;

4. What is the difference between WHERE and HAVING? Give a concrete example.
Hint: WHERE: filters individual rows before grouping. HAVING: filters aggregated groups after GROUP BY.

2.6 JOINs — Combine Data from Multiple Tables

JOINs are the most important SQL concept for analysts. Real databases store data across many tables.
JOINs combine them based on a matching column (key).

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 16

 Syntax — Four Types of JOIN
INNER JOIN — rows where match exists in BOTH tables
LEFT JOIN — ALL rows from left + matched rows from right (NULL if no match)
RIGHT JOIN — ALL rows from right + matched rows from left
FULL OUTER JOIN — ALL rows from both, NULLs where no match
SELECT [Link], [Link]
FROM table1 t1
JOIN table2 t2 ON [Link] = [Link];

Example — INNER JOIN — employees with their department names

Table: employees Table: departments
emp_id | name | dept_id dept_id | dept_name
1 | Riya | 10 10 | Engineering
2 | Arjun| 20 20 | Marketing
3 | Priya| 10 30 | Finance
4 | Karan| NULL
INNER JOIN — only employees WITH a matching dept:
SELECT [Link], d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
>> Riya Engineering
>> Arjun Marketing
>> Priya Engineering
>> (Karan excluded — dept_id is NULL, no match)

Example — LEFT JOIN — all employees, even without a department

SELECT [Link], d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
>> Riya Engineering
>> Arjun Marketing
>> Priya Engineering
>> Karan NULL (included, but no dept)

Example — Finding unmatched rows — 'anti-join' pattern

-- Products that have NEVER been ordered
SELECT p.product_name
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL;
-- Logic: LEFT JOIN brings all products.
-- Those never ordered will have NULL in oi columns.
-- WHERE IS NULL filters to only those.

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 17

 Example — Self Join — find employee and their manager's name
Table: employees
emp_id | name | manager_id
1 | Riya | 3
2 | Arjun | 3
3 | Priya | NULL (she is the manager)
SELECT [Link] AS employee, [Link] AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
>> Riya Priya
>> Arjun Priya
>> Priya NULL (no manager — she is the top)

Common Mistake
Always use table aliases (e, d, o) when joining — makes code readable and avoids ambiguous column
errors.
Joining without ON condition creates a CROSS JOIN (cartesian product) — every row from table1
combined with every row from table2. Almost never what you want.

Practice Questions
1. Tables: customers(cust_id, name) and orders(order_id, cust_id, amount). Get all customer names
with their total order amount.
Hint: SELECT [Link], SUM([Link]) FROM customers c LEFT JOIN orders o ON c.cust_id=o.cust_id GROUP
BY c.cust_id, [Link];

2. Find customers who have NEVER placed an order.

Hint: SELECT [Link] FROM customers c LEFT JOIN orders o ON c.cust_id=o.cust_id WHERE o.order_id IS
NULL;

3. What is a self join? Give a real-world use case.

Hint: Joining a table to itself. Use case: find employees and their manager's name from the same employees
table.

4. Write a 3-table join: orders JOIN customers JOIN products to get: customer name, product name,
order amount.
Hint: SELECT [Link], [Link], [Link] FROM orders o JOIN customers c ON o.cust_id=[Link] JOIN products p
ON o.prod_id=[Link];

2.7 Subqueries — Queries Inside Queries

A subquery is a SELECT statement nested inside another query. It runs first, and its result is used by the
outer query.

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 18

 Syntax — Subquery Types
-- Scalar subquery (returns single value)
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
-- Subquery in FROM clause (derived table)
SELECT dept, avg_sal FROM
(SELECT dept, AVG(salary) avg_sal FROM employees GROUP BY dept) AS dept_stats
WHERE avg_sal > 60000;
-- Subquery with IN
SELECT * FROM orders
WHERE cust_id IN (SELECT cust_id FROM customers WHERE city = 'Mumbai');
-- EXISTS — check if related rows exist
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cust_id = c.cust_id);

Example — Find employees earning above company average

SELECT name, salary,
ROUND(salary - (SELECT AVG(salary) FROM employees), 0) AS above_avg
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
>> Riya 75000 11250
>> Priya 80000 16250

Practice Questions
1. Find the product with the highest price using a subquery.
Hint: SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);

2. Get all orders where the amount is above the average order amount.
Hint: SELECT * FROM orders WHERE amount > (SELECT AVG(amount) FROM orders);

3. Find customers who have placed at least one order (use EXISTS).
Hint: SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cust_id=c.cust_id);

2.8 CTEs — Common Table Expressions

A CTE (WITH clause) creates a named temporary result set that you can reference like a table. CTEs make
complex queries readable and are preferred over nested subqueries.

Syntax — CTE Syntax

WITH cte_name AS (
SELECT ... -- your subquery here
)
SELECT * FROM cte_name WHERE ...;
-- Multiple CTEs
WITH cte1 AS (SELECT ...),
cte2 AS (SELECT ... FROM cte1)
SELECT * FROM cte2;

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 19

 Example — Find departments where avg salary > company average
WITH company_avg AS (
SELECT AVG(salary) AS avg_sal FROM employees
),
dept_stats AS (
SELECT dept, AVG(salary) AS dept_avg
FROM employees
GROUP BY dept
)
SELECT [Link], d.dept_avg, c.avg_sal AS company_avg
FROM dept_stats d
CROSS JOIN company_avg c
WHERE d.dept_avg > c.avg_sal;
>> Engineering 77500 63750

Practice Questions
1. Rewrite this subquery as a CTE: SELECT * FROM orders WHERE amount > (SELECT AVG(amount)
FROM orders)
Hint: WITH avg_amt AS (SELECT AVG(amount) avg FROM orders) SELECT * FROM orders, avg_amt WHERE
amount > avg;

2. When would you use a CTE instead of a subquery?

Hint: When the subquery is reused multiple times, or when the query is complex and needs to be readable.

2.9 Window Functions — Advanced Analytics

Window functions perform calculations across a set of related rows WITHOUT collapsing them into one row.
They are the most powerful SQL feature for analytics and appear in almost every senior interview.

Syntax — Window Function Syntax

function_name() OVER (
PARTITION BY column -- like GROUP BY, but rows are not collapsed
ORDER BY column -- order within each partition
ROWS/RANGE clause -- optional: define window frame
)
Key window functions:
ROW_NUMBER() -- unique row number (1,2,3...) per partition
RANK() -- rank with gaps (1,2,2,4 if tie)
DENSE_RANK() -- rank without gaps (1,2,2,3 if tie)
LAG(col,n) -- value from n rows BEFORE current row
LEAD(col,n) -- value from n rows AFTER current row
SUM() OVER() -- running/cumulative sum
AVG() OVER() -- moving average
FIRST_VALUE() -- first value in the window
LAST_VALUE() -- last value in the window

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 20

 Example — Rank employees by salary within each department
SELECT
name,
dept,
salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_in_dept,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dense_rnk,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS row_num
FROM employees;
>> Priya Engineering 80000 1 1 1
>> Riya Engineering 75000 2 2 2
>> Arjun Marketing 55000 1 1 1
>> Karan HR 45000 1 1 1

Example — Running total and month-over-month change

SELECT
month,
revenue,
SUM(revenue) OVER (ORDER BY month) AS running_total,
LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_rev,
revenue - LAG(revenue,1) OVER (ORDER BY month) AS mom_change
FROM monthly_sales;
>> Jan 120000 120000 NULL NULL
>> Feb 145000 265000 120000 +25000
>> Mar 138000 403000 145000 -7000

Common Mistake
RANK() leaves gaps: if two people share rank 2, the next rank is 4. DENSE_RANK() gives rank 3
instead.
Window functions cannot be used in WHERE clause. Use a CTE or subquery to filter on window
function results.

Practice Questions
1. Find the top 1 earner from each department.
Hint: SELECT * FROM (SELECT *, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employees) t WHERE rnk=1;

2. Calculate a 3-month moving average of sales.

Hint: SELECT month, AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND
CURRENT ROW) FROM monthly_sales;

3. What is the difference between RANK() and DENSE_RANK()?

Hint: RANK: 1,2,2,4 (gap after tie). DENSE_RANK: 1,2,2,3 (no gap). Use DENSE_RANK when you don't want to
skip numbers.

4. Find the percentage each employee's salary contributes to their department total.
Hint: SELECT name, salary, SUM(salary) OVER (PARTITION BY dept) dept_total,
ROUND(salary*100.0/SUM(salary) OVER(PARTITION BY dept),2) AS pct FROM employees;

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 21

 Tools & Resources
MySQL Community Server — free, install locally: [Link]/downloads
PostgreSQL + pgAdmin — industry standard: [Link]
DB Fiddle ([Link]) — run SQL in browser instantly, no install
[Link] — another instant browser practice tool
LeetCode SQL 50 ([Link]/studyplan/top-sql-50) — essential interview prep
HackerRank SQL track — free, structured, certifications available
[Link] — real SQL questions from Google, Amazon, Meta interviews

Project — SQL Capstone — E-Commerce Database Analysis

Setup: Create 4 tables — customers, products, orders, order_items
Load sample data: 100 customers, 50 products, 500 orders, 1500 order_items
Query 1: Total revenue, total orders, average order value (aggregate functions)
Query 2: Top 10 customers by lifetime spend (JOIN + GROUP BY)
Query 3: Monthly revenue trend for the last 12 months (GROUP BY month, ORDER BY)
Query 4: Products never ordered (LEFT JOIN anti-join pattern)
Query 5: Customers with more than 5 orders (HAVING)
Query 6: Rank products by revenue using window functions
Query 7: Month-over-month revenue change using LAG()
Query 8: Customers who ordered in Jan but NOT in Feb (subquery / NOT IN)
Deliverable: SQL file with all queries + screenshots of results

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 22

 MODULE 3

Python for Data Analytics

Pandas + NumPy + Matplotlib — the analyst's Swiss Army knife for automating everything.

3.1 Python Basics — Variables, Lists, Dicts, Loops

Master these before touching Pandas — they are used constantly.

Syntax — Data Types & Variables

# Variables
name = 'Riya' # str
age = 25 # int
salary = 75000.50 # float
is_active = True # bool
# List — ordered, mutable, allows duplicates
sales = [120, 340, 210, 450, 300]
[Link](500) # add to end
sales[0] # access first item -> 120
sales[-1] # last item -> 500
sales[1:3] # slice -> [340, 210]
# Dictionary — key-value pairs
employee = {'name':'Riya', 'dept':'IT', 'salary':75000}
employee['dept'] # -> 'IT'
employee['city'] = 'Pune' # add new key

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 23

 Syntax — Control Flow & Functions
# For loop over list
for s in sales:
print(s)
# While loop
i = 0
while i < 5:
print(i)
i += 1
# Function definition
def calculate_tax(salary, rate=0.3):
tax = salary * rate
return tax
calculate_tax(75000) # -> 22500.0
calculate_tax(75000, 0.2) # -> 15000.0
# List comprehension (analyst's best friend)
doubled = [x*2 for x in sales] # [240,680,420,900,600]
high_sales = [x for x in sales if x > 300] # [340, 450, 300, 500]

Practice Questions
1. Write a function that takes a list of salaries and returns the average.
Hint: def avg(lst): return sum(lst)/len(lst)

2. Given a list of product prices, create a new list with 10% discount applied to all items above 500.
Hint: [p*0.9 if p>500 else p for p in prices]

3. Create a dictionary of monthly sales from two lists: months=['Jan','Feb'] and sales=[120,145].
Hint: dict(zip(months, sales)) or {m:s for m,s in zip(months,sales)}

3.2 NumPy — Fast Array Operations

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 24

 Syntax — NumPy Essentials
import numpy as np
arr = [Link]([10, 20, 30, 40, 50])
[Link] # (5,) — 1D array of 5 elements
[Link] # int64
# Statistics
[Link](arr) # 30.0
[Link](arr) # 30.0
[Link](arr) # 14.14
[Link](arr) # 10
[Link](arr) # 50
[Link](arr) # 150
# Array creation
[Link](5) # [0,0,0,0,0]
[Link](3) # [1,1,1]
[Link](0,10,2) # [0,2,4,6,8]
[Link](0,1,5) # [0, 0.25, 0.5, 0.75, 1.0]
# Filtering (boolean indexing)
arr[arr > 25] # [30,40,50]
arr[(arr>15) & (arr<45)] # [20,30,40]

Practice Questions
1. Create a NumPy array of 100 evenly spaced values between 0 and 1.
Hint: [Link](0, 1, 100)

2. Given a salary array, find all salaries above the 75th percentile.
Hint: [Link](salaries, 75) then salaries[salaries > [Link](salaries,75)]

3.3 Pandas — Load & Explore DataFrames

A DataFrame is a 2D table with named columns and rows — like Excel in Python. Pandas is the core tool for
data analysis.

Syntax — Loading Data

import pandas as pd
df = pd.read_csv('[Link]') # load CSV
df = pd.read_csv('[Link]', encoding='latin1') # if encoding error
df = pd.read_excel('[Link]', sheet_name='Sheet1')
df = pd.read_csv('url_string') # load from URL

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 25

 Syntax — First Steps — Always Run These
[Link] # (rows, columns) -> (9994, 13)
[Link]() # first 5 rows
[Link](10) # last 10 rows
[Link]() # column names, dtypes, non-null counts
[Link]() # stats: count, mean, std, min, max, quartiles
[Link] # list of column names
[Link] # data type of each column
[Link]().sum() # count of missing values per column
[Link]().sum() # count of duplicate rows
df['col'].value_counts() # frequency count of a categorical column
df['col'].unique() # all unique values in a column
df['col'].nunique() # count of unique values

Example — Exploring a sales dataset

df = pd.read_csv('[Link]')
print([Link]) # (9994, 13)
print([Link]())
>> RangeIndex: 9994 entries
>> order_id object (9994 non-null)
>> order_date object (9994 non-null) <- needs datetime conversion
>> sales float64 (9994 non-null)
>> profit float64 (37 non-null) <- 9957 missing values!
df['category'].value_counts()
>> Technology 4000
>> Furniture 3000
>> Office Supplies 2994

Practice Questions
1. After loading a CSV, what are the first 5 things you check?
Hint: [Link], [Link](), [Link](), [Link](), [Link]().sum()

2. How do you find which columns have more than 10% missing values?
Hint: [Link]().mean()[[Link]().mean() > 0.1]

3.4 Pandas — Filter, Select & Transform

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 26

 Syntax — Selecting Columns & Rows
df['salary'] # single column -> Series
df[['name','salary']] # multiple columns -> DataFrame
[Link][2] # row by label/index
[Link][0] # row by position (0=first)
[Link][0:5, 0:3] # rows 0-4, columns 0-2
# Filtering rows
df[df['salary'] > 60000] # single condition
df[(df['dept']=='IT') & (df['salary']>60000)] # AND condition
df[(df['city']=='Pune') | (df['city']=='Mumbai')] # OR condition
df[df['city'].isin(['Pune','Mumbai','Delhi'])] # IN list
df[df['name'].[Link]('Kumar', case=False)] # text filter
df[df['salary'].between(50000, 80000)] # range filter

Syntax — Creating & Transforming Columns

# New calculated column
df['annual_salary'] = df['monthly_salary'] * 12
df['profit_margin'] = df['profit'] / df['revenue'] * 100
# Conditional column (like IF in Excel)
df['level'] = df['salary'].apply(lambda x: 'Senior' if x>70000 else 'Junior')
# Using numpy where (faster)
import numpy as np
df['grade'] = [Link](df['marks']>=60, 'Pass', 'Fail')
# Apply a function to a column
df['name_upper'] = df['name'].[Link]()
df['city_clean'] = df['city'].[Link]().[Link]()
# Rename columns
[Link](columns={'old_name':'new_name'}, inplace=True)

Practice Questions
1. Filter a sales DataFrame to get only rows where Region='West' and Sales > 10000.
Hint: df[(df['Region']=='West') & (df['Sales']>10000)]

2. Create a new column 'tax' which is 18% of the 'price' column.

Hint: df['tax'] = df['price'] * 0.18

3. What is the difference between [Link] and [Link]?

Hint: loc uses label/index names. iloc uses integer positions (0-based). Use iloc for position-based, loc for
label-based access.

3.5 Pandas — GroupBy & Aggregation

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 27

 Syntax — GroupBy Operations
# Single aggregation
[Link]('dept')['salary'].mean()
[Link]('dept')['salary'].agg(['mean','max','min','count'])
# Multiple columns
[Link](['dept','city'])['salary'].sum()
# Named aggregations (agg with dict)
[Link]('dept').agg(
avg_sal=('salary', 'mean'),
headcount=('emp_id', 'count'),
max_sal=('salary', 'max')
).reset_index()
# Pivot table
df.pivot_table(values='sales', index='region',
columns='category', aggfunc='sum', fill_value=0)

Example — Department salary summary

result = [Link]('dept').agg(
headcount=('emp_id','count'),
avg_salary=('salary','mean'),
total_bill=('salary','sum')
).reset_index()
result['avg_salary'] = result['avg_salary'].round(0)
>> dept headcount avg_salary total_bill
>> Engineering 2 77500 155000
>> HR 1 45000 45000
>> Marketing 1 55000 55000

Practice Questions
1. Find total sales and average discount by Region and Category.
Hint: [Link](['Region','Category']).agg(total_sales=('Sales','sum'),avg_disc=('Discount','mean')).reset_index()

2. Which city has the highest number of customers? (use value_counts)

Hint: df['city'].value_counts().index[0] or [Link]('city')['cust_id'].count().idxmax()

3.6 Matplotlib & Seaborn — Data Visualisation

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 28

 Syntax — Matplotlib — Basic Charts
import [Link] as plt
# Bar chart
[Link](figsize=(10,5))
[Link](df['region'], df['revenue'], color='steelblue')
[Link]('Revenue by Region')
[Link]('Region')
[Link]('Revenue')
plt.tight_layout()
[Link]()
# Line chart
[Link](df['month'], df['sales'], marker='o', color='green')
# Histogram
[Link](df['salary'], bins=20, color='purple', edgecolor='white')
# Scatter plot
[Link](df['marketing_spend'], df['revenue'], alpha=0.5)

Syntax — Seaborn — Statistical Visualisation

import seaborn as sns
# Box plot — shows distribution + outliers
[Link](x='dept', y='salary', data=df)
# Heatmap — correlation matrix
[Link]([Link](), annot=True, cmap='coolwarm', fmt='.2f')
# Bar plot with error bars
[Link](x='region', y='sales', data=df, estimator='mean')
# Pair plot — relationships between all numeric columns
[Link](df[['sales','profit','discount']])
# Count plot — frequency of categories
[Link](x='category', data=df, order=df['category'].value_counts().index)

Note
Chart type guide: Bar=compare categories, Line=trend over time, Scatter=relationship,
Box=distribution+outliers, Heatmap=correlation.
Always add [Link](), [Link](), [Link]() — unlabelled charts are meaningless in a presentation.

Practice Questions
1. Plot a bar chart showing top 10 products by total revenue.
Hint: top10 = [Link]('product')['revenue'].sum().nlargest(10); [Link](kind='bar')

2. Create a correlation heatmap for a DataFrame with columns: sales, profit, discount, quantity.
Hint: [Link](df[['sales','profit','discount','quantity']].corr(), annot=True, cmap='coolwarm')

3. What does a box plot tell you that a bar chart does not?
Hint: Box plot shows median, Q1, Q3, IQR, and outliers — the full distribution, not just the average.

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 29

 Tools & Resources
Anaconda Distribution — installs Python+Jupyter+all libraries: [Link]
Google Colab — free Jupyter notebook with GPU: [Link]
Jupyter Notebook — run via 'jupyter notebook' in terminal after Anaconda install
Install libraries: pip install pandas numpy matplotlib seaborn openpyxl xlrd
Kaggle Datasets — 1000s of free practice datasets: [Link]/datasets
Pandas documentation: [Link]/docs

Project — Python Capstone — Netflix Content Analysis

Dataset: Netflix Movies and TV Shows from Kaggle (8807 rows)
Step 1: Load and inspect — shape, dtypes, missing values count
Step 2: Clean — fix date_added column to datetime, handle NaN in country/director
Step 3: Analysis Q1 — What % of content is Movies vs TV Shows?
Step 4: Analysis Q2 — Which country produces the most content? (top 10 bar chart)
Step 5: Analysis Q3 — Content added per year trend (line chart)
Step 6: Analysis Q4 — Top 10 directors by number of titles
Step 7: Analysis Q5 — Most common genres (explode listed_in column, value_counts)
Step 8: Analysis Q6 — Average movie duration by genre (pivot table)
Deliverable: Jupyter notebook with markdown explanations + 6 charts + 5 business insights

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 30

 MODULE 4

Statistics & Probability

The math behind every chart, metric and business decision. Interviewers love this.

4.1 Descriptive Statistics — Summarise Your Data

Syntax — Measures of Central Tendency

Mean = sum of all values / count of values
= (10+20+30+40+50) / 5 = 30
Median = middle value when sorted
= [10, 20, 30, 40, 50] -> middle = 30
For even count: average of two middle values
= [10,20,30,40] -> (20+30)/2 = 25
Mode = most frequently occurring value
= [1,2,2,3,4,4,4,5] -> mode = 4
When to use:
Mean — when data has no outliers (symmetric distribution)
Median — when data has outliers (salaries, house prices)
Mode — for categorical data (most popular product)

Syntax — Measures of Spread

Range = Max - Min (simplest, affected by outliers)
= 50 - 10 = 40
Variance = average of squared deviations from mean
= sum((xi - mean)^2) / n
Std Dev = square root of variance (same unit as data)
= high std dev -> data is spread out
= low std dev -> data is clustered near mean
IQR (Interquartile Range) = Q3 - Q1
Q1 = 25th percentile (25% of data below this)
Q3 = 75th percentile (75% of data below this)
IQR = resistant to outliers
Outlier rule: value < Q1 - 1.5*IQR OR > Q3 + 1.5*IQR

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 31

 Example — Salary dataset analysis in Python
import numpy as np
salaries = [42000, 45000, 55000, 60000, 75000, 80000, 250000]
[Link](salaries) # 86714 <- pulled up by 250000
[Link](salaries) # 60000 <- better central estimate
[Link](salaries) # 67561
[Link](salaries, 25) # Q1 = 45000
[Link](salaries, 75) # Q3 = 80000
IQR = 80000 - 45000 # 35000
upper_fence = 80000 + 1.5*35000 # 132500
# 250000 > 132500 -> it is an outlier

Practice Questions
1. A dataset of house prices has mean=80L and median=55L. What does this tell you?
Hint: Mean is pulled up by expensive outliers. Median is the better central measure here. Distribution is
right-skewed.

2. Explain what a high standard deviation tells you about a dataset.

Hint: Data points are spread far from the mean — high variability. Low std dev = data is tightly clustered.

3. Calculate IQR for: [5, 7, 8, 12, 15, 18, 20, 25, 30]. Is 30 an outlier?
Hint: Q1=7.5, Q3=22.5, IQR=15. Upper fence=22.5+22.5=45. 30 < 45, so NOT an outlier.

4.2 Probability Distributions

Syntax — Normal Distribution (Bell Curve)

Properties:
Symmetric around the mean
Mean = Median = Mode
Defined by mean (mu) and std dev (sigma)
68-95-99.7 Rule:
68% of data falls within 1 std dev of mean
95% of data falls within 2 std dev of mean
99.7% of data falls within 3 std dev of mean
Real-world example: Heights, exam scores, measurement errors
Z-score = (value - mean) / std_dev
= how many standard deviations a value is from the mean
Z = 2 means the value is 2 std devs above the mean (top ~2.5%)

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 32

 Example — Z-score interpretation
Exam scores: mean=65, std_dev=10
Student A scored 85:
Z = (85-65)/10 = 2.0
-> Scored 2 std devs above mean -> top ~2.3% of class
Student B scored 50:
Z = (50-65)/10 = -1.5
-> 1.5 std devs BELOW mean

Practice Questions
1. IQ scores have mean=100 and std_dev=15. What % of people have IQ between 85 and 115?
Hint: 85 to 115 is mean +/- 1 std dev. By the 68-95-99.7 rule: 68% of people.

2. A factory produces bolts with diameter mean=10mm, std=0.2mm. A bolt is 10.5mm. Is it unusual?
Hint: Z=(10.5-10)/0.2=2.5. Being 2.5 std devs away is unusual (only ~1.2% of bolts are this large).

4.3 Hypothesis Testing & A/B Testing

Syntax — Hypothesis Testing Framework

Step 1: State hypotheses
H0 (Null): no effect / no difference (the status quo)
H1 (Alternative): there IS an effect / difference
Step 2: Choose significance level
alpha = 0.05 (5%) is the industry standard
Step 3: Run the test, get p-value
p-value = probability of seeing this result if H0 is true
Step 4: Decision rule
If p < alpha (0.05) -> Reject H0 -> result is significant
If p >= alpha -> Fail to reject H0 -> not significant
Common tests:
t-test — compare means of two groups
chi-square — compare proportions / categorical data
ANOVA — compare means of 3+ groups

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 33

 Example — A/B Test: Did the new button increase clicks?
from scipy import stats
# Group A (old button): 200 visitors, 20 clicks (10% CTR)
# Group B (new button): 200 visitors, 30 clicks (15% CTR)
group_a = [1]*20 + [0]*180 # 1=clicked, 0=did not
group_b = [1]*30 + [0]*170
t_stat, p_value = stats.ttest_ind(group_a, group_b)
print(f'p-value: {p_value:.4f}')
>> p-value: 0.0312
# p=0.031 < 0.05 -> Reject H0
# Conclusion: New button significantly increases clicks.
# The 5% improvement is statistically significant.

Practice Questions
1. A/B test: Version A has 8% conversion, Version B has 9.5%. p-value=0.12. What do you conclude?
Hint: p=0.12 > 0.05. Fail to reject H0. The difference is NOT statistically significant — could be random.

2. What is the difference between statistical significance and practical significance?

Hint: Statistical: unlikely due to chance (p<0.05). Practical: large enough to matter to business. A 0.1% change
can be statistically significant but practically useless.

4.4 Correlation — Relationships Between Variables

Syntax — Pearson Correlation Coefficient

r = correlation coefficient (ranges from -1 to +1)
r = 1.0 -> perfect positive correlation
r = 0.8 -> strong positive correlation
r = 0.4 -> moderate positive correlation
r = 0.0 -> no linear correlation
r = -0.4 -> moderate negative correlation
r = -0.8 -> strong negative correlation
r = -1.0 -> perfect negative correlation
In Python:
[Link]() # correlation matrix
df['col1'].corr(df['col2']) # correlation between two columns
import seaborn as sns
[Link]([Link](), annot=True, cmap='coolwarm')

Common Mistake
Correlation does NOT mean causation. Example: Ice cream sales and drowning rates are correlated
(both rise in summer). The cause is hot weather, not ice cream.
Correlation only measures LINEAR relationships. Two variables can be strongly related but have r=0 if
the relationship is curved.

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 34

 Practice Questions
1. Correlation between ad_spend and revenue is 0.85. What does this mean?
Hint: Strong positive linear relationship. As ad spend increases, revenue tends to increase. But we can't say ad
spend CAUSES revenue without further analysis.

2. What is the danger of acting on correlation without checking causation?

Hint: You might invest in something that appears to predict an outcome but is just coincidentally related. Always
look for mechanism/causation.

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 35

 MODULE 5

Power BI — Data Visualisation &

Dashboards
Turn tables of numbers into stories that executives understand in seconds.

5.1 Power BI Workflow — From Data to Dashboard

Syntax — Step-by-Step Power BI Workflow

Step 1: Get Data
Home tab -> Get Data -> Choose source
Sources: Excel, CSV, SQL Server, SharePoint, Web
Step 2: Transform in Power Query
Remove unnecessary columns
Fix data types (text to date, text to number)
Filter rows, merge queries, unpivot columns
Each action creates a 'step' (visible and editable)
Step 3: Load to Data Model
Close & Apply -> data loads into Power BI
Step 4: Create Relationships (Model View)
Drag key from one table to matching key in another
One-to-many is the most common relationship type
Step 5: Write DAX Measures
Create calculated KPIs for your report
Step 6: Build Report
Add visuals -> assign fields -> format -> add slicers

5.2 DAX — Key Measures with Syntax & Examples

Syntax — Basic Aggregation Measures

Total Sales = SUM(Sales[Revenue])
Total Orders = COUNTROWS(Orders)
Unique Customers = DISTINCTCOUNT(Orders[CustomerID])
Avg Order Value = DIVIDE([Total Sales], [Total Orders])
Profit Margin % = DIVIDE([Total Profit],[Total Sales]) * 100

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 36

 Syntax — CALCULATE — The Most Important DAX Function
-- CALCULATE changes the filter context of a measure
Syntax: CALCULATE(expression, filter1, filter2, ...)
Sales West = CALCULATE([Total Sales], Region[Region]="West")
Sales 2024 = CALCULATE([Total Sales],
YEAR(Calendar[Date]) = 2024)
High Value Sales = CALCULATE([Total Sales],
Sales[Revenue] > 10000)

Syntax — Time Intelligence — Year-to-Date, Month-over-Month

Sales YTD = TOTALYTD([Total Sales], Calendar[Date])
Sales MTD = TOTALMTD([Total Sales], Calendar[Date])
Sales Last Month = CALCULATE([Total Sales],
DATEADD(Calendar[Date], -1, MONTH))
MoM Growth % =
DIVIDE([Total Sales] - [Sales Last Month],
[Sales Last Month]) * 100
-- Requires a Calendar/Date table marked as date table

Practice Questions
1. Write a DAX measure to calculate % of total sales for each product.
Hint: Pct of Total = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Products)))

2. What does CALCULATE() do? Why is it the most important DAX function?
Hint: It evaluates an expression in a modified filter context. Without CALCULATE, you can't override slicers or
filters — it's the key to all advanced DAX.

3. What is the difference between a Measure and a Calculated Column?

Hint: Measure: computed dynamically based on filter context, stored as formula only. Calculated Column:
computed row by row at data refresh, stored in model — uses memory.

5.3 Star Schema — Data Modelling Best Practice

Syntax — Star Schema Structure

Fact Table (centre of the star):
Contains measurable events: orders, transactions, sales
Has foreign keys linking to dimension tables
Columns: order_id, cust_id, prod_id, date_id, quantity, revenue
Dimension Tables (points of the star):
dim_customer: cust_id, name, city, segment
dim_product: prod_id, name, category, sub_category
dim_date: date_id, date, year, quarter, month, weekday
Why Star Schema?
Faster queries (fewer JOINs needed)
DAX measures work correctly with proper relationships
Easier to understand for business users

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 37

 Practice Questions
1. You have one big flat Excel file with all data in one sheet. How would you restructure it into a star
schema for Power BI?
Hint: Split into fact_orders, dim_customer, dim_product, dim_date. Remove redundant columns from fact. Link
via keys.

2. Why should you NOT use a big flat table directly in Power BI?
Hint: Redundant data, larger file size, DAX time intelligence won't work correctly, harder to maintain.

Tools & Resources

Power BI Desktop — free: [Link]/downloads
Power BI Service — publish and share dashboards free account available
AdventureWorks sample data — search 'AdventureWorks Power BI dataset'
[Link] — best free DAX learning resource
Guy in a Cube (YouTube) — best free Power BI tutorials
Tableau Public — free alternative to Power BI with public gallery

Project — Power BI Capstone — Sales Performance Report

Dataset: Superstore Sales (Excel) — already cleaned from Module 1
Step 1: Load data into Power BI, transform in Power Query (fix date types)
Step 2: Create dim_customer, dim_product, dim_date tables from the flat file
Step 3: Build relationships in Model view (star schema)
Step 4: Create measures — Total Revenue, Total Orders, Avg Order Value, Profit Margin%, YTD Sales
Step 5: Page 1 (Executive Summary): 4 KPI cards + Revenue trend line + Region bar chart
Step 6: Page 2 (Product Analysis): Category breakdown + Top 10 products table + Subcategory
treemap
Step 7: Page 3 (Customer Analysis): Customer count by segment + Top 10 customers
Step 8: Add slicers for Year and Region (synced across all pages)
Deliverable: Publish to Power BI Service. Share the link. Screenshot for portfolio.

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 38

 MODULE 6

Data Cleaning & Real-World EDA

80% of analyst work is here. This is where junior analysts separate from good ones.

6.1 Handling Missing Values — Complete Playbook

Syntax — Detect Missing Values

[Link]().sum() # count per column
[Link]().sum() / len(df) * 100 # percentage missing
df[df['salary'].isnull()] # rows where salary is missing
[Link]().any(axis=1).sum() # rows with ANY missing value

Syntax — Handle Missing Values

# Drop rows with missing values
[Link]() # drop rows with ANY null
[Link](subset=['salary','dept']) # drop only if these are null
[Link](thresh=5) # keep rows with at least 5 non-null
# Drop columns with too many nulls (>50%)
[Link](axis=1, thresh=len(df)*0.5)
# Fill with statistics (numerical)
df['salary'].fillna(df['salary'].mean(), inplace=True) # fill with mean
df['salary'].fillna(df['salary'].median(), inplace=True) # fill with median
# Fill with mode (categorical)
df['city'].fillna(df['city'].mode()[0], inplace=True)
# Fill with a constant
df['discount'].fillna(0, inplace=True)
# Forward fill (carry last valid value forward)
df['price'].fillna(method='ffill', inplace=True)
# Fill based on group (impute by category)
df['salary'] = [Link]('dept')['salary'].transform(
lambda x: [Link]([Link]()))

Note
Decision guide: < 5% missing → drop rows. 5-30% → impute (mean for normal dist, median for
skewed). > 30% missing → consider dropping the whole column.
Always impute AFTER splitting into train/test (if doing ML). Otherwise you leak information.

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 39

 Practice Questions
1. A DataFrame has 10000 rows. The 'age' column has 450 nulls. The distribution is right-skewed. How
do you handle this?
Hint: 450/10000 = 4.5% — could drop or impute. Since right-skewed, impute with median (not mean).

2. The 'phone_number' column has 60% missing. What do you do?

Hint: 60% is too much to impute reliably. Drop the column. Document the decision.

6.2 Outlier Detection & Treatment

Syntax — Detect Outliers

# IQR method
Q1 = df['salary'].quantile(0.25)
Q3 = df['salary'].quantile(0.75)
IQR = Q3 - Q1
lower = Q1 - 1.5 * IQR
upper = Q3 + 1.5 * IQR
outliers = df[(df['salary'] < lower) | (df['salary'] > upper)]
# Z-score method (for normally distributed data)
from scipy import stats
z_scores = [Link]([Link](df['salary']))
outliers = df[z_scores > 3] # more than 3 std devs away
# Visualise with box plot
import seaborn as sns
[Link](x=df['salary'])

Syntax — Treat Outliers

# Option 1: Remove outliers
df_clean = df[(df['salary'] >= lower) & (df['salary'] <= upper)]
# Option 2: Cap/Clip outliers (Winsorization)
df['salary'] = df['salary'].clip(lower=lower, upper=upper)
# Option 3: Log transform (reduces impact of large values)
df['salary_log'] = np.log1p(df['salary'])

Practice Questions
1. You find age values of -5, 0, and 999 in your dataset. How do you handle them?
Hint: These are data entry errors. Replace with NaN using df['age'] = df['age'].where(df['age'].between(1,120)).
Then impute.

2. When would you cap outliers instead of removing them?

Hint: When outliers are valid but extreme (e.g., a legitimate 10-crore sale). Removing would lose real data.
Capping preserves the observation while reducing distortion.

6.3 Full EDA Framework — Step by Step

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 40

 Syntax — 7-Step EDA Checklist
Step 1: Understand the domain
What does each column mean? What are the business questions?
Step 2: Shape and types
[Link], [Link](), [Link]
Step 3: Missing values
[Link]().sum(), visualise with [Link]([Link]())
Step 4: Duplicates
[Link]().sum(), df[[Link]()]
Step 5: Univariate analysis
Numerical: [Link](), histograms, box plots
Categorical: value_counts(), count plots
Step 6: Bivariate & multivariate analysis
Scatter plots, correlation heatmap, group comparisons
Step 7: Business insights
Answer 5 specific questions from the data
e.g., Which region is most profitable? Which product has highest return rate?

Practice Questions
1. You are given a new dataset with 50 columns and 100,000 rows. Walk through your first 15 minutes
of EDA.
Hint: 1) [Link] 2) [Link]() 3) [Link]() 4) [Link]().sum() 5) [Link]().sum() 6) value_counts on
categoricals 7) histograms for numerics 8) correlation heatmap

2. How do you find the most correlated features with a target variable 'churn'?
Hint: [Link]()['churn'].sort_values(ascending=False). Or use a heatmap.

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 41

 MODULE 7

Capstone Projects & Interview Preparation

3 portfolio-ready projects + complete interview Q&A; to land your first 7 LPA job.

Capstone Project 1 — End-to-End Sales Analysis

Project — Sales Analysis — Excel + SQL + Python + Power BI

Dataset: Superstore Sales (Kaggle) — 9994 rows, 13 columns

PHASE 1 — Excel:
Clean data: remove duplicates, fix date format, fill blanks
Create calculated columns: Profit Margin %, Days to Ship
Build Pivot Table: Revenue by Region + Category
Build interactive dashboard with 3 slicers

PHASE 2 — SQL:
Import CSV to MySQL database
Query 1: Monthly revenue trend for 2023
Query 2: Top 5 customers by lifetime value (JOIN + GROUP BY)
Query 3: Products with negative profit (WHERE)
Query 4: City-wise order count using HAVING > 50
Query 5: Rank sub-categories by profit using DENSE_RANK()

PHASE 3 — Python EDA:

Full 7-step EDA in Jupyter Notebook
Insight 1: Which segment has highest profit margin?
Insight 2: Is there a correlation between discount and profit?
Insight 3: Monthly revenue trend + MoM growth rate
Insight 4: Shipping mode analysis — cost vs speed
Insight 5: Identify loss-making products — how many? Why?

PHASE 4 — Power BI Dashboard:

3-page report: Overview, Product Deep Dive, Regional Analysis
YTD Revenue, MoM Growth%, Profit Margin% KPI cards
All pages connected with Year + Region slicers

Deliverable: GitHub repo with README, SQL file, Jupyter notebook, Power BI .pbix file

Capstone Project 2 — Customer Churn Analysis

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 42

 Project — Telco Customer Churn — Python + Power BI
Dataset: Telco Customer Churn (Kaggle) — 7043 customers, 21 columns

Step 1: Load and inspect — check churn rate (what % of customers churned?)
Step 2: EDA on churned vs retained customers:
Does contract type affect churn? (Month-to-month vs annual)
Does tenure affect churn? (New customers churn more?)
Does payment method affect churn?
Does internet service type affect churn?
Step 3: Calculate churn rate by each segment using Pandas groupby
Step 4: Create visualisations:
Bar chart: Churn rate by contract type
Box plot: Tenure distribution for churned vs retained
Heatmap: Correlation of all features with churn
Step 5: Power BI dashboard for HR/CX team:
Overall churn rate KPI, churn by segment, high-risk customer list
Step 6: Business recommendations (written — 3 clear actions to reduce churn)

Deliverable: Jupyter notebook + Power BI file + 1-page PDF executive summary

Complete Interview Q&A;

7.1 SQL Interview Questions (Most Asked)

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 43

 Practice Questions
1. Find the second highest salary from the employees table.
Hint: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); OR
SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

2. Find duplicate email addresses in a users table.

Hint: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

3. Delete duplicate rows but keep one.

Hint: DELETE FROM users WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email);

4. Find employees who earn more than their manager.

Hint: SELECT [Link] FROM employees e JOIN employees m ON e.manager_id=[Link] WHERE [Link] >
[Link];

5. Write a query to find the Nth highest salary.

Hint: SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET N-1;

6. What is the difference between UNION and UNION ALL?

Hint: UNION removes duplicates. UNION ALL keeps all rows including duplicates (faster).

7. Find customers who placed orders in January but NOT in February.

Hint: SELECT cust_id FROM orders WHERE MONTH(order_date)=1 AND cust_id NOT IN (SELECT cust_id
FROM orders WHERE MONTH(order_date)=2);

7.2 Python Interview Questions

Practice Questions
1. How do you handle missing values in a Pandas DataFrame?
Hint: [Link]().sum() to find. dropna() to remove. fillna(mean/median/mode) to impute. Choose based on %
missing and distribution.

2. What is the difference between apply() and map() in Pandas?

Hint: map(): works on a Series, element-by-element. apply(): works on Series OR DataFrame, can apply complex
functions. applymap(): element-wise on DataFrame.

3. How do you merge two DataFrames in Pandas?

Hint: [Link](df1, df2, on='key', how='left/right/inner/outer'). Similar to SQL JOINs.

4. What does groupby().transform() do? How is it different from groupby().agg()?

Hint: agg() collapses to one row per group. transform() returns same-length output — fills group statistics back
into original DataFrame. Use for imputing by group mean.

5. How do you find and remove duplicate rows?

Hint: [Link]().sum() to count. df.drop_duplicates() to remove. df.drop_duplicates(subset=['col1','col2']) for
specific columns.

7.3 HR & Behavioural Questions

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 44

 Practice Questions
1. Tell me about yourself.
Hint: Structure: 1) Who you are + educational background. 2) What you've learned (skills from this course). 3)
Projects done. 4) Why this role excites you. Keep it to 90 seconds.

2. Why data analytics?

Hint: Answer honestly — mention curiosity about data, the ability to drive decisions with numbers, and specific
things you found interesting while learning SQL/Python.

3. Walk me through one of your projects.

Hint: Use STAR: Situation (dataset, context), Task (business question), Action (tools + steps), Result (insights +
impact). Quantify wherever possible.

4. Where do you see yourself in 3 years?

Hint: Senior Data Analyst → move toward Data Engineering or Business Intelligence. Mention interest in specific
domain (fintech, e-commerce, healthcare). Show ambition without overpromising.

6-Month Roadmap to Your First Job

Month Focus Area Weekly Hours Milestone

Month 1 Excel — all 10 topics + Project 1 10 hrs/week Excel dashboard on GitHub

Month 2 SQL — SELECT to Window Functions +12

LeetCode
hrs/weekSQL SQL
30 project + HackerRank SQL cert

Month 3 Python — Basics to Pandas + Netflix EDA

14 hrs/week Jupyter notebook on GitHub

Month 4 Statistics + Power BI + Superstore Dashboard

12 hrs/week Power BI dashboard on Power BI Service

Month 5 Capstone Projects 1 & 2 + Portfolio setup

15 hrs/week GitHub + LinkedIn ready portfolio

Month 6 Interview prep + Apply to 50+ jobs 10 hrs/week First offer at 7 LPA+

Note
Start applying from Month 5, not Month 6. Interviews take time. Apply while you are still building.
Target companies: TCS, Wipro, Infosys (analyst roles), startups on LinkedIn, consulting firms (Deloitte,
KPMG analytics).
Certifications to add on resume: Google Data Analytics (Coursera), HackerRank SQL, Microsoft Power
BI Data Analyst (PL-300).

Data Analytics Complete Learning Path | Zero to 7 LPA+ Page 45