Data Analytics

𝟲 𝗙𝗥𝗘𝗘 𝗬𝗼𝘂𝗧𝘂𝗯𝗲 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝘁𝗼 𝗞𝗶𝗰𝗸𝘀𝘁𝗮𝗿𝘁 𝗬𝗼𝘂𝗿 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗮𝗿𝗲𝗲𝗿!😍 Want to break into Data Analytics but don’t know where to start? These 6 FREE courses cover everything—from Excel, SQL, Python, and Power BI to Business Math & Statistics and Portfolio Projects! 📊 𝐋𝐢𝐧𝐤👇:- https://pdlink.in/4kMSztw 📌 Save this now and start learning today!

Window Functions in SQL Window functions perform calculations across a set of table rows related to the current row. Unlike aggregation functions, they do not collapse rows but retain all rows while providing additional insights. 1️⃣ Common Window Functions ROW_NUMBER() → Assigns a unique rank to each row within a partition RANK() → Similar to ROW_NUMBER(), but gives same rank to duplicates DENSE_RANK() → Similar to RANK(), but without skipping numbers NTILE(n) → Divides the result into n equal parts SUM() OVER() → Running total (cumulative sum) AVG() OVER() → Moving average LAG() → Gets the previous row’s value LEAD() → Gets the next row’s value 2️⃣ Basic Syntax SELECT column1, column2, window_function() OVER (PARTITION BY column ORDER BY column) AS alias FROM table_name;

✔ PARTITION BY groups rows before applying the function
✔ ORDER BY determines the ranking or sequence

3️⃣ Using ROW_NUMBER()

🔹 Assign a unique row number to each employee based on salary (highest first)

SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num FROM employees;

✔ Each employee gets a unique row number within their department. 4️⃣ Using RANK() and DENSE_RANK() 🔹 Rank employees by salary within each department

SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank FROM employees;

✔ RANK() skips numbers when there’s a tie ✔ DENSE_RANK() does not skip numbers 5️⃣ Using NTILE() for Distribution 🔹 Divide employees into 4 salary groups per department

SELECT name, department, salary, NTILE(4) OVER (PARTITION BY department ORDER BY salary DESC) AS salary_quartile FROM employees;

✔ Useful for dividing salaries into percentiles (e.g., top 25%, bottom 25%) 6️⃣ Running Total with SUM() OVER() 🔹 Calculate cumulative salary per department

SELECT name, department, salary, SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS running_total FROM employees;

✔ Useful for tracking cumulative totals 7️⃣ Using LAG() and LEAD() 🔹 Compare an employee’s salary with the previous and next employee’s salary

SELECT name, department, salary, LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS previous_salary, LEAD(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS next_salary FROM employees; 

✔ LAG() gets the previous row’s value ✔ LEAD() gets the next row’s value Mini Task for You: Write an SQL query to assign a unique rank to employees based on their salary within each department using RANK().

Window Functions in SQL Window functions perform calculations across a set of table rows related to the current row. Unlike aggregation functions, they do not collapse rows but retain all rows while providing additional insights. 1️⃣ Common Window Functions ROW_NUMBER() → Assigns a unique rank to each row within a partition RANK() → Similar to ROW_NUMBER(), but gives same rank to duplicates DENSE_RANK() → Similar to RANK(), but without skipping numbers NTILE(n) → Divides the result into n equal parts SUM() OVER() → Running total (cumulative sum) AVG() OVER() → Moving average LAG() → Gets the previous row’s value LEAD() → Gets the next row’s value 2️⃣ Basic Syntax SELECT column1, column2, window_function() OVER (PARTITION BY column ORDER BY column) AS alias FROM table_name;

✔ PARTITION BY groups rows before applying the function
✔ ORDER BY determines the ranking or sequence

3️⃣ Using ROW_NUMBER()

🔹 Assign a unique row number to each employee based on salary (highest first)

SQL SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num FROM employees;

✔ Each employee gets a unique row number within their department.

4️⃣ Using RANK() and DENSE_RANK()

🔹 Rank employees by salary within each department

SQL SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank FROM employees;

✔ RANK() skips numbers when there’s a tie
✔ DENSE_RANK() does not skip numbers

5️⃣ Using NTILE() for Distribution

🔹 Divide employees into 4 salary groups per department

SQL SELECT name, department, salary, NTILE(4) OVER (PARTITION BY department ORDER BY salary DESC) AS salary_quartile FROM employees;

✔ Useful for dividing salaries into percentiles (e.g., top 25%, bottom 25%)

6️⃣ Running Total with SUM() OVER()

🔹 Calculate cumulative salary per department

SQL SELECT name, department, salary, SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS running_total FROM employees;

✔ Useful for tracking cumulative totals

7️⃣ Using LAG() and LEAD()

🔹 Compare an employee’s salary with the previous and next employee’s salary

SQL SELECT name, department, salary, LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS previous_salary, LEAD(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS next_salary FROM employees; ` ✔ LAG() gets the previous row’s value ✔ LEAD() gets the next row’s value Mini Task for You: Write an SQL query to assign a unique rank to employees based on their salary within each department using RANK().

𝗠𝗮𝘀𝘁𝗲𝗿 𝗦𝗤𝗟 𝗘𝗳𝗳𝗼𝗿𝘁𝗹𝗲𝘀𝘀𝗹𝘆 𝘄𝗶𝘁𝗵 𝗧𝗵𝗶𝘀 𝗖𝗵𝗲𝗮𝘁 𝗦𝗵𝗲𝗲𝘁!🔥 Struggling with SQL basics?👋 This cheat sheet has everything you need! 🎯 𝐋𝐢𝐧𝐤👇:- https://pdlink.in/4hB8KYa 🚀 No more searching for syntax—just bookmark and use it anytime!

Common Table Expressions (CTEs) in SQL 👇👇 CTEs (WITH statement) help write cleaner and more readable SQL queries. They are like temporary result sets that can be referenced within the main query. 1️⃣ Basic Syntax of CTE WITH cte_name AS ( SELECT column1, column2 FROM table_name WHERE condition ) SELECT * FROM cte_name; ✔ The CTE cte_name is defined and then used in the main SELECT query. 2️⃣ Simple CTE Example 🔹 Find employees earning more than $70,000

WITH high_earners AS ( SELECT name, salary, department_id FROM employees WHERE salary > 70000 ) SELECT * FROM high_earners; 

✔ The CTE high_earners filters employees with high salaries before selecting all columns from it. 3️⃣ CTE with Aggregation 🔹 Find departments where the average salary is above $80,000

WITH department_salary AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) SELECT department_id, avg_salary FROM department_salary WHERE avg_salary > 80000; 

✔ The CTE department_salary calculates the average salary per department and filters out low-paying ones. 4️⃣ CTE for Recursive Queries (Hierarchy Example) 🔹 Find an employee hierarchy (who reports to whom)

WITH RECURSIVE employee_hierarchy AS ( SELECT employee_id, name, manager_id FROM employees WHERE manager_id IS NULL -- Start with top-level manager UNION ALL SELECT e.employee_id, e.name, e.manager_id FROM employees e INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id ) SELECT * FROM employee_hierarchy; 

✔ This recursive CTE finds an employee hierarchy starting from the top-level manager. 5️⃣ Why Use CTEs Instead of Subqueries? ✅ Better Readability – Makes complex queries easier to understand ✅ Reusability – Can be referenced multiple times in the main query ✅ Performance – Some databases optimize CTEs better than nested subqueries Mini Task for You: Write an SQL query using a CTE to find departments with more than 5 employees. You can find free SQL Resources here 👇👇 https://t.me/mysqldata Like this post if you want me to continue covering all the topics! ❤️ Share with credits: https://t.me/sqlspecialist Hope it helps :) #sql

𝗪𝗮𝗻𝘁 𝘁𝗼 𝗺𝗮𝘀𝘁𝗲𝗿 𝗘𝘅𝗰𝗲𝗹 𝗶𝗻 𝗷𝘂𝘀𝘁 𝟳 𝗱𝗮𝘆𝘀? 📊 Here's a structured roadmap to help you go from beginner to pro in a week! Whether you're learning formulas, functions, or data visualization, this guide covers everything step by step. 𝐋𝐢𝐧𝐤👇 :- https://pdlink.in/43lzybE All The Best 💥

JOINS in SQL Joins allow you to combine data from multiple tables based on related columns. They are essential for working with relational databases. 1️⃣ Types of JOINS INNER JOIN → Returns only matching rows from both tables LEFT JOIN → Returns all rows from the left table + matching rows from the right table RIGHT JOIN → Returns all rows from the right table + matching rows from the left table FULL JOIN → Returns all rows from both tables (matching + non-matching) SELF JOIN → Joins a table with itself CROSS JOIN → Returns all possible combinations of rows 2️⃣ INNER JOIN (Most Common Join) 🔹 Find employees and their department names

SELECT employees.name, employees.salary, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id; 

✔ Returns only employees who have a matching department. 3️⃣ LEFT JOIN (Includes Unmatched Rows from Left Table) 🔹 Find all employees, including those without a department

SELECT employees.name, employees.salary, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id; 

✔ Includes employees even if they don’t have a department (NULL if no match). 4️⃣ RIGHT JOIN (Includes Unmatched Rows from Right Table) 🔹 Find all departments, including those without employees

SELECT employees.name, employees.salary, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id; 

✔ Includes all departments, even if no employees are assigned. 5️⃣ FULL JOIN (Includes Unmatched Rows from Both Tables) 🔹 Get a complete list of employees and departments (matched + unmatched rows)

SELECT employees.name, employees.salary, departments.department_name FROM employees FULL JOIN departments ON employees.department_id = departments.department_id; 

✔ Includes all employees and departments even if there’s no match.

𝗙𝗥𝗘𝗘 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 𝘁𝗼 𝗟𝗲𝗮𝗿𝗻 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀! 📊🚀 Want to master data analytics? Here are top free courses, books, and certifications to help you get started with Power BI, Tableau, Python, and Excel. 𝐋𝐢𝐧𝐤👇 https://pdlink.in/41Fx3PW All The Best 💥

GROUP BY & HAVING in SQL The GROUP BY clause is used to group rows that have the same values in specified columns. It’s commonly used with aggregation functions (SUM(), AVG(), COUNT(), etc.) to perform calculations on each group. The HAVING clause filters groups after aggregation, similar to how WHERE filters individual rows. 1️⃣ Basic GROUP BY Usage 🔹 Find the total number of employees in each department

SELECT department, COUNT(*) FROM employees GROUP BY department; 

This groups employees by department and counts the number of employees in each department. 🔹 Find the total salary per department

SELECT department, SUM(salary) FROM employees GROUP BY department; 

2️⃣ GROUP BY with Multiple Columns You can group by multiple columns to analyze data more deeply. 🔹 Find the total salary for each job title within each department

SELECT department, job_title, SUM(salary) FROM employees GROUP BY department, job_title;

3️⃣ Using HAVING to Filter Groups Unlike WHERE, which filters before aggregation, HAVING filters after aggregation. 🔹 Find departments with more than 5 employees

SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) > 5; 

🔹 Find departments where the total salary is greater than $500,000

SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department HAVING SUM(salary) > 500000;

🔹 Find job titles where the average salary is above $70,000

SELECT job_title, AVG(salary) AS avg_salary FROM employees GROUP BY job_title HAVING AVG(salary) > 70000; 

4️⃣ GROUP BY with ORDER BY To sort grouped results, use ORDER BY. 🔹 Find the total salary per department, sorted in descending order

SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department ORDER BY total_salary DESC;

Mini Task for You: Write an SQL query to find departments where the average salary is more than $80,000. Let me know when you’re ready to move to the next topic! 🚀

𝗙𝗿𝗲𝗲 𝗧𝗖𝗦 𝗶𝗢𝗡 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝘁𝗼 𝗨𝗽𝗴𝗿𝗮𝗱𝗲 𝗬𝗼𝘂𝗿 𝗦𝗸𝗶𝗹𝗹𝘀!😍 Looking to boost your career with free online courses? 🎓 TCS iON, a leading digital learning platform from Tata Consultancy Services (TCS), offers a variety of free courses across multiple domains!📊 𝐋𝐢𝐧𝐤👇:- https://pdlink.in/3Dc0K1S Start learning today and take your career to the next level!✅️

SQL Interview Questions with detailed answers 2️⃣0️⃣ What is the use of CASE statements in SQL? The CASE statement in SQL is used for conditional logic within queries, similar to an IF-ELSE statement in programming. It allows you to return different values based on conditions. Use Cases of CASE Statement: 1️⃣ Creating custom categories based on conditions. 2️⃣ Handling NULL values with default replacements. 3️⃣ Applying conditional aggregations in reports. Example 1: Categorizing Sales Amount

SELECT order_id, customer_id, sales_amount, CASE WHEN sales_amount > 1000 THEN 'High' WHEN sales_amount BETWEEN 500 AND 1000 THEN 'Medium' ELSE 'Low' END AS sales_category FROM sales; 

✅ This classifies each sale as High, Medium, or Low based on sales_amount. Example 2: Handling NULL Values

SELECT employee_id, CASE WHEN department IS NULL THEN 'Not Assigned' ELSE department END AS department_status FROM employees; 

✅ This replaces NULL values in the department column with "Not Assigned". Example 3: Conditional Aggregation in Reports

SELECT SUM(CASE WHEN order_status = 'Completed' THEN total_amount ELSE 0 END) AS completed_sales, SUM(CASE WHEN order_status = 'Pending' THEN total_amount ELSE 0 END) AS pending_sales FROM orders; 

✅ This calculates total sales separately for "Completed" and "Pending" orders. Top 20 SQL Interview Questions React with ❤️ if you want similar Interview Series for other data analytics topics Share with credits: https://t.me/sqlspecialist Hope it helps :)

𝗧𝗼𝗽 𝟱 𝗙𝗿𝗲𝗲 𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝗬𝗼𝘂 𝗖𝗮𝗻 𝗘𝗻𝗿𝗼𝗹𝗹 𝗜𝗻 𝗧𝗼𝗱𝗮𝘆!😍 In today’s fast-paced tech industry, staying ahead requires continuous learning and upskilling✨️ Fortunately, 𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 is offering 𝗳𝗿𝗲𝗲 𝗰𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗰𝗼𝘂𝗿𝘀𝗲𝘀 that can help beginners and professionals enhance their 𝗲𝘅𝗽𝗲𝗿𝘁𝗶𝘀𝗲 𝗶𝗻 𝗱𝗮𝘁𝗮, 𝗔𝗜, 𝗦𝗤𝗟, 𝗮𝗻𝗱 𝗣𝗼𝘄𝗲𝗿 𝗕𝗜 without spending a dime!⬇️ 𝐋𝐢𝐧𝐤👇:- https://pdlink.in/3DwqJRt Start a career in tech, boost your resume, or improve your data skills✅️

Hey guys, Here is the list of best curated Telegram Channels for free education 👇👇 Free Courses with Certificate Web Development Free Resources Data Science & Machine Learning Programming Free Books Python Free Courses Python Interview Resources Ethical Hacking & Cyber Security English Speaking & Communication Stock Marketing & Investment Banking Coding Projects Jobs & Internship Opportunities Learn Digital Marketing Crack your coding Interviews Udemy Free Courses with Certificate Earn $10000 with ChatGPT Google Jobs Java Programming Free Resources Learn Blockchain & Crypto Data Analyst Jobs Artificial Intelligence Free access to all the Paid Channels 👇👇 https://t.me/addlist/4q2PYC0pH_VjZDk5 Do react with ♥️ if you need more content like this ENJOY LEARNING 👍👍

SQL Interview Questions with detailed answers 1️⃣9️⃣ How do you calculate the percentage of total sales for each category? To calculate the percentage of total sales for each category, we use SUM() and window functions or subqueries. Using Window Functions (Recommended for Modern SQL)

SELECT category_id, SUM(sales_amount) AS category_sales, (SUM(sales_amount) * 100.0) / SUM(SUM(sales_amount)) OVER () AS sales_percentage FROM sales GROUP BY category_id; 

Explanation: 1️⃣ SUM(sales_amount) OVER () calculates the total sales across all categories. 2️⃣ SUM(sales_amount) * 100.0 / total_sales computes the percentage for each category. 3️⃣ GROUP BY category_id ensures aggregation at the category level. Using a Subquery (Compatible with Older SQL Versions):

SELECT category_id, SUM(sales_amount) AS category_sales, (SUM(sales_amount) * 100.0) / (SELECT SUM(sales_amount) FROM sales) AS sales_percentage FROM sales GROUP BY category_id; 

This works the same way but calculates total sales in a subquery. Top 20 SQL Interview Questions Like this post if you want me to continue this SQL Interview Series♥️ Share with credits: https://t.me/sqlspecialist Hope it helps :)