SQL

sql Interview coding/Query questions

• Difference between WHERE and HAVING clause in SQL
• LIMIT and OFFSET – The most Important concept
• union and union all
• Distinct – to get unique/diffent rows. it eliminates duplicate and show only unique records
• Normalization and denormalization
• Joins
  • anti join
  • self join
  • inner outer left,right,full join
• Aggregates function – count(), min(), max(), avg(), sum()
• Windows/analytics function
  • ROW_NUMBER()
  • RANK()
  • DENSE_RANK()
  • LAG()
  • LEAD()
• keys/constraints
  • null check
  • primary key, natural key, surrogate key
• Connection polling

Medium Level:

1 Write a query to find the second highest salary in an employee table.

2 Fetch all employees whose names contain the letter “a” exactly twice.

3 How do you retrieve only duplicate records from a table?

4 Write a query to calculate the running total of sales by date.

5 Find employees who earn more than the average salary in their department.

6 Write a query to find the most frequently occurring value in a column.

7 Fetch records where the date is within the last 7 days from today.

3,944

8 Write a query to count how many employees share the same salary.

9 How do you fetch the top 3 records for each group in a table?

10 Retrieve products that were never sold (hint: use LEFT JOIN).

Challenging Level:

1 Retrieve customers who made their first purchase in the last 6 months.

2 How do you pivot a table to convert rows into columns?

3 Write a query to calculate the percentage change in sales month-over-month.

3,944

4 Find the median salary of employees in a table.

5 Fetch all users who logged in consecutively for 3 days or more.

6 Write a query to delete duplicate rows while keeping one occurrence.

1,929

7 Create a query to calculate the ratio of sales between two categories.

8 How would you implement a recursive query to generate a hierarchical structure?

8,078

9 Write a query to find gaps in sequential numbering within a table.

10 Split a comma-separated string into individual rows using SQL.

3,944

Advanced Problem-Solving:

1 Rank products by sales in descending order for each region.

2 Fetch all employees whose salaries fall within the top 10% of their department.

1,929

3 Identify orders placed during business hours (e.g., 9 AM to 6 PM).

4 Write a query to get the count of users active on both weekdays and weekends.

8,078

5. Retrieve customers who made purchases across at least three different categories.

1- How to find duplicates in a table

2- How to delete duplicates from a table

3- Difference between union and union all

4- Difference between rank,row_number and dense_rank

5- Find records in a table which are not present in another table

6- Find the second highest salary employees in each department

7- Find employees with salary more than their manager’s salary

8- Difference between inner and left join

9- update a table and swap gender values.

10- two tables left, right, inner join number of record count return.

1- Top N products by sales, Top N products within each category, Ton N employees by salaries etc.

2- Year over year growth, YOY growth for each category, Products with higher sales than previous month etc.

3- Running sales over months, rolling N months sales, within each category etc.

4- Pivot rows to columns, eg: year wise sales for each category in separate columns etc

5- Number of records after different kinds of joins.

1/ Write a query to calculate the 7-day moving average for sales data.
2/ Given a table with employee in and out times, calculate the total hours worked per employee per day.
3/ Find the top N highest-grossing products per category.
4/ Write a query to identify the first and last transaction for each customer within a specific time range.
5/ Implement a SQL query to rank products by their sales within each region and break ties using additional criteria.
6/ Write a query to retrieve the second highest salary from an employee table without using LIMIT or OFFSET.
7/ Identify the percentage contribution of each product to the total revenue, grouped by category.
8/ Write a query to find gaps in sequential data, such as missing invoice numbers.
9/ Given a sales table, identify the month-over-month growth percentage for each product.
10/ Write a query to find customers who have made purchases in every quarter of the year.
11/ Calculate the cumulative sales for each product over time and compare it with the average sales.
12/ Write a query to retrieve all customers who made more purchases this month compared to the previous month.
13/ Identify products that have never been sold along with their categories.
14/ Find the top 3 customers who contributed the most to revenue in the last year.
15/ Write a query to compare the sales of the same product across two different time periods.
16/ Identify the longest streak of consecutive days a customer has made a purchase.
17/ Calculate the rolling retention rate for an app based on daily user activity.
18/ Find duplicate rows in a table and group them by their count.
19/ Write a query to delete duplicate rows but keep the row with the earliest timestamp.
20/ Calculate the median salary for employees grouped by department.
21/ Identify the products that have been sold for 3 consecutive months but not for the 4th month.
22/ Write a query to segment customers into cohorts based on their first purchase date and calculate retention rates for each cohort.
23/ Implement a query to calculate the total time spent by each user on the platform, excluding overlapping time intervals.
24/ Create a query to find all the products that were never purchased together in the same transaction.
25/ Write a query to generate a time-series report for sales data, filling in missing dates with zero sales.

Database design and optimization

• Normalization
  • Explain the normalization process and why it’s important.
  • Discuss different normal forms (1NF, 2NF, 3NF, BCNF).
• Indexing:
  • What are indexes, and how do they improve query performance?
  • Write a query to create an index on the salary column of the employee table.

CREATE INDEX idx_salary 
ON employee(salary);

• Transactions and Concurrency:
  • Explain ACID properties.
  • Write a query to start a transaction, update a record, and then commit the transaction.sqlCopy

START TRANSACTION; 
    UPDATE employee 
    SET salary = salary * 1.1 
    WHERE department = 'Sales'; 
COMMIT;

1/ Find the second highest salary from an Employee table.
-Query a method without using the LIMIT or TOP clauses.
2/ Write a query to return the nth highest salary from the Employee table.
3/ Given a table of customers and orders, write a query to find the total number of customers who have not placed any orders.
4/ Find the most recent order for each customer from an Orders table.
5/ Write a query to find employees who have the same salary as their manager.
6/ Find the employees who have the longest consecutive streak of being present without any absence, given an Attendance table.
7/ Write a query to find the employees who have worked in multiple departments, using only one query.
8/ Write a query to return a list of products that have never been sold, given a Products and Orders table.
9/ Find the number of customers who made their first purchase in each month.
10/ Write a query to find all employees who joined after their manager, based on a Join table.
11/ Given a list of employees, write a query to find all employees who are not supervisors of anyone.
12/ Write a query to calculate the cumulative sales by month, given an Orders table.
13/ Find the count of orders placed by each customer in each month, considering only those customers who placed orders in more than one month.
14/ Write a query to rank products based on sales, but reset the ranking for each category.
15/ Find the customers who have purchased the same product at least twice.
16/ Write a query to calculate the difference in days between the first and last orders of each customer.
17/ Write a query to identify the customers who have never ordered the same product twice.
18/ Find the percentage of total sales contributed by the top 5 products.
19/ Find the most common product purchased together with each product.
20/ Write a query to find the customers who have spent more than the average amount, but spent less than the highest spender.

Basic Questions:
1.⁠ ⁠What is SQL performance tuning, and why is it important?
2.⁠ ⁠How do you identify performance bottlenecks in SQL queries?
3.⁠ ⁠What are the most common causes of poor SQL performance?
4.⁠ ⁠How do you optimize SQL queries for better performance?
5.⁠ ⁠What is the difference between query optimization and indexing?

Indexing Questions:
1.⁠ ⁠What is indexing, and how does it improve query performance?
2.⁠ ⁠What types of indexes are available in SQL (e.g., clustered, non-clustered)?
3.⁠ ⁠How do you determine which columns to index?
4.⁠ ⁠What is the difference between index scanning and index seeking?
5.⁠ ⁠How do you maintain indexes for optimal performance?

Query Optimization Questions:
1.⁠ ⁠How do you optimize SQL queries using EXPLAIN/EXECUTION PLAN?
2.⁠ ⁠What is the difference between WHERE and HAVING clauses?
3.⁠ ⁠How do you optimize JOIN operations?
4.⁠ ⁠What is the impact of subqueries on performance?
5.⁠ ⁠How do you optimize aggregate functions (e.g., SUM, AVG, MAX).

Database Design Questions:
1.⁠ ⁠How does database design impact query performance?
2.⁠ ⁠What is normalization, and how does it affect performance?
3.⁠ ⁠How do you design tables for optimal performance?
4.⁠ ⁠What is the impact of data types on performance?
5.⁠ ⁠How do you optimize database schema for query performance.

Troubleshooting Questions:
1.⁠ ⁠How do you troubleshoot slow-running queries?
2.⁠ ⁠What tools do you use for SQL performance troubleshooting?
3.⁠ ⁠How do you identify locking and blocking issues?
4.⁠ ⁠What is the impact of deadlocks on performance?
5.⁠ ⁠How do you resolve query timeout issues.

Advanced Questions:
1.⁠ ⁠How do you optimize SQL queries for parallel processing?
2.⁠ ⁠What is the impact of data compression on performance?
3.⁠ ⁠How do you optimize SQL queries for cloud databases?
4.⁠ ⁠What is the difference between query store and plan cache?
5.⁠ ⁠How do you optimize SQL queries for real-time analytics.

Scenario-Based Questions:
1.⁠ ⁠You have a slow-running query that joins multiple tables. How would you optimize it?
2.⁠ ⁠Your database is experiencing high CPU usage. What would you do to resolve it?
3.⁠ ⁠You need to optimize a query that uses multiple subqueries. How would you approach it?
4.⁠ ⁠Your database is running out of disk space. What would you do to resolve it?
5.⁠ ⁠You need to optimize a query that uses aggregate functions. How would you approach it.

Behavioral Questions:
1.⁠ ⁠Can you describe a time when you optimized a slow-running SQL query?
2.⁠ ⁠How do you stay up-to-date with new SQL performance tuning techniques?
3.⁠ ⁠Can you walk me through your process for troubleshooting SQL performance issues?
4.⁠ ⁠How do you communicate performance tuning recommendations to stakeholders?
5.⁠ ⁠Can you describe a challenging SQL performance tuning project you worked on?