Data Analytics

Real-world SQL Questions with Answers 🔥 Let's dive into some real-world SQL questions with a mini dataset. 📊 Dataset: employees id name department salary manager_id 1 Aditi HR 30000 5 2 Rahul IT 50000 6 3 Neha IT 60000 6 4 Aman Sales 40000 7 5 Kiran HR 70000 NULL 6 Mohit IT 80000 NULL 7 Suresh Sales 65000 NULL 8 Pooja HR 30000 5 1. Find average salary per department SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department; 2. Find employees earning above department average SELECT name, department, salary FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department = e.department ); 3. Find highest salary in each department SELECT department, MAX(salary) AS max_salary FROM employees GROUP BY department; 4. Find employees who earn more than their manager SELECT e.name FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.salary > m.salary; 5. Count employees in each department SELECT department, COUNT(*) AS total_employees FROM employees GROUP BY department; 6. Find departments with more than 2 employees SELECT department, COUNT(*) AS total FROM employees GROUP BY department HAVING COUNT(*) > 2; 7. Find second highest salary SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); 8. Find employees without managers SELECT name FROM employees WHERE manager_id IS NULL; 9. Rank employees by salary SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees; 10. Find duplicate salaries SELECT salary, COUNT(*) FROM employees GROUP BY salary HAVING COUNT(*) > 1; 11. Top 2 highest salaries SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 2; Double Tap ❤️ For More

Scenario based Interview Questions & Answers for Data Analyst 1. Scenario: You are working on a SQL database that stores customer information. The database has a table called "Orders" that contains order details. Your task is to write a SQL query to retrieve the total number of orders placed by each customer. Question: - Write a SQL query to find the total number of orders placed by each customer. Expected Answer: SELECT CustomerID, COUNT(*) AS TotalOrders FROM Orders GROUP BY CustomerID; 2. Scenario: You are working on a SQL database that stores employee information. The database has a table called "Employees" that contains employee details. Your task is to write a SQL query to retrieve the names of all employees who have been with the company for more than 5 years. Question: - Write a SQL query to find the names of employees who have been with the company for more than 5 years. Expected Answer: SELECT Name FROM Employees WHERE DATEDIFF(year, HireDate, GETDATE()) > 5; Power BI Scenario-Based Questions 1. Scenario: You have been given a dataset in Power BI that contains sales data for a company. Your task is to create a report that shows the total sales by product category and region. Expected Answer: - Load the dataset into Power BI. - Create relationships if necessary. - Use the "Fields" pane to select the necessary fields (Product Category, Region, Sales). - Drag these fields into the "Values" area of a new visualization (e.g., a table or bar chart). - Use the "Filters" pane to filter data as needed. - Format the visualization to enhance clarity and readability. 2. Scenario: You have been asked to create a Power BI dashboard that displays real-time stock prices for a set of companies. The stock prices are available through an API. Expected Answer: - Use Power BI Desktop to connect to the API. - Go to "Get Data" > "Web" and enter the API URL. - Configure the data refresh settings to ensure real-time updates (e.g., setting up a scheduled refresh or using DirectQuery if supported). - Create visualizations using the imported data. - Publish the report to the Power BI service and set up a data gateway if needed for continuous refresh. 3. Scenario: You have been given a Power BI report that contains multiple visualizations. The report is taking a long time to load and is impacting the performance of the application. Expected Answer: - Analyze the current performance using Performance Analyzer. - Optimize data model by reducing the number of columns and rows, and removing unnecessary calculations. - Use aggregated tables to pre-compute results. - Simplify DAX calculations. - Optimize visualizations by reducing the number of visuals per page and avoiding complex custom visuals. - Ensure proper indexing on the data source. Free SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v Like if you need more similar content Hope it helps :)

🗃 Introduction to Data Analysis This is the foundation of your entire data analyst journey. If you get this right, everything else becomes easier. 🎯 1. What Does a Data Analyst Actually Do? A Data Analyst turns raw data into useful insights that help businesses make decisions. 👉 Simple Flow: Raw Data → Clean → Analyze → Visualize → Tell Story → Decision 🔍 Real Example: Imagine an e-commerce company: Data Analyst checks: Why sales dropped last month? Finds: Mobile users faced checkout issues Suggests: Fix mobile UX Result: Sales improve 👉 This is the real job — not just coding. 🧭 2. Career Paths in Data Analytics You don’t have just one path. You can specialize based on your interest: 🔹 Business Analyst Focus: Business decisions Tools: Excel, Power BI Work: Reports, KPIs, dashboards 🔹 Product Analyst Focus: User behavior (apps/websites) Tools: SQL, Python Work: A/B testing, funnels 🔹 Data Analyst (Core) Focus: Data querying reporting Tools: SQL, Excel, Tableau Work: Data cleaning, dashboards 🔹 Analytics Engineer (Advanced) Focus: Data pipelines + modeling Tools: SQL, dbt Work: Clean data for analysts 🧠 3. Key Skills You MUST Build 🟢1. SQL (Most Important Skill) Used to extract data from databases You’ll write queries like: SELECT, WHERE, GROUP BY, JOIN 🟡2. Excel (Underrated but Powerful) • Quick analysis tool • Used everywhere in companies Key things: Pivot Tables Lookups (XLOOKUP) Dashboards 🔵3. Data Storytelling This is what separates average vs high-paid analysts 👉 Anyone can analyze data 👉 Few can explain it simply Example: Instead of saying: > “Sales dropped by 20%” Say: “Sales dropped by 20% mainly due to mobile checkout issues, fixing this can recover revenue quickly.” 🧰 4. Tools Ecosystem (What You’ll Use) 🧪Notebooks Practice Google Colab 👉 Run Python in browser (no setup needed) 📊Visualization Tools Tableau Public 👉 Create dashboards portfolio Microsoft Power BI 👉 Industry-level reporting tool 🧮Data Sources (Where data lives) • Databases (MySQL, PostgreSQL) • Excel files • APIs ⚡ 5. Types of Data You’ll Work With 📄Structured Data Tables (rows columns) Example: Excel, SQL tables 🧾Unstructured Data Text, images, videos Example: Reviews, tweets 📊Semi-structured JSON, XML Used in APIs 🔁 6. Typical Data Analyst Workflow Step-by-step: 1. Understand the problem 2. Collect data 3. Clean data (most time spent here!) 4. Analyze 5. Visualize 6. Communicate insights 👉 70% of work = cleaning + understanding data 👉 Only 30% = actual analysis 🚨 7. Beginner Mistakes to Avoid ❌ Learning too many tools at once ❌ Ignoring SQL ❌ Only watching tutorials (no practice) ❌ Not building projects 💡 Reality Check 👉 Data Analysis is NOT about coding 👉 It’s about thinking, problem-solving, and communication Double Tap ❤️ For More

🔰Data Analyst Roadmap 2026 ├── 🗃Introduction to Data Analysis │ ├── Role overview & career paths │ ├── Key skills: SQL, Excel, storytelling │ └── Tools ecosystem (Colab, Tableau Public) ├── 📊Excel Mastery (Formulas, Pivots) │ ├── VLOOKUP, INDEX-MATCH, XLOOKUP │ ├── PivotTables, slicers, Power Query │ ├── Charts & conditional formatting │ └── ETL basics in spreadsheets ├── 🔍SQL for Analytics (Joins, Aggregates) │ ├── Advanced SELECT with WHERE, GROUP BY │ ├── JOINS (INNER, LEFT, window functions) │ └── Performance: indexes, EXPLAIN plans ├── 📈Visualization Principles (Charts, Dashboards) │ ├── Chart types (bar, line, heatmaps) │ ├── Design rules (avoid chart junk) │ └── Color theory & accessibility ├── 🐍Python Basics (Pandas, NumPy) │ ├── DataFrames: load, clean, merge │ ├── Grouping, pivoting, NumPy arrays │ └── Jupyter notebooks & stats intro ├── 🔢Statistics Fundamentals (Averages, Tests) │ ├── Descriptive (mean, median, distributions) │ ├── Hypothesis testing (t-tests, chi-square) │ └── A/B testing & confidence intervals ├── 🛠Tableau/Power BI Essentials │ ├── Tableau: calculated fields, LOD │ ├── Power BI: DAX, data modeling │ └── Interactive dashboards & storytelling ├── 🤖AI Tools for Insights (Prompts, AutoML) │ ├── Prompt engineering for SQL/viz │ ├── Tableau Einstein, Power BI Copilot │ └── AutoML basics (no-code modeling) ├── ☁️Cloud Platforms (BigQuery Basics) │ ├── BigQuery SQL & massive datasets │ ├── AWS QuickSight, Snowflake intro │ └── Free tier cost optimization ├── 📊Data Storytelling Frameworks │ ├── Pyramid Principle for reports │ ├── KPI dashboards & executive summaries │ └── Narrative structure (context-insight-action) ├── 🔗ETL Pipelines Intro (dbt, Airflow) │ ├── Data transformation with dbt │ ├── Orchestration (Airflow basics) │ └── No-code: Zapier automation ├── 💼Portfolio & Interview Prep │ ├── 3-5 projects (sales, churn analysis) │ ├── Kaggle datasets & GitHub portfolio │ └── STAR method, mock interviews └── 🧪Real-world Challenges (Kaggle, Cases) ├── E-commerce churn prediction ├── Marketing ROI analysis ├── Supply chain optimization └── LeetCode SQL, case studies Like for detailed explanation ❤️

🎯📊 DATA ANALYST MOCK INTERVIEW (WITH ANSWERS) 🧠1️⃣ Tell me about yourself ✅ Sample Answer: “I have around 3 years of experience working with data. My core skills include SQL, Excel, and Power BI. I regularly work with data cleaning, transformation, and building dashboards to generate business insights. Recently, I’ve also been strengthening my Python skills for data analysis. I enjoy solving business problems using data and presenting insights in a simple and actionable way.” 📊2️⃣ What is the difference between WHERE and HAVING? ✅ Answer: WHERE filters rows before aggregation HAVING filters after aggregation Example: SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5; 🔗3️⃣ Explain different types of JOINs ✅ Answer: INNER JOIN → only matching records LEFT JOIN → all left + matching right RIGHT JOIN → all right + matching left FULL JOIN → all records from both 👉 In analytics, LEFT JOIN is most used. 🧠4️⃣ How do you find duplicate records in SQL? ✅ Answer: SELECT column, COUNT(*) FROM table GROUP BY column HAVING COUNT(*) > 1; 👉 Used for data cleaning. 📈5️⃣ What are window functions? ✅ Answer: “Window functions perform calculations across rows without reducing the number of rows. They are used for ranking, running totals, and comparisons.” Example: SELECT salary, RANK() OVER(ORDER BY salary DESC) FROM employees; 📊6️⃣ How do you handle missing data? ✅ Answer: Remove rows (if small impact) Replace with mean/median Use default values Use interpolation (advanced) 👉 Depends on business context. 📉7️⃣ What is the difference between COUNT(_) and COUNT(column)? ✅ Answer: COUNT(*) → counts all rows COUNT(column) → ignores NULL values 📊8️⃣ What is a KPI? Give example ✅ Answer: “KPI (Key Performance Indicator) is a measurable value used to track performance.” Examples: Revenue growth, Conversion rate, Customer retention 🧠9️⃣ How would you find the 2nd highest salary? ✅ Answer: SELECT MAX(salary) FROM employees WHERE salary < ( SELECT MAX(salary) FROM employees ); 📊🔟 Explain your dashboard project ✅ Strong Answer: “I created a sales dashboard in Power BI where I analyzed revenue trends, top-performing products, and regional performance. I used DAX for calculations and added filters for better interactivity. This helped stakeholders identify key areas for growth.” 🔥1️⃣1️⃣ What is normalization? ✅ Answer: “Normalization is the process of organizing data to reduce redundancy and improve data integrity.” 📊1️⃣2️⃣ Difference between INNER JOIN and LEFT JOIN? ✅ Answer: INNER JOIN → only matching data LEFT JOIN → keeps all left table data 👉 LEFT JOIN is preferred in analytics. 🧠1️⃣3️⃣ What is a CTE? ✅ Answer: “A CTE (Common Table Expression) is a temporary result set defined using WITH clause to improve readability.” 📈1️⃣4️⃣ How do you explain insights to non-technical people? ✅ Answer: “I focus on storytelling. Instead of technical terms, I explain insights in simple business language with visuals and examples.” 📊1️⃣5️⃣ What tools have you used? ✅ Answer: SQL, Excel, Power BI, Python (basic/advanced depending on you) 💼1️⃣6️⃣ Behavioral Question: Tell me about a challenge ✅ Answer: “While working on a dataset, I found inconsistencies in data. I cleaned and standardized it using SQL and Excel, ensuring accurate analysis. This improved the dashboard reliability.” Double Tap ♥️ For More

✅SQL Real-world Interview Questions with Answers🖥️ 📊 TABLE: employees id | name | department | salary 1 | Rahul | IT | 50000 2 | Priya | IT | 70000 3 | Amit | HR | 60000 4 | Neha | HR | 70000 5 | Karan | IT | 80000 6 | Simran | HR | 60000 🎯1️⃣ Find the 2nd highest salary 🧠 Logic: Get highest salary Then find max salary below that ✅ Query: SELECT MAX(salary) FROM employees WHERE salary < ( SELECT MAX(salary) FROM employees ); 🎯2️⃣ Find employees earning more than average salary 🧠 Logic: Calculate overall average salary Compare each employee ✅ Query: SELECT name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ); 🎯3️⃣ Find highest salary in each department 🧠 Logic: Group by department Use MAX ✅ Query: SELECT department, MAX(salary) AS highest_salary FROM employees GROUP BY department; 🎯4️⃣ Find top 2 highest salaries in each department 🧠 Logic: Use ROW_NUMBER Partition by department Filter top 2 ✅ Query: SELECT * FROM ( SELECT name, department, salary, ROW_NUMBER() OVER( PARTITION BY department ORDER BY salary DESC ) r FROM employees ) t WHERE r <= 2; 🎯5️⃣ Find employees earning more than their department average 🧠 Logic: Use correlated subquery Compare with department avg ✅ Query: SELECT e.name, e.department, e.salary FROM employees e WHERE e.salary > ( SELECT AVG(salary) FROM employees WHERE department = e.department ); ⭐ What Interviewer Checks Here These 5 questions test: ✔ Subqueries ✔ GROUP BY ✔ Window functions ✔ Correlated queries ✔ Real business logic SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v Double Tap ♥️ For More

Quick Python Cheat Sheet for Beginners 🐍✍️ Python is widely used for data analysis, automation, and AI—perfect for beginners starting their coding journey. Aggregation Functions 📊 • sum(list) → Adds all values 👉 sum([1,2,3]) = 6 • len(list) → Counts total elements 👉 len([1,2,3]) = 3 • max(list) → Highest value 👉 max([4,7,2]) = 7 • min(list) → Lowest value 👉 min([4,7,2]) = 2 • sum(list)/len(list) → Average 👉 sum([10,20])/2 = 15 Lookup / Searching 🔍 • in → Check existence 👉 5 in [1,2,5] = True • list.index(value) → Position of value 👉 [10,20,30].index(20) = 1 • Dictionary lookup 👉 data = {"name": "John", "age": 25} data["name"] # John Logical Operations 🧠 • if condition: → Decision making 👉 if x > 10: print("High") else: print("Low") • and → All conditions true • or → Any condition true • not → Reverse condition Text (String) Functions 🔤 • len(text) → Length 👉 len("hello") = 5 • text.lower() → Lowercase • text.upper() → Uppercase • text.strip() → Remove spaces 👉 " hi ".strip() = "hi" • text.replace(old, new) 👉 "hi".replace("h","H") = "Hi" • String concatenation 👉 "Hello " + "World" Date Time Functions 📅 • from datetime import datetime • datetime.now() → Current date time • Extract values: now = datetime.now() now.year now.month now.day Math Functions ➗ • import math • math.sqrt(x) → Square root • math.ceil(x) → Round up • math.floor(x) → Round down • abs(x) → Absolute value Conditional Aggregation (Like Excel SUMIF) ⚡ • Using list comprehension nums = [10, 20, 30, 40] sum(x for x in nums if x > 20) # 70 • Count condition len([x for x in nums if x > 20]) # 2 Pro Tip for Data Analysts 💡 👉 For real-world work, use libraries: pandas & numpy Example: import pandas as pd df["salary"].mean() Python Resources: https://whatsapp.com/channel/0029VaiM08SDuMRaGKd9Wv0L Double Tap ♥️ For More