Advanced SQL Query Interview Questions

All 82 questions

1. Write a SQL query to find the second-highest distinct salary.

   Asked in 13 of 612 interviews · last asked Oct 22 · Write code · Junior

   Show answer

   I find the highest salary below the maximum salary. This gives the second-highest distinct salary, so duplicates do not affect the result. A window function is another option. Both queries below ignore NULL salaries and return NULL if fewer than two distinct salaries exist.

   • Use a strict less-than comparison to exclude every occurrence of the highest salary.
   • DENSE_RANK gives equal salaries the same rank, without gaps.
   • ROW_NUMBER can count duplicate salaries separately and give the wrong result.

   SQLCopy

   SELECT MAX(salary) AS second_highest_salary
   FROM employees
   WHERE salary < (SELECT MAX(salary) FROM employees);
   WITH ranked AS (
     SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
     FROM employees
     WHERE salary IS NOT NULL
   )
   SELECT MAX(salary) AS second_highest_salary
   FROM ranked WHERE salary_rank = 2;

   What the interviewer checks: The interviewer checks whether you can find a distinct salary rank while handling duplicates and missing results.

   Other ways it was asked

   • “Write an SQL query to find the third-highest employee salary.”
   • “Write a MySQL query to get the second highest salary from an employee table with only one salary column.”
   • “Write a SQL query to find employees earning the second-highest salary in each department.”

2. What are SQL window functions, and can you give examples of when to use them?

   Asked in 12 of 612 interviews · last asked Oct 7 · Concept · Mid-level

   Show answer

   SQL window functions calculate values across related rows without combining them into one row. They use an OVER clause to define those rows and their order. Common uses include ranking sales with RANK, calculating running totals with SUM, and comparing consecutive sales with LAG.

   • Ranking functions, such as ROW_NUMBER and RANK, assign positions; RANK gives equal values the same rank.
   • Aggregate window functions, such as SUM and AVG, calculate totals or averages while keeping individual rows.
   • Value and offset functions, such as FIRST_VALUE and LAG, return values from other rows.
   • PARTITION BY defines groups; window ORDER BY defines calculation order, not final output order.

   SQLCopy

   SELECT sale_id, region, sale_date, amount,
          RANK() OVER (
              PARTITION BY region ORDER BY amount DESC) AS sale_rank,
          SUM(amount) OVER (
              PARTITION BY region ORDER BY sale_date, sale_id
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
          LAG(amount) OVER (
              PARTITION BY region ORDER BY sale_date, sale_id) AS previous_amount
   FROM sales;

   What the interviewer checks: The interviewer checks whether you can choose a window function and explain how it preserves individual rows.

   Other ways it was asked

   • “What are SQL window functions, and when would you use them?”
   • “What types of SQL window functions do you know?”
   • “What are SQL window functions, and when would you use one?”

3. What is a common table expression, and when would you use one?

   Asked in 12 of 612 interviews · last asked Sep 25 · Concept · Junior

   Show answer

   A common table expression (CTE) is a named query defined in a WITH clause that you can reference within one SQL statement. I use CTEs to break complex queries into clear steps. Recursive CTEs can handle hierarchies, such as employee reporting chains.

   • A CTE exists only within its statement; it is not a permanent table.
   • You can reference a CTE more than once in the same statement.
   • A CTE does not guarantee faster queries; execution depends on the database and query.
   • Recursive CTE syntax and limits vary by database.

   SQLCopy

   WITH department_totals AS (
       SELECT department_id, SUM(salary) AS total_salary
       FROM employees
       GROUP BY department_id
   )
   SELECT department_id, total_salary
   FROM department_totals
   WHERE total_salary > 100000;

   What the interviewer checks: The interviewer is checking whether you understand CTE scope and how CTEs make complex SQL easier to read.

   Other ways it was asked

   • “What are common table expressions (CTEs), and how can they be used instead of subqueries?”
   • “What are SQL common table expressions (CTEs), and when would you use them?”
   • “What is a common table expression (CTE), and how is it used in a SQL query?”

4. Write an SQL query to find the highest-paid employee in each department.

   Asked in 7 of 612 interviews · last asked Sep 17 · Write code · Junior

   Other ways it was asked

   • “How would you write a SQL query to find the names of employees who receive the second highest salary in each department?”
   • “Given sales, orders, and employees tables, write a SQL query to find the employee who generated the highest sales amount in each department.”
   • “Write SQL to find the top three highest paid employees in each department.”

5. How would you find duplicate records in SQL based on selected columns?

   Asked in 7 of 612 interviews · last asked Oct 17 · Tooling · Junior

   Other ways it was asked

   • “How would you identify duplicate records in a SQL table?”
   • “How can you use SQL to identify duplicate records in a table?”
   • “How would you find duplicate records in an employees table using SQL?”

6. What is the difference between RANK and DENSE_RANK?

   Asked in 6 of 612 interviews · last asked Sep 21 · Concept · Junior

   Other ways it was asked

   • “What is the difference between ROW_NUMBER(), DENSE_RANK(), and RANK() in SQL?”
   • “What is the operational difference between ROW_NUMBER, RANK, and DENSE_RANK?”
   • “Provide a quick example of the operational differences between ROW_NUMBER, RANK, and DENSE_RANK window functions.”

7. What is a subquery, and how would you use one?

   Asked in 4 of 612 interviews · last asked Sep 22 · Concept · Junior

   Other ways it was asked

   • “What is a subquery in SQL, and when would you use one?”
   • “What is a subquery in SQL?”
   • “What is a subquery?”

8. How would you delete duplicate rows from a SQL table while keeping one copy?

   Asked in 4 of 612 interviews · last asked Oct 19 · Write code · Junior

   Other ways it was asked

   • “How would you delete duplicate rows from a product table based on product ID?”
   • “Can you write a SQL query to delete duplicate rows?”
   • “How do you remove duplicate records from a database table using SQL?”

9. How would you use SQL to find the latest record for each user?

   Asked in 3 of 612 interviews · last asked Oct 7 · Write code · Junior

   Other ways it was asked

   • “How would you find the latest record per user in SQL?”
   • “How can you use SQL to retrieve the latest record for each employee?”
   • “How can you retrieve the latest record for an employee ID using SQL?”

10. What is the difference between a CTE and a subquery?

    Asked in 3 of 612 interviews · last asked Sep 18 · Concept · Junior

    Other ways it was asked

    • “What is the difference between a subquery and a CTE?”
    • “Why do we need CTEs, and how are they different from subqueries?”
    • “Which is more efficient: a CTE or a subquery?”

11. Why would you use DENSE_RANK() to find the third-highest salary in each department?

    Asked in 2 of 612 interviews · last asked Sep 10 · Concept · Mid-level

    Other ways it was asked

    • “Why might you choose DENSE_RANK() instead of ROW_NUMBER() or RANK() to find the top three highest-paid employees in each department?”
    • “Why might you use ROW_NUMBER() to find the second-highest salary in each department?”

12. Write a SQL query to find customers whose total spending exceeds the average total spending per customer.

    Asked in 2 of 612 interviews · last asked Sep 17 · Write code · Mid-level

    Other ways it was asked

    • “How would you write a SQL query to find customers whose total order amount is greater than the average customer spend?”

13. How would you bring each customer's latest login date from a table with multiple records per customer into a first table?

    Asked once · last asked Jun 17 · Scenario · Junior

    Other ways it was asked

    • “How would you write a SQL query to bring each customer's latest login date from a table with multiple records per customer into a first table?”

14. How would you use a SQL window function to calculate the maximum total amount for each user ID?

    Asked once · last asked Sep 22 · Write code · Mid-level

    Other ways it was asked

    • “How does a MAX() window function calculate the maximum total amount for each user ID across rows?”

15. How would you use SQL to uncover clinical insights that inform decision-making?

    Asked once · last asked Feb 11 · Behavioral · Mid-level

    Other ways it was asked

    • “Describe a project where you used SQL to uncover a critical clinical insight that influenced decision-making.”

16. Write a SQL query using the Student and Marks tables to find the top 10 Class 10 students in History, where each student's subject score is the average of their three highest marks across five attempts.

    Asked once · last asked Apr 8 · Write code · Mid-level

17. Write a SQL query to show how many users posted each number of tweets in 2022.

    Asked once · last asked Apr 14 · Write code · Mid-level

18. Write a query to detect duplicate rows in stg.Orders. Which fields define a logical duplicate, and how would you distinguish a true duplicate from a valid repeated rate?

    Asked once · last asked Apr 28 · Write code · Mid-level

19. How would you structure a 100-line SQL query to make it more readable?

    Asked once · last asked May 19 · Tooling · Mid-level

20. What is the difference between common table expressions (CTEs) and temporary tables, and which do you prefer?

    Asked once · last asked May 19 · Concept · Mid-level

21. Write SQL to identify customer IDs whose transactions are above the average for the last six months.

    Asked once · last asked Jun 6 · Write code · Mid-level

22. Write a SQL query to find duplicate employer IDs for a given pay date.

    Asked once · last asked Jun 24 · Write code · Mid-level

23. In SQL, how would you compute an uplift metric by segment using treatment and control groups, and return the point estimate plus a 95% confidence interval?

    Asked once · last asked Jun 30 · Write code · Senior

24. For the 95% confidence interval in SQL, what standard error formula would you use for the difference between two conversion proportions, and what assumptions does it rely on?

    Asked once · last asked Jun 30 · Concept · Mid-level

25. When computing a confidence interval in BigQuery or Athena, what SQL unit-of-analysis strategy would you use to ensure it is based on unique users and not biased by repeated events?

    Asked once · last asked Jun 30 · Scenario · Senior

    Other ways it was asked

    • “When computing this in a cloud analytics pipeline such as BigQuery or Athena, how would you ensure the confidence interval is based on unique users and not biased by repeated events? What unit-of-analysis strategy would you use in SQL?”

26. Write a SQL query that removes duplicate rows while keeping the latest record for each ID.

    Asked once · last asked Jul 24 · Write code · Mid-level

27. How can the ROW_NUMBER() window function be used to identify consecutive streaks in SQL?

    Asked once · last asked Aug 11 · Concept · Mid-level

28. How would you write a SQL query to find the missing order IDs in a sequence?

    Asked once · last asked Aug 11 · Write code · Mid-level

29. What is the difference between rank, dense rank, and row number, and when should we use row number?

    Asked once · last asked Aug 14 · Concept · Mid-level

30. How are window functions different from aggregate functions in SQL? Can you give an example?

    Asked once · last asked Aug 20 · Concept · Mid-level

31. Write a SQL subquery to find which manager each employee reports to, showing their names.

    Asked once · last asked Aug 20 · Write code · Junior

32. Write a SQL query to identify the customers who placed orders on consecutive days.

    Asked once · last asked Aug 20 · Write code · Mid-level

33. In SQL, what is the difference between IN and EXISTS?

    Asked once · last asked Aug 20 · Concept · Mid-level

34. Describe a complex SQL Server task you worked on and how you approached it.

    Asked once · last asked Aug 31 · Behavioral · Senior

35. Given an employee table with duplicate records, write a BigQuery query to deduplicate it.

    Asked once · last asked Sep 2 · Write code · Mid-level

36. Given an employee table with user sessions, write a query to find users who purchased on seven consecutive days.

    Asked once · last asked Sep 2 · Write code · Mid-level

37. What's the difference between IN, EXISTS, and an inner join? When would you choose each one in production?

    Asked once · last asked Sep 4 · Concept · Mid-level

38. Can you write an example for row_number, rank, and dense_rank in SQL Server?

    Asked once · last asked Sep 5 · Write code · Mid-level

39. Write a SQL query to return the latest record for each order based on the timestamp.

    Asked once · last asked Sep 7 · Write code · Mid-level

40. How would you deduplicate a huge production SQL table with billions of records while maintaining high availability and controlling resource usage?

    Asked once · last asked Sep 7 · Scenario · Senior

41. How would you remove duplicate records from a SQL table while preserving one original record?

    Asked once · last asked Sep 8 · Scenario · Mid-level

42. How would you read pipe-delimited files and use an efficient SQL join to find employees with the third-highest salary in each department?

    Asked once · last asked Sep 10 · Write code · Mid-level

    Other ways it was asked

    • “How would you read pipe-delimited files and use an efficient SQL join to find the third-highest salary in each department?”

43. Can you differentiate between rank and dense rank?

    Asked once · last asked Sep 10 · Concept · Junior

44. How would you find the inventory on the first and last day of each month for every distinct product, plant, and store combination?

    Asked once · last asked Sep 16 · Write code · Mid-level

45. Can you walk me through a specific example where your data cleaning, SQL, and dashboard skills significantly impacted a project or decision?

    Asked once · last asked Sep 18 · Behavioral · Mid-level

46. Write a SQL query to find duplicate email IDs in a table.

    Asked once · last asked Sep 22 · Write code · Junior

47. Write a DELETE query to remove duplicate email records while keeping one copy.

    Asked once · last asked Sep 22 · Write code · Mid-level

48. Write a SQL query to calculate each category’s sales contribution percentage within its year.

    Asked once · last asked Sep 22 · Write code · Mid-level

49. What is the difference between scalar and multi-row subqueries?

    Asked once · last asked Sep 22 · Concept · Junior

50. What does a SQL subquery that returns the maximum total amount across an entire table do?

    Asked once · last asked Sep 22 · Concept · Junior

51. When should you use a SQL window function instead of a subquery?

    Asked once · last asked Sep 22 · Concept · Mid-level

52. How does MAX() as a window function behave differently from a regular aggregate in a SQL query?

    Asked once · last asked Sep 22 · Concept · Mid-level

53. How would you use a SQL window function to filter for the second-highest total amount?

    Asked once · last asked Sep 22 · Write code · Mid-level

54. Can you explain what a window function is and describe its types?

    Asked once · last asked Sep 22 · Concept · Junior

55. What are value functions in SQL window functions?

    Asked once · last asked Sep 22 · Concept · Mid-level

56. Can you write a SQL query to find the top five salaries in a department?

    Asked once · last asked Sep 26 · Write code · Junior

57. How would you rank regions by total spending, and which SQL function would you use?

    Asked once · last asked Sep 29 · Concept · Mid-level

    Other ways it was asked

    • “How would you rank regions by total sales, and which SQL function would you use?”

58. Write a SQL query to rank each customer's total spend within their region.

    Asked once · last asked Sep 29 · Write code · Mid-level

59. Write a SQL query that groups rows and returns an aggregate, such as COUNT, for each group, then numbers or sorts the results.

    Asked once · last asked Oct 2 · Write code · Mid-level

60. In SQL, how would you ensure that only the latest version is included when the same store, SKU, and day combination arrives more than once due to reprocessing?

    Asked once · last asked Oct 2 · Write code · Senior

61. When writing a query to rank users by spending, how would you handle nulls, users with no orders, and ties?

    Asked once · last asked Oct 8 · Write code · Mid-level

62. How would you write a SQL query to report monthly revenue by user signup cohort?

    Asked once · last asked Oct 8 · Write code · Mid-level

63. How would you select the latest order for each user if multiple orders share the same date?

    Asked once · last asked Oct 8 · Write code · Mid-level

64. If you need to compare this month's sales with last month's sales, how would you approach it in SQL?

    Asked once · last asked Oct 17 · Scenario · Mid-level

65. Write a query to return each customer's most recent successful event, including the customer ID, event ID, and event type.

    Asked once · last asked Oct 18 · Write code · Mid-level

66. When would you use ROW_NUMBER in SQL?

    Asked once · last asked Oct 18 · Concept · Mid-level

67. Given a table with 100 million records, how would you use SQL to select each customer's latest event, and why might you use ROW_NUMBER instead of MAX?

    Asked once · last asked Oct 18 · Write code · Senior

68. How would you handle ties in timestamps when selecting each customer's latest event?

    Asked once · last asked Oct 18 · Scenario · Mid-level

69. What are SQL window functions, common table expressions (CTEs), and nested subqueries, and when would you use each?

    Asked once · last asked Jan 13 · Concept · Junior

70. Can you provide an example of a nested query?

    Asked once · last asked Jul 16 · Write code · Junior

    Other ways it was asked

    • “Can you write an example of a nested SQL query?”
    • “What is a nested query?”

71. What is the syntax for writing a SQL window function?

    Asked once · last asked Aug 25 · Concept · Junior

72. Give an example of an analytic function.

    Asked once · last asked Sep 4 · Concept · Junior

73. What is the LEAD function?

    Asked once · last asked Sep 4 · Concept · Junior

74. Is the SQL LEAD function a window function or a general-purpose function?

    Asked once · last asked Sep 4 · Concept · Junior

75. What is a recursive query in SQL?

    Asked once · last asked Sep 11 · Concept · Junior

76. How would you write a SQL CTE using sales and product tables in a star schema?

    Asked once · last asked Sep 14 · Write code · Mid-level

77. How do you determine which columns a SQL subquery needs to return?

    Asked once · last asked Sep 18 · Concept · Mid-level

78. Why do we use subqueries in SQL?

    Asked once · last asked Sep 18 · Concept · Junior

79. Can you write a query to fetch the last five records from a table?

    Asked once · last asked Sep 18 · Write code · Junior

80. When querying duplicate email IDs in a table, what columns should the result include?

    Asked once · last asked Sep 22 · Write code · Junior

81. What are the types of subqueries?

    Asked once · last asked Sep 22 · Concept · Junior

82. Could you take me through window functions in dbt?

    Asked once · last asked Oct 2 · Concept · Junior

More SQL interview questions

• All SQL interview questions
• SQL Basics Interview Questions
• Database Design Interview Questions
• SQL Joins and Aggregation Interview Questions
• SQL Views and Stored Procedures Interview Questions
• SQL Query Optimization and Indexing Interview Questions
• SQL Transactions and Concurrency Interview Questions
• Database Integration and Security Interview Questions
• Database Administration and Scaling Interview Questions

How this list is made

Every question here was asked by a real interviewer in a real job interview that a candidate ran MeetAssist in. After the call, the interviewer's questions are pulled from the transcript. Practice sessions, tests, exams and anything that was not a job interview are dropped.

Questions are rewritten so they make sense on their own: names of people, employers and clients are removed, and speech-to-text mistakes are fixed. Small talk, logistics and questions about one candidate's own résumé are left out. Rephrasings of the same question are merged, and the count is the number of different interviews it came up in.

The interviews come from candidates who use MeetAssist, so they lean towards software, data and AI roles: the shares describe these interviews, not every SQL interview. Answers to the most asked questions are written and checked by an editor; a "common mistake" is shown only when reviews of real answers in these interviews show it, with the count.

612 interviews, 357 candidates, Nov 2025 to Oct 2026. Updated every month with new interviews.