🧠 SQL Interview Question (Moderate–Tricky & Duplicate Transaction Detection)
📌

transactions(transaction_id, user_id, transaction_date, amount)

❓ Ques :

👉 Find users who made multiple transactions with the same amount consecutively.

🧩 How Interviewers Expect You to Think

• Sort transactions chronologically for each user
• Compare the current transaction amount with the previous one
• Use a window function to detect consecutive duplicates

💡 SQL Solution

SELECT
user_id,
transaction_date,
amount
FROM (
SELECT
user_id,
transaction_date,
amount,
LAG(amount) OVER (
PARTITION BY user_id
ORDER BY transaction_date
) AS prev_amount
FROM transactions
) t
WHERE amount = prev_amount;

🔥 Why This Question Is Powerful

• Tests understanding of LAG() for row comparison
• Evaluates ability to identify patterns in sequential data
• Reflects real-world use cases like detecting suspicious or duplicate transactions

❤️ React if you want more tricky real interview-level SQL questions 🚀

🚀 Window Functions ⭐

Window functions are one of the most powerful SQL features used in data analytics, reporting, and advanced SQL interviews.

👉 They allow you to perform calculations across rows without collapsing them like GROUP BY.

In simple words:
GROUP BY → reduces rows

Window Functions → keep rows but add calculated values

🧠 Basic Syntax of Window Functions

SELECT column, window_function() 
OVER ( 
  PARTITION BY column 
  ORDER BY column 
) 
FROM table;

Components:
- OVER() → defines the window
- PARTITION BY → splits data into groups
- ORDER BY → defines calculation order

🔹 1️⃣ ROW_NUMBER()

Assigns a unique sequential number to rows.

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

Result:
name | salary | rank
Rahul | 90000 | 1
Priya | 85000 | 2
Amit | 85000 | 3

👉 Even if salaries are same, numbers stay unique.

🔹 2️⃣ RANK()

Assigns rank but skips numbers when ties occur.

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

Result:
name | salary | rank
Rahul | 90000 | 1
Priya | 85000 | 2
Amit | 85000 | 2
Neha | 80000 | 4

Notice rank 3 is skipped.

🔹 3️⃣ DENSE_RANK()

Similar to RANK but does not skip numbers.

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

Result:
name | salary | rank
Rahul | 90000 | 1
Priya | 85000 | 2
Amit | 85000 | 2
Neha | 80000 | 3

🔹 4️⃣ PARTITION BY

Used to divide rows into groups before calculation.

Example: Rank employees within each department

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

👉 Each department gets its own ranking.

🔹 5️⃣ LAG()

Used to access previous row values.

Example: Compare sales with previous day.

SELECT date, sales, LAG(sales) OVER(ORDER BY date) AS previous_sales 
FROM sales;

🔹 6️⃣ LEAD()

Used to access next row values.

SELECT date, sales, LEAD(sales) OVER(ORDER BY date) AS next_sales 
FROM sales;

⭐ Real Data Analyst Examples

Top 3 highest salaries

SELECT ** 
FROM ( 
  SELECT name, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) AS rn 
  FROM employees 
) t 
WHERE rn <= 3;

Running total of sales

SELECT date, sales, SUM(sales) OVER(ORDER BY date) AS running_total 
FROM sales;

Rank products by category

SELECT product_name, category, price, 
RANK() OVER(PARTITION BY category ORDER BY price DESC) AS rank 
FROM products;

🎯 Common Interview Questions
✔ Difference between ROW_NUMBER, RANK, DENSE_RANK
✔ Find Nth highest salary
✔ Running totals using window functions
✔ Compare current row with previous row
✔ Rank employees by department

🚀 Mini Practice Tasks
Task 1: Assign row numbers to employees by salary.
Task 2: Rank employees by salary.
Task 3: Find top 3 highest salaries using window functions.
Task 4: Calculate running total of sales.

💼 What You Must Master
✅ ROW_NUMBER()
✅ RANK()
✅ DENSE_RANK()
✅ PARTITION BY
✅ LAG() / LEAD()
✅ Running totals

These functions are used heavily in real analytics queries and SQL interviews.

Double Tap ♥️ For More

✅ Useful Platform to Practice SQL Programming 🧠🖥️

Learning SQL is just the first step — practice is what builds real skill. Here are the best platforms for hands-on SQL:

1️⃣ LeetCode – For Interview-Oriented SQL Practice
• Focus: Real interview-style problems
• Levels: Easy to Hard
• Schema + Sample Data Provided
• Great for: Data Analyst, Data Engineer, FAANG roles
✔ Tip: Start with Easy → filter by “Database” tag
✔ Popular Section: Database → Top 50 SQL Questions
Example Problem: “Find duplicate emails in a user table” → Practice filtering, GROUP BY, HAVING

2️⃣ HackerRank – Structured & Beginner-Friendly
• Focus: Step-by-step SQL track
• Has certification tests (SQL Basic, Intermediate)
• Problem sets by topic: SELECT, JOINs, Aggregations, etc.
✔ Tip: Follow the full SQL track
✔ Bonus: Company-specific challenges
Try: “Revising Aggregations – The Count Function” → Build confidence with small wins

3️⃣ Mode Analytics – Real-World SQL in Business Context
• Focus: Business intelligence + SQL
• Uses real-world datasets (e.g., e-commerce, finance)
• Has an in-browser SQL editor with live data
✔ Best for: Practicing dashboard-level queries
✔ Tip: Try the SQL case studies & tutorials

4️⃣ StrataScratch – Interview Questions from Real Companies
• 500+ problems from companies like Uber, Netflix, Google
• Split by company, difficulty, and topic
✔ Best for: Intermediate to advanced level
✔ Tip: Try “Hard” questions after doing 30–50 easy/medium

5️⃣ DataLemur – Short, Practical SQL Problems
• Crisp and to the point
• Good UI, fast learning
• Real interview-style logic
✔ Use when: You want fast, smart SQL drills

📌 How to Practice Effectively:
• Spend 20–30 mins/day
• Focus on JOINs, GROUP BY, HAVING, Subqueries
• Analyze problem → write → debug → re-write
• After solving, explain your logic out loud

🧪 Practice Task:
Try solving 5 SQL questions from LeetCode or HackerRank this week. Start with SELECT, WHERE, and GROUP BY.

💬 Tap ❤️ for more!

⚙️ SQL Developer Roadmap

📂 SQL Basics (CREATE, DROP, USE Database)
∟📂 Data Types & DDL (Tables, Constraints - PK/FK)
∟📂 DML (INSERT, UPDATE, DELETE)
∟📂 SELECT Queries (DISTINCT, LIMIT/TOP)
∟📂 WHERE Clause (Operators, LIKE, IN, BETWEEN)
∟📂 ORDER BY & Sorting (ASC/DESC)
∟📂 Aggregate Functions (COUNT, SUM, AVG, MIN/MAX)
∟📂 GROUP BY & HAVING
∟📂 JOINs (INNER, LEFT, RIGHT, FULL)
∟📂 Subqueries
∟📂 String Functions (CONCAT, SUBSTRING, UPPER/LOWER)
∟📂 Date Functions (NOW, DATEADD, DATEDIFF)
∟📂 Window Functions (ROW_NUMBER, RANK, PARTITION BY)
∟📂 CTEs (Common Table Expressions)
∟📂 Indexes & Performance
∟📂 Transactions (BEGIN, COMMIT, ROLLBACK)
∟📂 Views & Stored Procedures
∟📂 Practice (LeetCode SQL, HackerRank)
∟✅ Apply for Data Analyst / Backend Roles

💬 Tap ❤️ for more!

𝗙𝗥𝗘𝗘 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀 𝗢𝗻 𝗕𝘆 𝗜𝗻𝗱𝘂𝘀𝘁𝗿𝘆 𝗘𝘅𝗽𝗲𝗿𝘁𝘀 😍

Choose the Right Career Path in 2026

Learn → Level Up → Get Hired

🎯 Join this FREE Career Guidance Session & find:
✔ The right tech career for YOU
✔ Skills companies are hiring for
✔ Step-by-step roadmap to get a job

👇 𝗦𝗮𝘃𝗲 𝘆𝗼𝘂𝗿 𝘀𝗽𝗼𝘁 𝗻𝗼𝘄 (𝗟𝗶𝗺𝗶𝘁𝗲𝗱 𝘀𝗲𝗮𝘁𝘀)

https://pdlink.in/4sNAyhW

Date & Time :- 18th March 2026 , 7:00 PM

🔥 Top SQL Interview Questions with Answers

🎯 1️⃣ Find 2nd Highest Salary
📊 Table: employees
id | name | salary
1 | Rahul | 50000
2 | Priya | 70000
3 | Amit | 60000
4 | Neha | 70000

❓ Problem Statement: Find the second highest distinct salary from the employees table.

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

🎯 2️⃣ Find Nth Highest Salary
📊 Table: employees
id | name | salary
1 | A | 100
2 | B | 200
3 | C | 300
4 | D | 200

❓ Problem Statement: Write a query to find the 3rd highest salary.

✅ Solution
SELECT salary FROM ( SELECT salary, DENSE_RANK() OVER(ORDER BY salary DESC) r FROM employees ) t WHERE r = 3;

🎯 3️⃣ Find Duplicate Records
📊 Table: employees
id | name
1 | Rahul
2 | Amit
3 | Rahul
4 | Neha

❓ Problem Statement: Find all duplicate names in the employees table.

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

🎯 4️⃣ Customers with No Orders
📊 Table: customers
customer_id | name
1 | Rahul
2 | Priya
3 | Amit

📊 Table: orders
order_id | customer_id
101 | 1
102 | 2

❓ Problem Statement: Find customers who have not placed any orders.

✅ Solution
SELECT c.name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.customer_id IS NULL;

🎯 5️⃣ Top 3 Salaries per Department
📊 Table: employees
name | department | salary
A | IT | 100
B | IT | 200
C | IT | 150
D | HR | 120
E | HR | 180

❓ Problem Statement: Find the top 3 highest salaries in each department.

✅ Solution
SELECT * FROM ( SELECT name, department, salary, ROW_NUMBER() OVER( PARTITION BY department ORDER BY salary DESC ) r FROM employees ) t WHERE r <= 3;

🎯 6️⃣ Running Total of Sales
📊 Table: sales
date | sales
2024-01-01 | 100
2024-01-02 | 200
2024-01-03 | 300

❓ Problem Statement: Calculate the running total of sales by date.

✅ Solution
SELECT date, sales, SUM(sales) OVER(ORDER BY date) AS running_total FROM sales;

🎯 7️⃣ Employees Above Average Salary
📊 Table: employees
name | salary
A | 100
B | 200
C | 300

❓ Problem Statement: Find employees earning more than the average salary.

✅ Solution
SELECT name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees );

🎯 8️⃣ Department with Highest Total Salary
📊 Table: employees
name | department | salary
A | IT | 100
B | IT | 200
C | HR | 500

❓ Problem Statement: Find the department with the highest total salary.

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

🎯 9️⃣ Customers Who Placed Orders
📊 Tables: Same as Q4
❓ Problem Statement: Find customers who have placed at least one order.

✅ Solution
SELECT name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE c.customer_id = o.customer_id );

🎯 🔟 Remove Duplicate Records
📊 Table: employees
id | name
1 | Rahul
2 | Rahul
3 | Amit

❓ Problem Statement: Delete duplicate records but keep one unique record.

✅ Solution
DELETE FROM employees WHERE id NOT IN ( SELECT MIN(id) FROM employees GROUP BY name );

🚀 Pro Tip:
👉 In interviews:
First explain logic
Then write query
Then optimize

Double Tap ♥️ For More

𝗙𝗿𝗲𝘀𝗵𝗲𝗿𝘀 𝗖𝗮𝗻 𝗚𝗲𝘁 𝗮 𝟯𝟬 𝗟𝗣𝗔 𝗝𝗼𝗯 𝗢𝗳𝗳𝗲𝗿 𝘄𝗶𝘁𝗵 𝗔𝗜 & 𝗗𝗦 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻😍

IIT Roorkee offering AI & Data Science Certification Program

💫Learn from IIT ROORKEE Professors
✅ Students & Fresher can apply
🎓 IIT Certification Program
💼 5000+ Companies Placement Support

Deadline: 22nd March 2026

📌 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗡𝗼𝘄 👇 :-

https://pdlink.in/4kucM7E

Big Opportunity, Do join asap!

📊 Complete SQL Syllabus Roadmap (Beginner to Expert) 🗄️

🔰 Beginner Level:

1. Intro to Databases: What are databases, Relational vs. Non-Relational
2. SQL Basics: SELECT, FROM, WHERE
3. Data Types: INT, VARCHAR, DATE, BOOLEAN, etc.
4. Operators: Comparison, Logical (AND, OR, NOT)
5. Sorting & Filtering: ORDER BY, LIMIT, DISTINCT
6. Aggregate Functions: COUNT, SUM, AVG, MIN, MAX
7. GROUP BY and HAVING: Grouping Data and Filtering Groups
8. Basic Projects: Creating and querying a simple database (e.g., a student database)

⚙️ Intermediate Level:

1. Joins: INNER, LEFT, RIGHT, FULL OUTER JOIN
2. Subqueries: Using queries within queries
3. Indexes: Improving Query Performance
4. Data Modification: INSERT, UPDATE, DELETE
5. Transactions: ACID Properties, COMMIT, ROLLBACK
6. Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT
7. Views: Creating Virtual Tables
8. Stored Procedures & Functions: Reusable SQL Code
9. Date and Time Functions: Working with Date and Time Data
10. Intermediate Projects: Designing and querying a more complex database (e.g., an e-commerce database)

🏆 Expert Level:

1. Window Functions: RANK, ROW_NUMBER, LAG, LEAD
2. Common Table Expressions (CTEs): Recursive and Non-Recursive
3. Performance Tuning: Query Optimization Techniques
4. Database Design & Normalization: Understanding Database Schemas (Star, Snowflake)
5. Advanced Indexing: Clustered, Non-Clustered, Filtered Indexes
6. Database Administration: Backup and Recovery, Security, User Management
7. Working with Large Datasets: Partitioning, Data Warehousing Concepts
8. NoSQL Databases: Introduction to MongoDB, Cassandra, etc. (optional)
9. SQL Injection Prevention: Secure Coding Practices
10. Expert Projects: Designing, optimizing, and managing a large-scale database (e.g., a social media database)

💡 Bonus: Learn about Database Security, Cloud Databases (AWS RDS, Azure SQL Database, Google Cloud SQL), and Data Modeling Tools.

👍 Tap ❤️ for more

SQL Cheat Sheet for Data Analysts 🗄️📊

1. SELECT
What it is: Used to choose columns from a table
What it does: Returns specific columns of data

Query: Fetch name and salary

SELECT name, salary 
FROM employees;

2. FROM
What it is: Specifies the table
What it does: Tells SQL where to get data from

Query: Fetch all data from employees

SELECT * 
FROM employees;

3. WHERE
What it is: Filters rows based on condition
What it does: Returns only matching rows

Query: Employees with salary > 30000

SELECT * 
FROM employees 
WHERE salary > 30000;

4. ORDER BY
What it is: Sorts the data
What it does: Arranges rows in order

Query: Sort by salary (highest first)

SELECT * 
FROM employees 
ORDER BY salary DESC;

5. COUNT()
What it is: Counts rows
What it does: Returns total records

Query: Count employees

SELECT COUNT(*) 
FROM employees;

6. AVG()
What it is: Calculates average
What it does: Returns mean value

Query: Average salary

SELECT AVG(salary) 
FROM employees;

7. GROUP BY
What it is: Groups rows by column
What it does: Applies aggregation per group

Query: Avg salary per department

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

8. HAVING
What it is: Filters grouped data
What it does: Returns filtered groups

Query: Departments with avg salary > 40000

SELECT department, AVG(salary) 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 40000;

9. INNER JOIN
What it is: Combines matching rows from tables
What it does: Returns common data

Query: Employees with department names

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

10. LEFT JOIN
What it is: Combines all left + matching right
What it does: Returns all left table data

Query: All employees with departments

SELECT e.name, d.department_name 
FROM employees e 
LEFT JOIN departments d 
ON e.dept_id = d.dept_id;

11. CASE WHEN
What it is: Conditional logic
What it does: Creates values based on condition

Query: Categorize salary

SELECT name, 
    CASE 
        WHEN salary > 40000 THEN 'High' 
        ELSE 'Low' 
    END AS category 
FROM employees;

12. SUBQUERY
What it is: Query inside another query
What it does: Uses result of inner query

Query: Salary above average

SELECT name, salary 
FROM employees 
WHERE salary > ( 
    SELECT AVG(salary) 
    FROM employees 
);

13. RANK()
What it is: Window function
What it does: Assigns rank without grouping

Query: Rank employees by salary

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

14. DISTINCT
What it is: Removes duplicates
What it does: Returns unique values

Query: Unique departments

SELECT DISTINCT department 
FROM employees;

15. LIKE
What it is: Pattern matching
What it does: Filters text patterns

Query: Names starting with A

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

Double Tap ♥️ For More

Last Chance to Join 🚀

Don’t miss this power-packed masterclass by Tushar Jha, Lead Data Scientist at Google.

Learn how FinTech leaders use data analytics to drive real growth with PW Skills.

⏳ 2 Hours | High-Impact Learning

Secure your spot now before it’s gone - https://tinyurl.com/3dzsw8my

📢 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗔𝗹𝗲𝗿𝘁 – Data Analytics with Artificial Intelligence

Upgrade your career with AI-powered data science skills.
Open for all. No Coding Background Required

📊 Learn Data Analytics with Artificial Intelligence from Scratch
🤖 AI Tools & Automation
📈 Build real world Projects for job ready portfolio
🎓 E&ICT IIT Roorkee Certification Program

🔥Deadline :- 22nd March

𝗔𝗽𝗽𝗹𝘆 𝗡𝗼𝘄 👇 :- 
https://pdlink.in/4tkErvS

Don't Miss This Opportunity. Get Placement Assistance With 5000+ Companies

Quick recap of essential SQL basics 😄👇

SQL is a domain-specific language used for managing and querying relational databases. It's crucial for interacting with databases, retrieving, storing, updating, and deleting data. Here are some fundamental SQL concepts:

1. Database
- A database is a structured collection of data. It's organized into tables, and SQL is used to manage these tables.

2. Table
- Tables are the core of a database. They consist of rows and columns, and each row represents a record, while each column represents a data attribute.

3. Query
- A query is a request for data from a database. SQL queries are used to retrieve information from tables. The SELECT statement is commonly used for this purpose.

4. Data Types
- SQL supports various data types (e.g., INTEGER, TEXT, DATE) to specify the kind of data that can be stored in a column.

5. Primary Key
- A primary key is a unique identifier for each row in a table. It ensures that each row is distinct and can be used to establish relationships between tables.

6. Foreign Key
- A foreign key is a column in one table that links to the primary key in another table. It creates relationships between tables in a database.

7. CRUD Operations
- SQL provides four primary operations for data manipulation:
- Create (INSERT) - Add new records to a table.
- Read (SELECT) - Retrieve data from one or more tables.
- Update (UPDATE) - Modify existing data.
- Delete (DELETE) - Remove records from a table.

8. WHERE Clause
- The WHERE clause is used in SELECT, UPDATE, and DELETE statements to filter and conditionally manipulate data.

9. JOIN
- JOIN operations are used to combine data from two or more tables based on a related column. Common types include INNER JOIN, LEFT JOIN, and RIGHT JOIN.

10. Index
- An index is a database structure that improves the speed of data retrieval operations. It's created on one or more columns in a table.

11. Aggregate Functions
- SQL provides functions like SUM, AVG, COUNT, MAX, and MIN for performing calculations on groups of data.

12. Transactions
- Transactions are sequences of one or more SQL statements treated as a single unit. They ensure data consistency by either applying all changes or none.

13. Normalization
- Normalization is the process of organizing data in a database to minimize data redundancy and improve data integrity.

14. Constraints
- Constraints (e.g., NOT NULL, UNIQUE, CHECK) are rules that define what data is allowed in a table, ensuring data quality and consistency.

Here is an amazing resources to learn & practice SQL: https://bit.ly/3FxxKPz

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

🚀Greetings from PVR Cloud Tech!! 🌈

🔥 Do you want to become a Master in Azure Cloud Data Engineering?

If you're ready to build in-demand skills and unlock exciting career opportunities,
this is the perfect place to start!

📌 Start Date: 23rd March 2026

⏰ Time: 07 AM – 08 AM IST | Monday

🔗 𝐈𝐧𝐭𝐞𝐫𝐞𝐬𝐭𝐞𝐝 𝐢𝐧 𝐀𝐳𝐮𝐫𝐞 𝐃𝐚𝐭𝐚 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫𝐢𝐧𝐠 𝐥𝐢𝐯𝐞 𝐬𝐞𝐬𝐬𝐢𝐨𝐧𝐬?

👉 Message us on WhatsApp:

https://wa.me/917032678595?text=Interested_to_join_Azure_Data_Engineering_live_sessions

🔹 Course Content:

https://drive.google.com/file/d/1QKqhRMHx2SDNDTmPAf3_54fA6LljKHm6/view

📱 Join WhatsApp Group:

https://chat.whatsapp.com/GCdcWr7v5JI1taguJrgU9j

📥 Register Now:

https://forms.gle/f3t9Ao2DRGMkyBdC9

📺 WhatsApp Channel:

https://www.whatsapp.com/channel/0029Vb60rGU8V0thkpbFFW2n

Team 
PVR Cloud Tech :) 
+91-9346060794