𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: Write a query to get the names of all employees along with their department names. If an employee is not assigned to a department, still include them.

𝗠𝗲: Here’s my SQL solution using a LEFT JOIN:

SELECT e.name AS employee_name, d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

✔ Why it works:
– LEFT JOIN ensures all employees are shown, even those without departments.
– It joins the employees and departments tables on department_id.
– Clean, readable, and interview-ready!

🔎 Bonus Insight:
Always understand the difference between INNER JOIN, LEFT JOIN, and RIGHT JOIN. In real-world databases, missing data is common — use joins wisely to handle it.

💬 Tap ❤️ if this helped you!

✅ SQL Mini-Challenge! 🔍💻

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: List all employees and their managers. If an employee doesn’t have a manager, still include them.

𝗠𝗲: Using a self-join with LEFT JOIN:

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

✔ Why it works:
– LEFT JOIN ensures employees without managers are still included.
– Self-join allows referencing the same table for managers.
– Simple and clean solution for interviews.

🔎 Bonus Tip:
Always consider null values in joins; LEFT JOIN helps preserve the main table rows even if the related data is missing.

💬 Tap ❤️ if this helped you!

✅ SQL Scenario-Based Question & Answer 💻📊

Scenario:
You have two tables:

Employees

| emp_id | name  | dept_id | salary |
| ------ | ----- | ------- | ------ |
| 1      | John  | 10      | 5000   |
| 2      | Alice | 20      | 6000   |
| 3      | Bob   | 10      | 4500   |
| 4      | Mary  | NULL    | 7000   |

Departments

| dept_id | dept_name |
| ------- | --------- |
| 10      | Sales     |
| 20      | Marketing |
| 30      | HR        |

Question:
Write a query to display all employees with their department name. If an employee doesn't belong to any department, show "Not Assigned" instead.

Answer:

SELECT e.name AS employee_name,
       COALESCE(d.dept_name, 'Not Assigned') AS department_name
FROM Employees e
LEFT JOIN Departments d
ON e.dept_id = d.dept_id;

Explanation:
⦁ LEFT JOIN ensures all employees are included, even those without a matching dept_id (like Mary).
⦁ COALESCE picks the first non-NULL value: dept_name if available, otherwise "Not Assigned".
⦁ This handles NULLs gracefully while keeping the full employee list intact.

Result:

| employee_name | department_name |
| ------------- | --------------- |
| John          | Sales           |
| Alice         | Marketing       |
| Bob           | Sales           |
| Mary          | Not Assigned    |

💬 Double Tap ❤️ if this helped you!

✅ SQL Interview Challenge! 🧠💻

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: Retrieve all employees along with their department names. Only include employees who belong to a department.

𝗠𝗲: Using INNER JOIN:

SELECT e.name AS employee_name,
       d.dept_name AS department_name
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id;

✔ Why it works:
– INNER JOIN returns only rows with matching values in both tables based on the ON condition, excluding employees without a dept_id match (or vice versa).
– This ensures we get complete, valid pairs—perfect for reports focusing on assigned staff, and it's efficient for large datasets in 2025's analytics tools.

💬 Tap ❤️ if this helped you!

✅ SQL Interview Challenge! 📊🧠

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: Find the average salary of employees in each department.

𝗠𝗲: Using GROUP BY and AVG():
SELECT d.dept_name,
AVG(e.salary) AS avg_salary
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

✔ Why it works:
– AVG() computes the mean salary per group, handling decimals nicely.
– GROUP BY clusters rows by department name for department-level aggregates.
– INNER JOIN links employee salaries to department names only for matching records—add ORDER BY avg_salary DESC for ranked insights!

💬 Tap ❤️ for more!

✅ Interviewer: Count the number of employees in each department. 📊

Me: Using GROUP BY and COUNT():

SELECT d.dept_name,
       COUNT(e.id) AS employee_count
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

✔ Why it works: 
– COUNT() tallies employees per department by counting non-null IDs. 
– GROUP BY segments the results by department name for aggregated output. 
– INNER JOIN links employees to departments only where IDs match, avoiding nulls—add ORDER BY employee_count DESC to sort by largest teams first!

💬 Tap ❤️ if you're learning something new!

✅10 Most Useful SQL Interview Queries (with Examples) 💼

1️⃣ Find the second highest salary:

SELECT MAX(salary)  
FROM employees  
WHERE salary < (SELECT MAX(salary) FROM employees);

2️⃣ Count employees in each department:

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

3️⃣ Fetch duplicate emails:

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

4️⃣ Join orders with customer names:

SELECT c.name, o.order_date  
FROM customers c  
JOIN orders o ON c.id = o.customer_id;

5️⃣ Get top 3 highest salaries:

SELECT DISTINCT salary  
FROM employees  
ORDER BY salary DESC  
LIMIT 3;

6️⃣ Retrieve latest 5 logins:

SELECT * FROM logins  
ORDER BY login_time DESC  
LIMIT 5;

7️⃣ Employees with no manager:

SELECT name  
FROM employees  
WHERE manager_id IS NULL;

8️⃣ Search names starting with ‘S’:

SELECT * FROM employees  
WHERE name LIKE 'S%';

9️⃣ Total sales per month:

SELECT MONTH(order_date) AS month, SUM(amount)  
FROM sales  
GROUP BY MONTH(order_date);

🔟 Delete inactive users:

DELETE FROM users  
WHERE last_active < '2023-01-01';

✅ Tip: Master subqueries, joins, groupings & filters – they show up in nearly every interview!

💬 Tap ❤️ for more!

The program for the 10th AI Journey 2025 international conference has been unveiled: scientists, visionaries, and global AI practitioners will come together on one stage. Here, you will hear the voices of those who don't just believe in the future—they are creating it!

Speakers include visionaries Kai-Fu Lee and Chen Qufan, as well as dozens of global AI gurus from around the world!

On the first day of the conference, November 19, we will talk about how AI is already being used in various areas of life, helping to unlock human potential for the future and changing creative industries, and what impact it has on humans and on a sustainable future.

On November 20, we will focus on the role of AI in business and economic development and present technologies that will help businesses and developers be more effective by unlocking human potential.

On November 21, we will talk about how engineers and scientists are making scientific and technological breakthroughs and creating the future today!

Ride the wave with AI into the future!

Tune in to the AI Journey webcast on November 19-21.

✅ Advanced SQL Queries 🗄️💡

1️⃣ GROUP BY & HAVING
⦁ GROUP BY groups rows sharing a value to perform aggregate calculations.
⦁ HAVING filters groups based on conditions (like WHERE but for groups).

Example:
Find total sales per product with sales > 1000:
SELECT product_id, SUM(sales) AS total_sales
FROM sales_data
GROUP BY product_id
HAVING SUM(sales) > 1000;

2️⃣ Subqueries
⦁ A query inside another query. Useful for filtering or calculating values dynamically.

Example:
Get customers who placed orders over 500:
SELECT customer_id, order_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

3️⃣ Aggregate Functions
⦁ Perform calculations on sets of rows:
⦁ COUNT() counts rows
⦁ SUM() adds numeric values
⦁ AVG() calculates average
⦁ MAX() and MIN() find extremes

Example:
Find average order amount per customer:
SELECT customer_id, AVG(amount) AS avg_order
FROM orders
GROUP BY customer_id;

4️⃣ Complex Joins with Filtering
⦁ Join tables and filter results in one query.

Example:
List customers with orders over100:
SELECT c.customer_name, o.order_id, o.amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.amount > 100;

📌 SQL Roadmap: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v/1506

💬 Double Tap ❤️ For More!

Data Analytics isn't rocket science. It's just a different language.

Here's a beginner's guide to the world of data analytics:

1) Understand the fundamentals:
- Mathematics
- Statistics
- Technology

2) Learn the tools:
- SQL
- Python
- Excel (yes, it's still relevant!)

3) Understand the data:
- What do you want to measure?
- How are you measuring it?
- What metrics are important to you?

4) Data Visualization:
- A picture is worth a thousand words

5) Practice:
- There's no better way to learn than to do it yourself.

Data Analytics is a valuable skill that can help you make better decisions, understand your audience better, and ultimately grow your business.

It's never too late to start learning!

Tune in to the 10th AI Journey 2025 international conference: scientists, visionaries, and global AI practitioners will come together on one stage. Here, you will hear the voices of those who don't just believe in the future—they are creating it!

Speakers include visionaries Kai-Fu Lee and Chen Qufan, as well as dozens of global AI gurus! Do you agree with their predictions about AI?

On the first day of the conference, November 19, we will talk about how AI is already being used in various areas of life, helping to unlock human potential for the future and changing creative industries, and what impact it has on humans and on a sustainable future.

On November 20, we will focus on the role of AI in business and economic development and present technologies that will help businesses and developers be more effective by unlocking human potential.

On November 21, we will talk about how engineers and scientists are making scientific and technological breakthroughs and creating the future today! The day's program includes presentations by scientists from around the world:
- Ajit Abraham (Sai University, India) will present on “Generative AI in Healthcare”
- Nebojša Bačanin Džakula (Singidunum University, Serbia) will talk about the latest advances in bio-inspired metaheuristics
- AIexandre Ferreira Ramos (University of São Paulo, Brazil) will present his work on using thermodynamic models to study the regulatory logic of trannoscriptional control at the DNA level
- Anderson Rocha (University of Campinas, Brazil) will give a presentation ennoscriptd “AI in the New Era: From Basics to Trends, Opportunities, and Global Cooperation”.

And in the special AIJ Junior track, we will talk about how AI helps us learn, create and ride the wave with AI.

The day will conclude with an award ceremony for the winners of the AI Challenge for aspiring data scientists and the AIJ Contest for experienced AI specialists. The results of an open selection of AIJ Science research papers will be announced.

Ride the wave with AI into the future!

Tune in to the AI Journey webcast on November 19-21.

✅ SQL Practice Questions with Answers 🧠🗃️

🔍 Q1. How to find the 2nd highest salary from a table?
✅ Answer:

SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

🔍 Q2. How to find duplicate values in a column?
✅ Answer:

SELECT name, COUNT(*) FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

🔍 Q3. How to select records that exist in one table but not in another?
✅ Answer:

SELECT * FROM employees
WHERE id NOT IN (SELECT employee_id FROM payroll);

🔍 Q4. How to get the top 3 highest salaries? (MySQL)
✅ Answer:

SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 3;

🔍 Q5. How to fetch employees with the same salary?
✅ Answer:

SELECT * FROM employees e1
WHERE EXISTS (
  SELECT 1 FROM employees e2
  WHERE e1.salary = e2.salary AND e1.id <> e2.id
);

🔍 Q6. How to get the department-wise highest salary?
✅ Answer:

SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department;

💬 Tap ❤️ for more!

✅ SQL Practice Questions with Answers: Part-2 🧠🗃️

🔍 Q7. Find employees who never received a bonus
👤 Table: employees
id | name
1 | Arjun
2 | Riya
3 | Meena

💰 Table: bonus
employee_id | bonus_amount
1 | 3000
3 | 5000

📄 Query:

SELECT e.id, e.name  
FROM employees e  
LEFT JOIN bonus b ON e.id = b.employee_id  
WHERE b.employee_id IS NULL;

📌 Result: Riya

🔍 Q8. Get highest salary employee from each department
🧾 Table: employees
id | name | dept | salary
1 | Arjun | HR | 40000
2 | Riya | IT | 55000
3 | Meena | IT | 62000
4 | Kabir | HR | 45000

📄 Query:

SELECT e.*  
FROM employees e  
JOIN (  
  SELECT department, MAX(salary) AS max_salary  
  FROM employees  
  GROUP BY department  
) t  
ON e.department = t.department  
AND e.salary = t.max_salary;

📌 Result: Kabir (HR), Meena (IT)

🔍 Q9. Count number of employees who joined each year
🗓 Table: employees
id | name | join_date
1 | Arjun | 2021-03-10
2 | Riya | 2022-05-12
3 | Meena | 2021-11-03
4 | Kabir | 2023-01-09

📄 Query:

SELECT YEAR(join_date) AS join_year, COUNT(*) AS total  
FROM employees  
GROUP BY YEAR(join_date)  
ORDER BY join_year;

📌 Result:
2021 → 2
2022 → 1
2023 → 1

🔍 Q10. Find employees earning more than department average
🧾 Table: employees
id | name | dept | salary
1 | Arjun | HR | 40000
2 | Riya | IT | 55000
3 | Meena | IT | 62000
4 | Kabir | HR | 45000

📄 Query:

SELECT e.*  
FROM employees e  
JOIN (  
  SELECT department, AVG(salary) AS avg_salary  
  FROM employees  
  GROUP BY department  
) t  
ON e.department = t.department  
WHERE e.salary > t.avg_salary;

📌 Result: Kabir (HR), Meena (IT)

🔍 Q11. Fetch the 5th highest salary from employee table
🧾 Table: employees
Salaries: 90000, 85000, 78000, 76000, 72000, 70000

📄 Query:

SELECT DISTINCT salary  
FROM employees  
ORDER BY salary DESC  
LIMIT 1 OFFSET 4;

📌 Result: 72000

🔍 Q12. Find employees working on more than one project
📂 Table: project_assignments
employee_id | project_id
1 | 101
1 | 102
2 | 103
3 | 104
3 | 105
3 | 106

📄 Query:

SELECT employee_id, COUNT(*) AS project_count  
FROM project_assignments  
GROUP BY employee_id  
HAVING COUNT(*) > 1;

📌 Result:
1 → 2 projects
3 → 3 projects

💬 Tap ❤️ for more!

✅ SQL Practice Questions with Answers: Part-3 🧠🗃️

🔍 Q13. Find employees whose salary is above the company average
🧾 Table: employees
id | name | salary
1 | Arjun | 40000
2 | Riya | 55000
3 | Meena | 62000
4 | Kabir | 45000

📄 Query:

SELECT *  
FROM employees  
WHERE salary > (SELECT AVG(salary) FROM employees);

📌 Result: Riya, Meena

🔍 Q14. Get the 3 most recent joined employees
🗓 Table: employees
id | name | join_date
1 | Arjun | 2021-03-10
2 | Riya | 2022-05-12
3 | Meena | 2023-02-01
4 | Kabir | 2023-11-09

📄 Query:

SELECT *  
FROM employees  
ORDER BY join_date DESC  
LIMIT 3;

📌 Result: Kabir, Meena, Riya

🔍 Q15. Retrieve employees who don’t have a manager assigned
🧾 Table: employees
id | name | manager_id
1 | Arjun | NULL
2 | Riya | 1
3 | Meena | NULL
4 | Kabir | 2

📄 Query:

SELECT id, name  
FROM employees  
WHERE manager_id IS NULL;

📌 Result: Arjun, Meena

🔍 Q16. Find departments where more than 2 employees work
🧾 Table: employees
id | name | department
1 | Arjun | HR
2 | Riya | IT
3 | Meena | IT
4 | Kabir | HR
5 | John | IT

📄 Query:

SELECT department, COUNT(*) AS total  
FROM employees  
GROUP BY department  
HAVING COUNT(*) > 2;

📌 Result: IT → 3 employees

🔍 Q17. Select employees whose salary equals department average
🧾 Table: employees
id | name | dept | salary
1 | Arjun | HR | 40000
2 | Riya | IT | 55000
3 | Meena | IT | 62000
4 | Kabir | HR | 45000

📄 Query:

SELECT e.*  
FROM employees e  
JOIN (  
  SELECT dept, AVG(salary) AS avg_salary  
  FROM employees  
  GROUP BY dept  
) t ON e.dept = t.dept  
WHERE e.salary = t.avg_salary;

📌 Result: None (but logic works if matches exist)

🔍 Q18. Get employees who have at least one matching project
📂 Table: project_assignments
employee_id | project_id
1 | 101
2 | 101
2 | 102
3 | 103
4 | 101

📄 Query:

SELECT p1.employee_id, p2.employee_id AS colleague  
FROM project_assignments p1  
JOIN project_assignments p2  
ON p1.project_id = p2.project_id  
AND p1.employee_id <> p2.employee_id;

📌 Result:
Employees 1, 2, 4 share project 101

💬 Tap ❤️ for more!

✅ Free Resources to Learn SQL in 2025 🧠📚

1. YouTube Channels
• freeCodeCamp – Comprehensive SQL courses
• Simplilearn – SQL basics and advanced topics
• CodeWithMosh – SQL tutorial for beginners
• Alex The Analyst – Practical SQL for data analysis

2. Websites
• W3Schools SQL Tutorial – Easy-to-understand basics
• SQLZoo – Interactive SQL tutorials with exercises
• GeeksforGeeks SQL – Concepts, interview questions, and examples
• LearnSQL – Free courses and interactive editor

3. Practice Platforms
• LeetCode (SQL section) – Interview-style SQL problems
• HackerRank (SQL section) – Challenges and practice problems
• StrataScratch – Real-world SQL questions from companies
• SQL Fiddle – Online SQL sandbox for testing queries

4. Free Courses
• Khan Academy: Intro to SQL – Basic database concepts and SQL
• Codecademy: Learn SQL (Basic) – Interactive lessons
• Great Learning: SQL for Beginners – Free certification course
• Udemy (search for free courses) – Many introductory SQL courses often available for free

5. Books for Starters
• “SQL in 10 Minutes, Sams Teach Yourself” – Ben Forta
• “SQL Practice Problems: 57 Problems to Test Your SQL Skills” – Sylvia Moestl Wasserman
• “Learning SQL” – Alan Beaulieu

6. Must-Know Concepts
• SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
• JOINs (INNER, LEFT, RIGHT, FULL)
• Subqueries, CTEs (Common Table Expressions)
• Window Functions (RANK, ROW_NUMBER, LEAD, LAG)
• Basic DDL (CREATE TABLE) and DML (INSERT, UPDATE, DELETE)

💡 Practice consistently with real-world scenarios.

💬 Tap ❤️ for more!

✅ Top 5 Mistakes to Avoid When Learning SQL ❌📄

1️⃣ Ignoring Data Basics
Don't skip understanding tables, rows, primary keys, and relationships. These are the foundation of SQL.

2️⃣ Memorizing Queries Without Practice
Reading syntax isn't enough. Write real queries on sample databases to retain concepts.

3️⃣ Not Using Joins Early On
Many avoid JOINs thinking they're hard. Practice INNER, LEFT, and RIGHT JOINs with real examples to understand table relationships.

4️⃣ Skipping GROUP BY and Aggregates
GROUP BY with COUNT, SUM, AVG, etc., is core to analytics. Learn it early and use it often.

5️⃣ Not Practicing Real-World Scenarios
Writing SELECT * from a table isn't enough. Use projects like sales reports, user activity tracking, or inventory queries.

💬 Tap ❤️ for more!

📊 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: How do you use CASE in SQL?

👋 𝗠𝗲: Use CASE to add conditional logic inside SELECT, WHERE, or ORDER BY.

Example:

SELECT name,
       salary,
       CASE 
         WHEN salary >= 80000 THEN 'High'
         WHEN salary >= 50000 THEN 'Medium'
         ELSE 'Low'
       END AS salary_level
FROM employees;

🧠 Logic Breakdown:
- Works like if-else
- Evaluates conditions top to bottom
- Returns the first match
- ELSE is optional (defaults to NULL)

✅ Use Case:
- Create custom categories
- Replace values based on logic
- Conditional ordering or filtering

💬 Tap ❤️ for more!

✅ SQL Skills Every Beginner Should Learn 📊💻

1️⃣ Understanding the Basics
⦁ What is a database and table
⦁ Rows, columns, primary keys, foreign keys
⦁ Relational database concepts

2️⃣ Core SQL Queries
⦁ SELECT, FROM, WHERE – Get filtered data
⦁ ORDER BY, LIMIT – Sort and control output
⦁ DISTINCT, BETWEEN, IN, LIKE – Filter smarter

3️⃣ Joins (Combine Tables)
⦁ INNER JOIN – Matching records in both tables
⦁ LEFT JOIN, RIGHT JOIN – Include unmatched from one side
⦁ FULL OUTER JOIN – All records, matched or not

4️⃣ Aggregations
⦁ COUNT(), SUM(), AVG(), MIN(), MAX()
⦁ GROUP BY to summarize data
⦁ HAVING to filter aggregated results

5️⃣ Subqueries & CTEs
⦁ Subquery inside WHERE or SELECT
⦁ WITH clause for clean and reusable code

6️⃣ Window Functions
⦁ ROW_NUMBER(), RANK(), DENSE_RANK()
⦁ PARTITION BY, ORDER BY inside OVER()

7️⃣ Data Cleaning & Logic
⦁ Handle NULL values
⦁ Use CASE WHEN for conditional columns
⦁ Remove duplicates using DISTINCT or ROW_NUMBER()

8️⃣ Practice & Projects
⦁ Sales reports, user activity, inventory tracking
⦁ Work on public datasets
⦁ Solve SQL questions on LeetCode or HackerRank

Double Tap ♥️ For More

✅ SQL Aggregation & GROUP BY Explained 📊🧠

Aggregation functions are used to summarize data, especially when working with grouped values.

1️⃣ Common Aggregate Functions

⦁ COUNT() – Total number of rows
⦁ SUM() – Adds up numeric values
⦁ AVG() – Calculates the average
⦁ MIN() / MAX() – Finds smallest/largest value

Example:

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

➡️ Returns the number of employees in each department.

2️⃣ GROUP BY

Used with aggregate functions to group rows based on column values.

SELECT city, AVG(salary)  
FROM employees  
GROUP BY city;

➡️ Shows average salary by city.

3️⃣ HAVING vs WHERE

⦁ WHERE filters rows before grouping
⦁ HAVING filters groups after aggregation

Example:

SELECT dept_id, COUNT(*)  
FROM employees  
GROUP BY dept_id  
HAVING COUNT(*) > 5;

➡️ Shows departments with more than 5 employees.

4️⃣ GROUP BY Multiple Columns

SELECT dept_id, role, COUNT(*)  
FROM employees  
GROUP BY dept_id, role;

➡️ Groups by department and role for detailed summaries.

5️⃣ Real-World Use Cases

✅ Sales by region
✅ Orders per customer
✅ Avg. rating per product
✅ Monthly revenue reports

💬 Tap ❤️ for more!

✅ SQL Window Functions 🪟📊

Window functions perform calculations across rows related to the current row without collapsing data, unlike GROUP BY.

1️⃣ ROW_NUMBER()
Assigns a unique number to each row within a partition.

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

📌 *Use case:* Rank employees by salary within each department.


2️⃣ RANK() vs DENSE_RANK()
• RANK() → Skips numbers on ties (1, 2, 2, 4)
• DENSE_RANK() → No gaps (1, 2, 2, 3)

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

3️⃣ LAG() & LEAD()
Access previous or next row values.

SELECT name, salary,
  LAG(salary)  OVER (ORDER BY id) AS prev_salary,
  LEAD(salary) OVER (ORDER BY id) AS next_salary
FROM employees;

📌 Use case: Compare current vs previous/next values
(e.g., salary change, stock price movement).

4️⃣ NTILE(n)
Divides rows into *n* equal buckets.

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

📌 Use case: Quartiles & percentile-based analysis.


5️⃣ Aggregates with OVER()
Running totals & partition-wise calculations.

SELECT name, dept_id, salary,
  SUM(salary) OVER (PARTITION BY dept_id) AS dept_total
FROM employees;

🧠 Interview Q&A

Q1️⃣ GROUP BY vs OVER()?
• GROUP BY → Collapses rows (one row per group)
• OVER() → Keeps all rows and adds calculated columns

Q2️⃣ When to use LAG()?
To compare current row with previous data
(e.g., daily revenue change, previous month balance).

Q3️⃣ No PARTITION BY used?
The function runs over the entire result set.

Q4️⃣ Can we use ORDER BY inside OVER()?
✅ Yes. Required for ranking, LAG/LEAD, running totals.

💬 Double tap ❤️ & share for more SQL tips! 🚀