Data Analytics

𝗔𝗿𝘁𝗶𝗳𝗶𝗰𝗶𝗮𝗹 𝗜𝗻𝘁𝗲𝗹𝗹𝗶𝗴𝗲𝗻𝗰𝗲 𝗮𝗻𝗱 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗣𝗿𝗼𝗴𝗿𝗮𝗺 𝗯𝘆 𝗖𝗖𝗘, 𝗜𝗜𝗧 𝗠𝗮𝗻𝗱𝗶😍 Freshers get 15 LPA Average Salary with AI & ML Skills! - Eligibility: Open to everyone - Duration: 6 Months - Program Mode: Online - Taught By: IIT Mandi Professors 90% Resumes without AI + ML skills are being rejected. 🔥Deadline :- 26th April   𝗔𝗽𝗽𝗹𝘆 𝗡𝗼𝘄👇 :-  https://pdlink.in/3QSxhjC . Get Placement Assistance With 5000+ Companies

🔥 Let’s move to the next topic in the SQL Roadmap: ✅ GROUP BY & Aggregation Functions 🧠 1. What is GROUP BY? GROUP BY is used to group rows with same values 👉 It helps you summarize data 💡 Example Table: employees name department salary Amit IT 60000 Neha HR 40000 Ravi IT 70000 Sara HR 50000 👉 Without GROUP BY SELECT AVG(salary) FROM employees; ✔ Gives overall average 👉 With GROUP BY SELECT department, AVG(salary) FROM employees GROUP BY department; ✔ Gives average salary per department ⚡ 2. Aggregation Functions These functions perform calculations on data 🔹 COUNT() → number of rows SELECT COUNT() FROM employees; 🔹 SUM() → total SELECT SUM(salary) FROM employees; 🔹 AVG() → average SELECT AVG(salary) FROM employees; 🔹 MIN() → smallest value SELECT MIN(salary) FROM employees; 🔹 MAX() → largest value SELECT MAX(salary) FROM employees; 🎯 3. GROUP BY + Aggregation 👉 Count employees in each department SELECT department, COUNT() FROM employees GROUP BY department; 👉 Total salary per department SELECT department, SUM(salary) FROM employees GROUP BY department; 👉 Highest salary per department SELECT department, MAX(salary) FROM employees GROUP BY department; 🚨 4. Important Rule (Interview Favorite) 👉 Every column in SELECT must be: - Either inside GROUP BY - Or used with aggregation function ❌ Wrong: SELECT name, AVG(salary) FROM employees; ✅ Correct: SELECT department, AVG(salary) FROM employees GROUP BY department; 🎯 5. Practice Tasks 1. Count total employees 2. Find total salary of all employees 3. Find average salary per department 4. Find maximum salary in each department 5. Count employees in each department ✅ Practice Task Solution ✅ 1. Count total employees SELECT COUNT() FROM employees; ✅ 2. Find total salary of all employees SELECT SUM(salary) FROM employees; ✅ 3. Find average salary per department SELECT department, AVG(salary) FROM employees GROUP BY department; ✅ 4. Find maximum salary in each department SELECT department, MAX(salary) FROM employees GROUP BY department; ✅ 5. Count employees in each department SELECT department, COUNT() FROM employees GROUP BY department; ⚡ Mini Challenge 🔥 👉 Find department with highest average salary ⚡ Mini Challenge Solution 🔥 SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department ORDER BY avg_salary DESC LIMIT 1; ⚡ Double Tap ❤️ For More

𝐏𝐚𝐲 𝐀𝐟𝐭𝐞𝐫 𝐏𝐥𝐚𝐜𝐞𝐦𝐞𝐧𝐭 - 𝐆𝐞𝐭 𝐏𝐥𝐚𝐜𝐞𝐝 𝐈𝐧 𝐓𝐨𝐩 𝐌𝐍𝐂'𝐬 😍 Learn Coding From Scratch - Lectures Taught By IIT Alumni 60+ Hiring Drives Every Month 𝐇𝐢𝐠𝐡𝐥𝐢𝐠𝐡𝐭𝐬:-  🌟 Trusted by 7500+ Students 🤝 500+ Hiring Partners 💼 Avg. Rs. 7.4 LPA 🚀 41 LPA Highest Package Eligibility: BTech / BCA / BSc / MCA / MSc 𝐑𝐞𝐠𝐢𝐬𝐭𝐞𝐫 𝐍𝐨𝐰👇 :-  https://pdlink.in/4hO7rWY Hurry, limited seats available!🏃‍♀️

Every day you login... Work.. and logout. Days become months. Months become years. But nothing changes. Same role. Same work. Same pay. Meanwhile, others are moving into Cloud & Data Engineering… building real systems and earning better. If you are looking to get into Azure Data Engineering then.. 𝗝𝗼𝗶𝗻 𝘁𝗵𝗲 3 months 𝗟𝗶𝘃𝗲 𝗣𝗿𝗼𝗴𝗿𝗮𝗺 📌 Start Date: 20th April 2026 ⏰ Time: 9 PM – 10 PM IST | Monday 👉 𝐌𝐞𝐬𝐬𝐚𝐠𝐞 𝐮𝐬 𝐨𝐧 𝐖𝐡𝐚𝐭𝐬𝐀𝐩𝐩: https://wa.me/917032678595?text=Interested_to_join_Azure_Data_Engineering_live_sessions 🔹 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗵𝗲𝗿𝗲: https://forms.gle/DRXEhvyG9ENDsNYR9 🎟️ 𝗝𝗼𝗶𝗻 𝗪𝗵𝗮𝘁𝘀𝗔𝗽𝗽 𝗚𝗿𝗼𝘂𝗽: https://chat.whatsapp.com/GCG3Si7vhrJD1evV9NAbhL 🏀 𝗖𝗼𝘂𝗿𝘀𝗲 𝗖𝗼𝗻𝘁𝗲𝗻𝘁: https://drive.google.com/file/d/1QKqhRMHx2SDNDTmPAf3_54fA6LljKHm6/view

Now, Let’s move to the next topic of the SQL Roadmap: ORDER BY LIMIT 🧠 1. ORDER BY (Sorting Data) ORDER BY is used to sort your result. 👉 Syntax

SELECT column_name FROM table_name
ORDER BY column_name;

🔹 Ascending Order (Default)

SELECT * FROM employees
ORDER BY salary ASC;

✔ Lowest salary → highest 🔹 Descending Order

SELECT * FROM employees
ORDER BY salary DESC;

✔ Highest salary → lowest 💡 2. Sorting Multiple Columns

SELECT * FROM employees
ORDER BY department ASC, salary DESC;

👉 First sorts by department 👉 Then salary within each department 🎯 3. LIMIT (Control Output Size) LIMIT is used to restrict the number of rows. 👉 Syntax

SELECT * FROM table_name
LIMIT number;

👉 Example

SELECT * FROM employees
LIMIT 5;

✔ Returns only the first 5 rows ⚡ 4. Using ORDER BY LIMIT 👉 Top 5 highest salaries

SELECT * FROM employees
ORDER BY salary DESC
LIMIT 5;

👉 Lowest 3 salaries

SELECT * FROM employees
ORDER BY salary ASC
LIMIT 3;

🎯 5. Practice Tasks 1. Show all employees sorted by salary (ascending) 2. Show all employees sorted by salary (descending) 3. Get top 3 highest paid employees 4. Get lowest 2 salary employees 5. Sort employees by department and salary ✅ Practice Task Solution ✅ 1. Show all employees sorted by salary (ascending)

SELECT * FROM employees
ORDER BY salary ASC;

✅ 2. Show all employees sorted by salary (descending)

SELECT * FROM employees
ORDER BY salary DESC;

✅ 3. Get top 3 highest paid employees

SELECT * FROM employees
ORDER BY salary DESC
LIMIT 3;

✅ 4. Get lowest 2 salary employees

SELECT * FROM employees
ORDER BY salary ASC
LIMIT 2;

✅ 5. Sort employees by department and salary

SELECT * FROM employees
ORDER BY department ASC, salary DESC;

👉 First sorts by department 👉 Then highest salary inside each department ⚡ Mini Challenge 🔥 👉 Get the 2nd highest salary employee. ⚡ Mini Challenge Solution 🔥 ✔ Method 1 (Using LIMIT + OFFSET)

SELECT * FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

✔ Method 2 (Alternative way)

SELECT * FROM employees
ORDER BY salary DESC
LIMIT 1, 1;

🔥 Pro Tip: If you understand OFFSET → you can get Top N, 2nd highest, 3rd highest easily. ⚡ Double Tap ❤️ For More

𝗜𝗜𝗧 & 𝗜𝗜𝗠 𝗢𝗳𝗳𝗲𝗿𝗶𝗻𝗴 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗣𝗿𝗼𝗴𝗿𝗮𝗺𝘀😍 👉Open for all. No Coding Background Required AI/ML By IIT Patna  :- https://pdlink.in/41ZttiU Business Analytics With AI :- https://pdlink.in/41h8gRt Digital Marketing With AI :-https://pdlink.in/47BxVYG AI/ML By IIT Mandi :- https://pdlink.in/4cvXBaz 🔥Get Placement Assistance With 5000+ Companies🎓

Now, let’s move to the next topic in SQL Roadmap ✍️ SELECT WHERE This is the most important beginner topic 👇 🧠 1. SELECT Statement • SELECT is used to retrieve data from a table 👉 Basic Syntax SELECT column_name FROM table_name; 👉 Example SELECT name FROM employees; • ✔ Returns only the name column 👉 Select Multiple Columns SELECT name, salary FROM employees; 👉 Select All Columns SELECT * FROM employees; ✔ ** means everything 🎯 2. WHERE Clause (Filtering Data) • WHERE is used to filter records based on conditions 👉 Syntax SELECT * FROM table_name WHERE condition; 👉 Example SELECT * FROM employees WHERE salary > 50000; ✔ Returns employees earning more than 50k ⚡ 3. Operators You Must Know 🔹 Comparison Operators • = (equal) • > (greater than) • < (less than) • >= , <= • != or <> (not equal) 🔹 Logical Operators • AND → both conditions true • OR → any condition true • NOT → reverse condition 👉 Example SELECT * FROM employees WHERE department = 'IT' AND salary > 50000; 💡 4. Real-Life Thinking Instead of memorizing, think like this: • 👉 “What data do I need?” • 👉 “From which table?” • 👉 “What condition?” Example: “Show all HR employees earning less than 40k” SELECT * FROM employees WHERE department = 'HR' AND salary < 40000; 🎯 5. Practice Tasks 1. Show all employees with salary > 30k 2. Show employees from IT department 3. Show employees with salary between 40k–80k 4. Display only names of HR employees 5. Combine conditions using AND / OR 🔥 Practice Tasks Solution ✅ 1. Show all employees with salary > 30k SELECT * FROM employees WHERE salary > 30000; ✅ 2. Show employees from IT department SELECT * FROM employees WHERE department = 'IT'; ✅ 3. Show employees with salary between 40k–80k SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000; • 👉 Alternative: SELECT * FROM employees WHERE salary >= 40000 AND salary <= 80000; ✅ 4. Display only names of HR employees SELECT name FROM employees WHERE department = 'HR'; ✅ 5. Combine conditions using AND / OR SELECT * FROM employees WHERE department = 'IT' AND salary > 50000; • 👉 OR example: SELECT * FROM employees WHERE department = 'HR' OR salary < 30000; ⚡ Double Tap ❤️ For More

𝗙𝘂𝗹𝗹𝘀𝘁𝗮𝗰𝗸 𝗗𝗲𝘃𝗲𝗹𝗼𝗽𝗺𝗲𝗻𝘁 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗪𝗶𝘁𝗵 𝗚𝗲𝗻𝗔𝗜😍 Curriculum designed and taught by alumni from IITs & leading tech companies, with practical GenAI applications. * 2000+ Students Placed * 41LPA Highest Salary * 500+ Partner Companies - 7.4 LPA Avg Salary 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗡𝗼𝘄👇:- 🔹 Online :- https://pdlink.in/4hO7rWY 🔹 Hyderabad :- https://pdlink.in/4cJUWtx 🔹 Pune :-  https://pdlink.in/3YA32zi 🔹 Noida :-  https://linkpd.in/NoidaFSD Hurry Up 🏃‍♂️! Limited seats are available.

𝗔𝗜/𝗠𝗟 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗣𝗿𝗼𝗴𝗿𝗮𝗺 𝗕𝘆  𝗩𝗶𝘀𝗵𝗹𝗲𝘀𝗮𝗻 𝗶-𝗛𝘂𝗯, 𝗜𝗜𝗧 𝗣𝗮𝘁𝗻𝗮 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻😍 Freshers are getting paid 10 - 15 Lakhs by learning AI & ML skill Upgrade your career with a beginner-friendly AI/ML certification. 👉Open for all. No Coding Background Required 💻 Learn AI/ML from Scratch 🎓 Build real world Projects for job ready portfolio  🔥Deadline :- 19th April     𝗔𝗽𝗽𝗹𝘆 𝗡𝗼𝘄👇 :-  https://pdlink.in/41ZttiU . Get Placement Assistance With 5000+ Companies

🔥 Thanks for the amazing response on SQL Roadmap Let’s start with the first topic of the SQL Roadmap: ✅ What is SQL & How Databases Work 🧠 What is SQL? SQL (Structured Query Language) is used to communicate with databases. 👉 In simple words: SQL helps you store, retrieve, update, and delete data. Think like this 👇 Excel → You manually filter data SQL → You write a query → Data comes instantly ⚡ 🗃 What is a Database? A database is a place where data is stored in an organized way. Example: Student records, Employee data, Orders from an e-commerce website 📊 Types of Databases 1️⃣ Relational Database (RDBMS) - Data stored in tables (rows & columns) - Uses SQL - Example: MySQL, PostgreSQL 2️⃣ Non-Relational Database (NoSQL) - Data stored as JSON, documents, key-value - Flexible structure - Example: MongoDB 🧩 Key Terms You Must Know - Table → Like Excel sheet - Row → One record (one entry) - Column → One field (like name, age) - Primary Key → Unique ID (no duplicates) Example Table: id name salary 1 Amit 50000 2 Ravi 60000 ⚙️ How SQL Works (Simple Flow) 1️⃣ You write a query 2️⃣ Database processes it 3️⃣ Result is returned Example: SELECT * FROM employees; 👉 This means: “Give me all data from employees table” 💡 Real-Life Example Imagine Swiggy/Zomato 🍔 When you search “Pizza”: 👉 SQL runs in background 👉 Fetches restaurants with pizza 👉 Shows results instantly 🎯 Your Task Today ✔ Install MySQL Workbench or PostgreSQL ✔ Understand tables, rows, columns ✔ Run your first query (SELECT *) ✔ Explore any sample database 🔥 Pro Tip Don’t just read → Try everything practically SQL is 90% practice, 10% theory Double Tap ❤️ For More

🔢 16) How do you merge customer and orders dataframes on customer_id? 👉 Answer: # Keep all customers (even no orders) merged = pd.merge(customers, orders, on='customer_id', how='left') LEFT JOIN = Industry standard for customer analytics! 📉 17) What are 5 must-have KPIs for an e-commerce dashboard? 👉 Answer: 1. Revenue (vs target) 2. AOV 3. Conversion Rate 4. Cart Abandonment 5. Customer Acquisition Cost. Trend + Target + YoY always! ⚙️ 18) Your SQL query is running slow. How do you optimize? 👉 Answer: Top 5 fixes: 1. Indexes on WHERE/JOIN columns 2. EXPLAIN query plan 3. Avoid SELECT * 4. Limit subqueries 5. Aggregate at source. 🧠 19) Tell me about a time your data analysis failed. What happened? 👉 Answer: Situation: Dashboard showed wrong trends. Problem: Timezone mismatch in sales data. Fix: Added CONVERT_TZ() in SQL + data validation layer. Result: 100% accuracy, saved stakeholder trust. 💬 20) Do you have any questions for us? 👉 Answer: 1. What are the top 3 metrics leadership cares about? 2. What's your biggest data challenge? 3. How do you measure success in this role after 90 days? Double Tap ❤️ For More!

💼 Top 20 Frequently Asked Data Analyst Interview Questions 🧠 1) Can you walk me through the tools you use for data analysis? 👉 Answer: Absolutely! For data extraction I use SQL to query databases like MySQL and PostgreSQL. For cleaning and analysis, Python with pandas and NumPy is my go-to. Excel for quick pivots and Power BI/Tableau for interactive dashboards. I pick the right tool based on data size and stakeholder needs. 🎯 2) Write a SQL query to find the 2nd highest salary from employees table. 👉 Answer: SELECT MAX(salary) as second_highest FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); Follow-up: Or using window functions: DENSE_RANK() OVER (ORDER BY salary DESC) 📊 3) Explain INNER JOIN vs LEFT JOIN with a business example. 👉 Answer: INNER JOIN gives only matching records. LEFT JOIN gives all from left table + matches from right. Example: Customer orders analysis - LEFT JOIN keeps customers with zero orders to see churn patterns. 🔍 4) How would you handle missing values in a sales dataset? 👉 Answer: Step 1: df.isnull().sum() to assess impact. Step 2: For numbers - impute median (df.fillna(df.median())). For categories - mode. Step 3: Flag imputed values for transparency. Never drop >5% without business justification. 🧩 5) What's pandas groupby() and write an example? 👉 Answer: # Sales by region + month df.groupby(['region', 'month'])['revenue'].agg({ 'mean': 'mean', 'total': 'sum', 'records': 'count' }).round(2) Split -> Apply -> Combine pattern! 📈 6) When would you normalize vs denormalize a database? 👉 Answer: Normalize for transactional systems (OLTP) to save storage. Denormalize for analytics (OLAP) for faster queries. Example: Star schema with fact/dimension tables. 🔢 7) VLOOKUP vs INDEX+MATCH - which is better and why? 👉 Answer: INDEX+MATCH wins! VLOOKUP breaks if columns shift and only looks right. =INDEX(sales_range, MATCH(A2, id_range, 0)) Dynamic, safer, 2-way lookup. 📉 8) Difference between COUNT() vs COUNT(column_name)? 👉 Answer: COUNT(): Total rows including NULLs. COUNT(column): Non-null values only. Use COUNT() for total records, COUNT(sales) to exclude null sales. ⚙️ 9) How do you identify and remove duplicates in pandas? 👉 Answer: # Find duplicates dupe_count = df.duplicated(subset=['email']).sum() print(f"Found {dupe_count} duplicates") # Remove (keep first) df_clean = df.drop_duplicates(subset=['email'], keep='first') Always check business logic first! 🧠 10) Name 4 SQL aggregate functions with a practical example. 👉 Answer: SELECT dept, COUNT() as headcount, AVG(salary) as avg_salary, MAX(salary) as top_earner, SUM(salary) as payroll FROM employees GROUP BY dept; 📊 11) Sales dropped 20% last quarter. Walk me through your analysis. 👉 Answer: Framework: 1️⃣ Segment - Product/Category/Region/Customer 2️⃣ Trends - YoY, MoM, seasonality 3️⃣ Funnel - Where drop occurs 4️⃣ External - Competitor pricing, marketing Dashboard: Drill-down + alerts for anomalies. 🎯 12) What's the difference between Data Analyst and Data Scientist? 👉 Answer: DA: SQL/Excel/Dashboards = 'What happened?' DS: ML/Python/R = 'What will happen?' Analogy: DA = Rearview mirror, DS = Crystal ball. Most value from clean DA first! 🔍 13) Write a SQL window function to rank salaries by department. 👉 Answer: SELECT name, dept, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as dept_rank FROM employees; 🧩 14) How do you create a pivot table showing sales by region/month? 👉 Answer: Excel: Insert -> PivotTable -> Rows: Region -> Columns: Month -> Values: Sum of Sales -> Slicers for filters. Power BI: Drag-drop + matrix visual. 📈 15) Explain correlation vs causation with an example. 👉 Answer: Classic: Ice cream sales correlate with drownings (both peak summer)