Sql Interview Questions

82 real Sql questions asked in live technical interviews — each with a model answer. Updated weekly.

🎤 Auto-captured by Assisting AI during live interviews

These Sql interview questions were captured from real interviews by candidates using Assisting AI. Each links to a full model answer. For real-time help during your own interview, get Assisting AI from ₹500/day.

What does this SQL query do?🟢 Easy · Conceptual · Asked 2×Why should you avoid using SELECT * in SQL queries?🟢 Easy · Conceptual · Asked 2×Can you write a SQL query or a Python script to find all duplicate rows in a tickets table?🟡 Medium · Coding · Asked 1×Given a table with columns ID, student, name, and email, how would you handle duplicate names?🟡 Medium · Conceptual · Asked 1×What is the difference between COUNT(*) and COUNT(email) in SQL?🟢 Easy · Conceptual · Asked 1×What is the difference between ROW_NUMBER, DENSE_RANK, and RANK window functions?🟡 Medium · Conceptual · Asked 1×Why should the GROUP BY clause include the distance column instead of destination in this query?🟡 Medium · Conceptual · Asked 1×How can you use a window function with DENSE_RANK to find the second highest salary in each department?🟡 Medium · Conceptual · Asked 1×How can I implement the given SQL query using PySpark instead of SQL?🟡 Medium · Coding · Asked 1×How do you use a window function with DENSE_RANK to rank salaries within each department and filter for rank 2?🟡 Medium · Conceptual · Asked 1×How can you use a window function to retrieve the second highest salary per department?🟡 Medium · Conceptual · Asked 1×What is the difference between a left join and a right join?🟢 Easy · Conceptual · Asked 1×How would you optimize a SQL query in Snowflake that involves multiple joins?🟡 Medium · Conceptual · Asked 1×Give me a brief on how a SQL join works.🟢 Easy · Conceptual · Asked 1×Given the following table of customer events, write a SQL query to find the most recent status for each customer.🟡 Medium · Coding · Asked 1×Can you tell me the difference between RANK() and DENSE_RANK()?🟢 Easy · Conceptual · Asked 1×What other window functions are you aware of?🟢 Easy · Conceptual · Asked 1×How would you retrieve the top two orders by amount for each store for a given month?🟡 Medium · Coding · Asked 1×How would you design a customer table with CustomerID as the primary key and columns for customer name, phone, email, address, and last updated date?🟡 Medium · Conceptual · Asked 1×In ETL pipeline design, what are the most critical aspects you focus on to track data lineage, and can you write an SQL query to implement a Slowly Changing Dimension (SCD) Type 2?🟡 Medium · Coding · Asked 1×Given a denormalized dataset, how would you extract the desired columns or transform it into a normalized form using Python, Spark, or SQL?🟡 Medium · Coding · Asked 1×Write a SQL query to find duplicate employee IDs in the employee table, keep one record, and delete the remaining duplicates.🟡 Medium · Coding · Asked 1×Write a SQL query to find duplicate records in the employee table.🟢 Easy · Coding · Asked 1×Can you write a query to find duplicate records in the impertable table?🟢 Easy · Coding · Asked 1×Two people run the exact same query on the same table, but one of them takes far longer than the other. How could that happen?🟡 Medium · Conceptual · Asked 1×How many tables were you responsible for, roughly, and how big was the biggest one?🟢 Easy · Behavioral · Asked 1×You've written a query that returns the correct result but runs too slowly within the time window you have. Walk me through what you do, in order, from the moment you decide it's too slow.🟡 Medium · Debugging · Asked 1×The same query on the same table ran in 30 seconds last week but takes 3 minutes today, with no changes to the query. Where do you look?🟡 Medium · Debugging · Asked 1×You have a query with four nested subqueries that is hard to read. How would you restructure it?🟡 Medium · Conceptual · Asked 1×You need each customer's three most recent orders. How would you write that query?🟡 Medium · Coding · Asked 1×How would you write a SQL query to group each user's logins into consecutive‑day runs (gaps and islands) and then find the longest run per user?🟡 Medium · Conceptual · Asked 1×How do you perform a full outer join on `txn_id` and flag rows where either side is missing?🟡 Medium · Conceptual · Asked 1×Explain the SQL query that reconciles transactions from tables txn_a and txn_b.🟡 Medium · Conceptual · Asked 1×Explain the SQL query that breaks down transaction differences between tables txn_a and txn_b.🟡 Medium · Conceptual · Asked 1×Explain the recursive CTE query that calculates total salary per employee hierarchy, line by line.🟡 Medium · Conceptual · Asked 1×Explain the workflow of the recursive CTE query that calculates total salary per employee hierarchy.🟡 Medium · Conceptual · Asked 1×Explain Q3, the recursive CTE that walks the org hierarchy and sums salaries.🟡 Medium · Conceptual · Asked 1×What are the SQL queries to answer the following three questions: (1) Retrieve employee and manager names, showing 'TBO' if no manager; (2) List employees earning more than the average salary; (3) Get manager level and total salary for their hierarchy.🟡 Medium · Coding · Asked 1×How can I modify the query for Q3 to return only direct reports, not the entire hierarchy?🟡 Medium · Coding · Asked 1×What are the SQL queries to answer the following three questions: (1) Retrieve manager name, or 'TBD' if missing; (2) List employees earning more than the average salary; (3) Get manager name and total salary of their hierarchy, including the manager, but only if the total exceeds 100k.🟡 Medium · Coding · Asked 1×What are the SQL queries to answer the following three questions: (1) Retrieve employee and manager names, showing 'TBO' if no manager; (2) List employees earning more than the average salary; (3) Get manager salary at their level and total salary of their hierarchy.🟡 Medium · Coding · Asked 1×Explain the SQL queries for the following two questions: (1) Retrieve distinct student_name, class, and section; (2) Find student names that appear more than once in the table.🟡 Medium · Coding · Asked 1×How can you find customers who have data for all 12 months in a year using SQL?🟡 Medium · Conceptual · Asked 1×When implementing slowly changing dimensions, how do you decide whether to count changes that occur within seven days versus those that occur after seven days? Also, can you walk through the SQL syntax you would use for handling SCD type 2?🟡 Medium · Conceptual · Asked 1×How would you add a column that calculates the rolling average of revenue per transaction timestamp, given that you only have a transaction timestamp column and no order date?🟡 Medium · Coding · Asked 1×How would you compute a 7‑day rolling average of daily revenue when there can be multiple transactions per day, using transaction timestamps?🟡 Medium · Coding · Asked 1×How would you modify your query to compute a 30‑day rolling average of daily revenue when you only have a transaction timestamp column and no order date?🟡 Medium · Coding · Asked 1×Write an SQL query to compute a 7‑day rolling average of daily revenue using a window function.🟡 Medium · Coding · Asked 1×What is the purpose of the load_ts column in this query, and how does it differ from transaction_ts?🟢 Easy · Conceptual · Asked 1×How does this query calculate the rolling 7‑day average revenue using a window function?🟡 Medium · Conceptual · Asked 1×How would you join these two tables, and what would the resulting dataset contain?🟡 Medium · Conceptual · Asked 1×How would you design a time series database for product sales and store data, and how would you retrieve daily, monthly, and weekly summaries?🟡 Medium · Conceptual · Asked 1×What are the ways you can use to optimize a slow running query?🟡 Medium · Conceptual · Asked 1×Write a SQL query to retrieve the top 3 customers by total purchase amount in the last 6 months.🟡 Medium · Conceptual · Asked 1×Using the query SELECT salary FROM (SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees) t WHERE rn = 3; how would you modify it to correctly handle duplicate salaries when retrieving the third highest salary?🟡 Medium · Conceptual · Asked 1×Given the same query, how would you adjust it to account for duplicate salaries when selecting the third highest salary?🟡 Medium · Conceptual · Asked 1×Given the list of salaries [500, 300, 300, 400, 400, 200], what would the ROW_NUMBER() values be if you order by salary descending?🟢 Easy · Conceptual · Asked 1×How would you retrieve the top 3 salaries from the employees table?🟢 Easy · Conceptual · Asked 1×How would you find the third highest salary in the employees table, not department-wise?🟡 Medium · Conceptual · Asked 1×How would you restrict the MCP to only allow predefined safe SQL operations?🟡 Medium · Conceptual · Asked 1×Would you allow your MCP to execute arbitrary SQL against a connected SQL database?🟡 Medium · Conceptual · Asked 1×What is the error in the following SQL query that finds employees working on projects greater than or equal to the average number of projects per employee?🟢 Easy · Debugging · Asked 1×What is a better approach to find employees working on projects greater than or equal to the average number of projects per employee?🟡 Medium · Conceptual · Asked 1×What is the best SQL query you would write to solve this problem?🟡 Medium · Conceptual · Asked 1×How would you write a SQL query to find the first and last purchase dates for each customer-product pair, ensuring that each pair has at least two completed orders?🟡 Medium · Coding · Asked 1×Write a SQL query that returns the first and last purchase dates for each customer-product pair, only including pairs with at least two completed orders.🟡 Medium · Coding · Asked 1×Describe how you would use a CTE and window functions to rank purchases by date and then filter for the first and last occurrences for each customer-product pair.🟡 Medium · Coding · Asked 1×Explain why you should use 'IS NOT NULL' instead of 'ISNULL' in SQL queries.🟢 Easy · Conceptual · Asked 1×Why do we use a calendar function?🟢 Easy · Conceptual · Asked 1×How do you calculate a running total in SQL?🟢 Easy · Conceptual · Asked 1×In SQL code, how would you write a query to find the second highest salary?🟡 Medium · Coding · Asked 1×Which type of join will you use?🟢 Easy · Conceptual · Asked 1×Can you go ahead and write a query on the left side?🟡 Medium · Coding · Asked 1×Can you tell me what the result will be from the data?🟢 Easy · Conceptual · Asked 1×Explain the following SQL query: SELECT customer_name, COUNT(*) FROM customerTable WHERE CITY_Name LIKE '%Pu%'?🟢 Easy · Conceptual · Asked 1×What will be the result of the query?🟢 Easy · Conceptual · Asked 1×Given the table of user interactions with actions 'sent' and 'accepted', how would you find pairs of users who have not performed both actions on the same day?🟡 Medium · Coding · Asked 1×Initially, we will aggregate the data. If we look at the data, we see that four requests were sent on January 4, 2020. How would you aggregate this data to analyze request patterns?🟡 Medium · Conceptual · Asked 1×Why did you use ORDER BY in your experience query?🟡 Medium · Debugging · Asked 1×Why did you use PARTITION BY by industry?🟡 Medium · Behavioral · Asked 1×Write a SQL query to select the top 10 users by order count.🔴 Hard · Coding · Asked 1×What is the correct method to achieve the highest sales rank, dense rank, or row number?🟡 Medium · Conceptual · Asked 1×

🎤 Get Sql questions answered in real-time

Assisting AI listens to your interview, captures questions live, and gives you instant AI-powered answers on a discreet on-screen overlay.

Get Assisting AI — Starts at ₹500

Browse Other Topics