Advanced SQL interview questions for Data Engineers

SQL is one of the most favorite topics in Data Engineering interviews because Data Engineers should not only be proficient in Programming but should be able to write simple or advanced sql queries, Data Modelling, and Pipeline Design.

In this post, we will discuss the possible advanced sql questions asked in Data Engineering Interviews.

Advanced SQL Interview Questions

1.  What is the difference between GROUP BY and PARTITION BY?

GROUP BY	PARTITION BY
GROUP BY returns only one row after aggregating columns for each group.	PARTITION BY gives aggregated columns for each record in the table.
The number of rows in the table is reduced.	The number of rows in the table remains the same.
It is an aggregation function.	It is an analytic function.
GROUP BY does not allow to add the columns that are not a part of the GROUP BY clause.	With the PARTITION BY clause, we can add any columns.

2.  How to Transpose the table in SQL?

Transposing the table is a criterion where the rows are changed to columns and vice-versa. This SQL question is one of the most important SQL interview questions asked in the Data Engineering Interviews The transposition of the table can be achieved using PIVOT in SQL. PIVOT rotates a table-valued expression by taking the unique values from the columns and creating multiple columns in the tables. Let's look at an example to understand that

Input Table

Student Id	Subjects	Marks
1980	Maths	45
1980	English	40
1981	Maths	48
1981	English	45
1980	Biology	41
1981	Biology	45

Output Table

Student ID	Maths	English	Biology
1980	45	40	41
1981	48	45	45

Syntax

SELECT first_column AS <first_column_alias>,
[pivot_value1], [pivot_value2], ... [pivot_value_n]
FROM 
(<source_table>) AS <source_table_alias>
PIVOT 
(
 aggregate_function(<aggregate_column>)
 FOR <pivot_column>
 IN ([pivot_value1], [pivot_value2], ... [pivot_value_n])
) AS <pivot_table_alias>;

3.  What is the difference between RANK and DENSE_RANK?

RANK	DENSE_RANK
RANK gives the ranking of records within the partition.	DENSE_RANK also gives the ranking of records within the partition, but the criteria for ranking are different than RANK.
Ties are assigned the same rank in RANK.	Ties are assigned consecutive ranks in DENSE_RANK.
The next rank after the tie is skipped in RANK.	No ranks are skipped in DENSE_RANK.

4.  What is the difference between UNION AND UNION ALL?

UNION ALL	UNION
Concatenate the tables that have a similar structure.	Similar to UNION ALL it concatenates the table.
It directly joins the tables without removing duplicates.	It removes the duplicated records before taking the Union of the tables.
High Performance.	Low Performance.

5.  What is the difference between Clustered and Non-Clustered Index?

Indexing is a strategy that provides a quick lookup of data in the columns of the table and an index is a structure that can help in the faster retrieval of the data. There are mainly four types of indexes

• Unique Index
• Non-Unique Index
• Clustered Index
• Non-Clustered Index

Most of the inteviewers in Data Engineering Interviews ask questions related to Clustered Index and Non-Clustered Index. Below is the difference between Clustered Index and Non-Clustered Index

Clustered Index	Non-Clustered Index
Modifies the way records are stored in the database based on indexed columns.	Creates a separate entity within the tables which references the original table.
It is used for the easy and speedy retrieval of data.	It is relatively slower as compared to the Clustered Index.
There can be only one Clustered Index per table.	There can be multiple Non-Clustered Indexes in a single table.

6.  What is the difference between Zero and NULL values?

It is not actually a trick question, but during interviews when you are already nervous or anxious, you can easily get stuck at this one. NULL values are definitely different than Zero because NULL basically refers to the empty or missing value. It is not Zero it simply doesn't exist.

7.  What is the difference between DELETE and TRUNCATE?

DELETE	TRUNCATE
DELETE statement deletes one or more rows in the table based on certain conditions.	TRUNCATE deletes the whole content of the table, keeping the schema unaffected.
WHERE clause can be used with DELETE.	WHERE clause cannot be used with TRUNCATE.
DELETE is slower because it maintains logs.	TRUNCATE is faster because it doesn't maintain any logs.
Rollback is possible in DELETE.	Rollback is not possible in TRUNCATE.
DELETE takes more space.	TRUNCATE takes less space.

8.  What are the window functions and how to use them?

Window functions are used to calculate the aggregation of the values in the columns over the rows. So how is it different from GROUP BY? GROUP BY basically performs the aggregation over all the rows in the table whereas window functions performs aggreation over a set of rows and return the values for each of the rows. As a Data Engineer or a Data Analyst you should know about window functions as they are really useful for doing Business Analysis and estimations. Below is the syntax of the window functions.

Syntax

window_function() --can be any aggregation function
OVER (   

       --optional arguments
       [ <PARTITION BY clause> ]  
       [ <ORDER BY clause> ]   
       [ <ROW or RANGE clause> ]  
      )  

Window functions use the OVER() and PARTITION BY() clause in the queries. Let's understand each of them individually.

• Over: Specifies the clause for the window function.
• Partition By: Also explained above in the article, it divides the rows into partitions or frames for the aggregation of the rows
• Order By: It is used to define the order of the rows within the partition
• Row or Range: It limits the number of rows within the partition based on the start and the end values in the partition

Let's look at an example to understand that

Input Table (Student)

Student Id	Subjects	Marks
1980	Maths	45
1980	English	40
1981	Maths	48
1981	English	45
1980	Biology	41
1981	Biology	45
1982	Biology	41
1982	English	43
1982	Maths	45

And now if we use the below query on the above table we will get the output as

Query

SELECT 
   Student Id, 
   Subject, 
   SUM(Marks) as Total Sum 
OVER (PARTITION BY Student Id ORDER BY Student Id) 
FROM Student

Output Table

Student ID	Subject	Total Marks
1980	Maths	126
1980	English	126
1980	Biology	126
1981	Maths	138
1981	English	138
1981	Biology	138
1982	Biology	129
1982	English	129
1982	Maths	129

9.  Write an SQL to create a Binary Search Tree

Often companies ask to write advanced sql queries for the Data structure problems in interviews. One such problem is writing an SQL for creating a Binary Search Tree. Writing Binary Tree in any programming language is simple. But how to do that in SQL? Let's look at the Input Table and then try to visualize Binary Search Tree and write SQL for it.

 
Here is the input table below called BST

Input Table (BST)

Node	Parent
1	2
3	2
6	8
9	8
2	5
8	5
5	NULL

Let's construct the Binary Search Tree using the above input table, this would look something like this 

Now, let's go ahead and write the SQL Query for it.

SELECT Node,
CASE WHEN Parent is NULL THEN "Root"
     WHEN Node IN (SELECT DISTINCT Parent 
     FROM BST) THEN "Inner"
     ELSE "Leaf"
END AS output FROM BST ORDER BY Node ASC

10.  Write an SQL to find the consecutive number in the table

These types of problems are often asked in advanced sql interview questions and can be solved using Lag and Lead SQL functions. Let's look at those briefly.

LAG()

The LAG function is used to access the value in a different row above the current row. From which row the value will be accessed can be specified by the offset parameter of the function. Let's look at its syntax

LAG(expression [,offset[,default_value]]) OVER(ORDER BY columns)

It takes three arguments

• Column Name: The column from where the value is obtained.
• Offset: The number of rows to skip above the current row.
• Default Value: The default value is returned when the obtained value is Empty.

LEAD()

The LEAD function is used to access the value in a different row below the current row. From which row the value will be accessed can be specified by the offset parameter of the function. It has syntax similar to LAG()

Now let's write an SQL Query for the input table called Logs

Input Table (Logs)

id	number
1	1
2	1
3	1
4	2
5	1
6	2
7	2

SELECT DISTINCT t.number AS consecutive_nums
FROM (
SELECT number, LAG(number) OVER() AS lag_num, LEAD(number) OVER() AS lead_num
FROM Logs
) as t
WHERE t.number = t.lag_num AND t.number = t.lead_num