SQL Coding Interview Questions Discover recent interview questions from diverse companies, revealing sought-after skills in coding, problem-solving, and industry knowledge. 1. Write All Numbers that appear at least three times consecutively output: SQLSELECT DISTINCT num FROM ( SELECT num, LEAD(num) OVER (ORDER BY id) AS next_num, LAG(num) OVER (ORDER BY id) AS prev_num FROM your_table ) t WHERE num SQL Coding Interview Questions

Discover recent interview questions from diverse companies, revealing sought-after skills in coding, problem-solving, and industry knowledge.

1. Write All Numbers that appear at least three times consecutively

output:

SQL

SELECT DISTINCT num
FROM (
SELECT num,
LEAD(num) OVER (ORDER BY id) AS next_num,
LAG(num) OVER (ORDER BY id) AS prev_num
FROM your_table
) t
WHERE num = next_num AND num = prev_num;

2. Find the Fourth highest Salary from Employee Table

output:

SQL

SELECT salary
FROM (
SELECT salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM Employee
) AS ranked
WHERE rn = 4;

--(OR)

SELECT salary
FROM Employee
ORDER BY salary DESC
OFFSET 3 ROWS
FETCH NEXT 1 ROW ONLY;

3. SQL query to swap seat IDs for every two consecutive students, while ensuring that the ID of the last student remains unchanged if the total number of students is odd?

output:

SQL

SELECT
CASE WHEN id % 2 = 0 THEN id - 1
 WHEN (id % 2)! = 0 AND (SELECT MAX(id) FROM Seat)!= id then id+1
ELSE id
END AS id, Student
FROM Seat ORDER BY id

4.Write a sql query to fill Null Values with last not null values from Data Table

output:

SQL

WITH CTE as(SELECT id, value, count(value) over(order by id) as rn from DATA)
select id, CASE WHEN value Is null then max(value) over (partition by rn order by id)
ELSE value END AS value from CTE
--OR
select id, FIRST_VALUE(value) over(partition by rn order by id)
from (SELECT id, value, count(value) over(order by id) as rn from DATA)a order by id

5. Generate a cumulative sum of ‘marks’ for each row in a table Marks

output:

SQL

SELECT id, SUM(marks) OVER (ORDER BY id) AS marks FROM Marks;

6. Write a SQL query to fetch the genres that don’t have any movies associated with them.

output:

SQL

SELECT
GenreID,
GenreName
FROM
Genres where GenreID not in (select GenreID from movies)

--(OR)

SELECT g.GenreID, g.GenreName FROM Genres g
LEFT JOIN
Movies m ON g.GenreID = m.GenreID
WHERE
m.MovieID IS NULL;

7. Write a Query to fetch the records of brands whose amount is increasing every year from given Phone Prices table below

output:

SQL

WITH CTE as(
Select *, (case when Price < LEAD(Price, 1, Price+1) over (Partition by Brand Order by Year)
then 1
else 0 end) as flag
from PhonePrices)
Select * from PhonePrices Where Brand Not In (select Brand from CTE where flag=0)

8. Write a SQL query to delete all duplicate email entries in a table named Person,
keeping only unique emails based on its smallest Id

output:

SQL

Delete u1
From UserEmails as u1, UserEmails as u2
Where u1.Email = u2.Email and u1.Id > u2.Id;

--(OR)

WITH cte AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY Email ORDER BY ID) AS rn
FROM
UserEmails
)
DELETE FROM cte WHERE rn >= 2;

9. write a SQL query that finds out mangers who earn more than their employees

output:

SQL

Select M.Name
FROM
Employees3 AS M
JOIN
Employees3 AS E ON M.Id = E.ManagerId
WHERE M.Salary > E.Salary;

10. Swap all ‘m’ and ‘f’ values in the ‘gender’ column of the ‘salary’ table with a single update statement and no intermediate temp table.

output:

SQL

Update salary
set sex = case when sex = 'm' then 'f' else 'm' end;

11. Write an SQL query to sort the result in alternate order of Gender.

output:

SQL

Select id,name,gender
from emp
order by row_number()over(partition by gender order by id),gender desc

--(OR)

WITH alternate_order AS
(SELECT
ID,
NAME,
GENDER,
row_number()over (partition by gender order by id) as rnk
FROM EMP)
SELECT id,name,gender FROM alternate_order
order by rnk,gender DESC;

12. Write a SQL query to find out palindromes from the below Polindrome table.

OUTPUT:

SQL

SELECT * FROM polindrome where id= REVERSE(id)

13. Write a SQL query to join “users” (user_id, name) and “course_enrollments” (user_id, course_name) tables. Output one row per user with name and comma-separated list of courses.

OUTPUT:

SQL

SELECT u.name,
STRING_AGG(ce.course_name, ', ') AS enrolled_courses
FROM users u
JOIN
course_enrollments ce ON u.user_id = ce.user_id
GROUP BY u.name;

14. write an SQL query to calculate the total balance for each day, considering both credit and debit transactions. Display the result with the columns “transaction_date” and “total_balance”.

OUTPUT:

SQL

WITH CumulativeBalance AS (
SELECT
transaction_date,
SUM(CASE WHEN transaction_type = 'credit' THEN amount ELSE -amount END) AS balance
FROM
financial_transaction
GROUP BY
transaction_date
)
SELECT
transaction_date,
SUM(balance) OVER (ORDER BY transaction_date) AS total_balance
FROM
CumulativeBalance;

15. Write a SQL query to display the below output.

OUTPUT :

SQL

Select distinct id, max(val1)over(partition by id) as val1,
max(val2) over(partition by id) as val2,
max(val3) over(partition by id) as val3
from Input order by id;

16. Find the origin and the destination of each customer.

OUTPUT :

SQL

SELECT cust_id, MIN(origin) AS origin, MAX(destination) AS destination
    FROM flights
    GROUP BY cust_id;

(OR)

WITH FirstFlight AS (
    SELECT cust_id, origin, ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY flight_id) AS row_num
    FROM flights
),
LastFlight AS (
    SELECT cust_id, destination, ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY flight_id DESC) AS row_num
    FROM flights
)
SELECT f.cust_id,f.origin,l.destination
FROM FirstFlight f
JOIN LastFlight l ON f.cust_id = l.cust_id
WHERE f.row_num = 1 AND l.row_num = 1;