Conquer Your Data Engineering Interview: Top 12 SQL Problems Solved

Understanding Different Types of SQL Joins

Joins are the bedrock of relational database querying, allowing you to combine rows from two or more tables based on a related column. In data engineering interviews, a deep understanding of INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), and FULL OUTER JOIN is essential. An INNER JOIN returns only the rows where the join condition is met in both tables. For instance, if you have an 'employees' table and a 'departments' table, an INNER JOIN on 'department_id' would show only employees who are assigned to a valid department. A LEFT JOIN, conversely, returns all rows from the left table and the matched rows from the right table. If there's no match in the right table, NULL values are returned for its columns. This is useful for finding employees who haven't been assigned a department. A RIGHT JOIN is the mirror image of a LEFT JOIN. A FULL OUTER JOIN returns all rows when there is a match in either the left or the right table. If there is no match, the missing side's columns will have NULL values. Consider a scenario with 'orders' and 'customers' tables. A LEFT JOIN from 'customers' to 'orders' would list all customers, including those who haven't placed any orders yet. Understanding the nuances of when to use each join type, and how they affect the result set, is critical. Often, interviewers present scenarios where you need to identify records present in one table but not another, or to combine data from multiple sources accurately. Practicing these join types with real-world examples, such as merging employee data with their respective project assignments or customer purchase histories with product details, will solidify your understanding. For example, imagine you have two tables: Customers (CustomerID, Name) and Orders (OrderID, CustomerID, OrderDate). To find all customers and their orders, using a LEFT JOIN Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID would show all customers, with NULLs for order details if they haven't ordered. This is a fundamental concept tested in many entry-level data engineering roles, including those advertised by companies like Wipro and Cognizant.

Mastering Aggregate Functions and Group By

Aggregate functions in SQL allow you to perform calculations across a set of rows and return a single value. Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX. These are almost always used in conjunction with the GROUP BY clause. The GROUP BY clause groups rows that have the same values in specified columns into summary rows, like 'find the total sales per city' or 'count the number of employees in each department'. For example, if you have a 'Sales' table with columns like 'SaleID', 'ProductID', 'Quantity', and 'SaleDate', you might be asked to calculate the total quantity sold for each product. The query would look something like: SELECT ProductID, SUM(Quantity) AS TotalQuantity FROM Sales GROUP BY ProductID. Another common interview problem involves finding the average salary per department. Assuming you have an 'Employees' table with 'EmployeeID', 'Department', and 'Salary' columns, the query would be: SELECT Department, AVG(Salary) AS AverageSalary FROM Employees GROUP BY Department. When using GROUP BY, it's important to remember that any column in the SELECT list that is not an aggregate function must be included in the GROUP BY clause. This ensures that the aggregation is performed correctly for each distinct group. Interviewers often use these concepts to assess your ability to summarize and analyze data. They might ask you to find the number of customers who made purchases in a specific month, or the maximum order value for each customer. Understanding how to combine these aggregate functions with WHERE clauses to filter data before aggregation, or HAVING clauses to filter groups after aggregation, is also crucial. For instance, to find departments with an average salary greater than 50,000, you would use: SELECT Department, AVG(Salary) AS AverageSalary FROM Employees GROUP BY Department HAVING AVG(Salary) > 50000. This demonstrates a deeper understanding beyond basic aggregation.

Solving Problems with Subqueries and Correlated Subqueries

Subqueries, also known as inner queries or nested queries, are queries embedded within another SQL query. They can be used in the WHERE clause, FROM clause, or SELECT clause. Subqueries are powerful tools for breaking down complex problems into smaller, manageable parts. A common use case is finding records that meet certain criteria based on data from another table. For example, to find all employees whose salary is greater than the average salary of all employees, you would use a subquery: SELECT EmployeeName, Salary FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees). This is a straightforward subquery. Correlated subqueries are a more advanced type, where the inner query depends on the outer query for its execution. The inner query is executed once for each row processed by the outer query. While powerful, they can sometimes be less efficient than joins or other methods. An example could be finding employees who earn more than the average salary in their respective departments: SELECT e1.EmployeeName, e1.Salary, e1.Department FROM Employees e1 WHERE e1.Salary > (SELECT AVG(e2.Salary) FROM Employees e2 WHERE e2.Department = e1.Department). Here, the inner query recalculates the average for each department as the outer query iterates through employees. Interviewers often pose problems that require identifying duplicate records, finding the Nth highest salary, or selecting records that don't exist in another table, all of which can be solved effectively using subqueries. Understanding the difference between correlated and non-correlated subqueries, and knowing when to use them versus joins, is a key differentiator in data engineering interviews. Mastering these techniques is vital for roles at companies like Accenture and Capgemini.

Leveraging Window Functions for Advanced Analytics

Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions that collapse rows into a single output row, window functions retain the individual rows while producing aggregated values. This makes them incredibly powerful for tasks like ranking, calculating running totals, and finding moving averages. Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), and aggregate functions used with the OVER() clause. For instance, finding the Nth highest salary in each department is a classic problem perfectly suited for window functions. Using RANK() or DENSE_RANK() with an OVER() clause partitioned by department and ordered by salary descending allows you to assign a rank to each employee within their department. SELECT EmployeeName, Department, Salary, RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) as SalaryRank FROM Employees. Another common task is calculating a running total. If you have a table of daily sales, you can calculate the cumulative sales up to each day using the SUM() window function: SELECT SaleDate, DailySales, SUM(DailySales) OVER (ORDER BY SaleDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM DailySales. The LAG() and LEAD() functions are useful for comparing a row with a previous or next row. For example, you could calculate the difference in sales between consecutive days. Understanding the PARTITION BY and ORDER BY clauses within the OVER() clause is crucial. PARTITION BY divides the rows into partitions (similar to GROUP BY), and ORDER BY specifies the order of rows within each partition. These functions are increasingly important in modern data engineering roles, enabling sophisticated data analysis directly within the database, a skill highly valued by companies like Tech Mahindra.

Identifying and Handling Duplicate Records

Duplicate records can skew analysis and lead to incorrect insights. Identifying and handling them is a fundamental data cleaning task that often appears in SQL interview questions for data engineers. A common approach involves using the ROW_NUMBER() window function or a combination of GROUP BY and COUNT. To identify duplicate rows based on all columns, you can use: SELECT , ROW_NUMBER() OVER(PARTITION BY col1, col2, ..., colN ORDER BY col1) as rn FROM your_table WHERE rn > 1. This query assigns a unique row number to each row within partitions defined by the combination of specified columns. Rows with rn greater than 1 are duplicates. Alternatively, using GROUP BY and HAVING: SELECT col1, col2, ..., COUNT() FROM your_table GROUP BY col1, col2, ... HAVING COUNT() > 1. This query returns the combinations of values that appear more than once. Once identified, duplicates can be deleted. A common method uses a Common Table Expression (CTE) with ROW_NUMBER(): WITH RowNumCTE AS ( SELECT , ROW_NUMBER() OVER(PARTITION BY col1, col2, ..., colN ORDER BY col1) as rn FROM your_table ) DELETE FROM RowNumCTE WHERE rn > 1. Interviewers might present scenarios where you need to remove duplicates from a staging table before loading it into a data warehouse or to ensure data integrity in a customer table. Understanding different strategies for identifying duplicates (e.g., based on a subset of columns vs. all columns) and the implications of different deletion methods is key. This skill is directly applicable to real-world data pipelines and is frequently assessed by companies like IBM.

Finding the Nth Highest Salary/Value

The 'Nth highest salary' problem is a classic SQL interview question that tests your understanding of subqueries, window functions, and sometimes even self-joins. It's a common way for interviewers to gauge your problem-solving skills beyond basic SELECT statements. Using Subqueries: One approach involves using a subquery to find the salary that is greater than exactly N-1 other salaries. SELECT Salary FROM Employees e1 WHERE N-1 = (SELECT COUNT(DISTINCT Salary) FROM Employees e2 WHERE e2.Salary > e1.Salary). This query finds the Nth distinct salary. To find the Nth highest salary (allowing for ties), you might need a slightly more complex approach. Using Window Functions (Recommended): This is generally the most efficient and readable method. Using the DENSE_RANK() window function is ideal because it assigns consecutive ranks without gaps, even if there are ties. WITH RankedSalaries AS ( SELECT EmployeeName, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) as dr FROM Employees ) SELECT EmployeeName, Salary FROM RankedSalaries WHERE dr = N. For example, to find the 3rd highest salary: WITH RankedSalaries AS ( SELECT EmployeeName, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) as dr FROM Employees ) SELECT EmployeeName, Salary FROM RankedSalaries WHERE dr = 3. Interviewers might ask for the second highest salary, the third most expensive product, or the fifth most frequent customer. The core concept remains the same: ranking data and selecting the desired element. Understanding the difference between RANK(), DENSE_RANK(), and ROW_NUMBER() is crucial here, as each handles ties differently. This problem is a staple in technical interviews across the board, including those conducted by major IT service companies in India.

Practical Application: Simulating Interview Scenarios

To truly excel in your data engineering interview, theoretical knowledge isn't enough; you need practical application. Simulating interview scenarios helps bridge this gap. Imagine you're applying for a role at a fintech startup or a large e-commerce platform in India. They might present you with a scenario involving customer transaction data. For instance, 'Given a table of customer transactions with columns like CustomerID, TransactionID, Amount, and TransactionDate, write a query to find the top 3 customers who spent the most in the last quarter.' This requires combining filtering (last quarter), aggregation (SUM of Amount per CustomerID), ordering (DESC by total amount), and limiting (TOP 3). The query might look like: SELECT CustomerID, SUM(Amount) AS TotalSpent FROM Transactions WHERE TransactionDate BETWEEN 'start_of_quarter' AND 'end_of_quarter' GROUP BY CustomerID ORDER BY TotalSpent DESC LIMIT 3. Another scenario could involve analyzing website user activity logs. 'Find the number of unique users who visited the site on each day of the week.' This involves extracting the day of the week from a timestamp, grouping by it, and counting distinct user IDs. Prepgenix AI specializes in creating these realistic interview simulations. Our platform offers a vast array of practice problems, covering everything from basic joins to complex window function applications, tailored to the Indian job market. By practicing on Prepgenix AI, you can build the muscle memory and confidence needed to tackle unexpected questions during your actual interview, whether it's for a product-based company like Zoho or a service-based giant. We focus on common patterns seen in placements and hiring drives, ensuring your preparation is relevant and effective. Remember, interviewers aren't just looking for correct syntax; they're assessing your thought process, your ability to translate business requirements into SQL queries, and your understanding of performance implications. Practicing with diverse problems helps you develop this holistic approach.

Frequently Asked Questions