Question 1: Customers Who Never Order

Write an SQL query to report all customers who never order anything.

Return the result table in any order.

Difficulty level: Easy

Solution Overview:
So, to solve the problem we need to use LEFT JOIN, WHERE clause. By the right use of these two, we can easily get the final result.

Solution Framework:
In this problem, we have two tables with customer details and another with orders. All customers might order a product or not. We want to get the name of the customers who haven’t ordered anything yet. At first, we used the left join to join the Orders table with the Customers table.

Note: A left join takes all the values from the left table and only the common rows from the right table.

The left join was performed on the Customer table because we want all the Customers with their Orders. Now, as we have all the customers with their orders we will find some null CustomerId in the order table who haven’t ordered anything yet. So, we have used the where clause to get the records for that use, as the where clause filters out the rows or takes the rows that we want.

Then we have selected the name column from the Customers table as we only want the name of the customers.

Step 1

Here we have at first selected all values from the Customers and Orders table by the * notation to see the values of each column. We can see the Customers table has the id and names of the customers for 4 customers. The Orders table has 2 orders with order id and customer id for 2 customers.

Step 2

In this step, we have performed a left join to join the Orders table with the Customers table. The left join was performed on the Customer table because we want all the Customers with their Orders. Now, as we have all the Customers with their orders we will find some null CustomerId in the order table who haven’t ordered anything yet. In the output, we can see id 2 and 4 have Null values in the customerId.

Step 3

In the last step, we have used the where clause to take only the Customers who have null customerId in the Orders table from step 2 Output.

Question 2: Second Highest Salary

Write an SQL query to report the second highest salary from the Employee table. If there is no second highest salary, the query should report null.

Difficulty level: Medium

Solution Overview:

This problem was a bit tougher as we needed to use an OFFSET clause that is not used frequently in SQL. We have used this clause to restrict the first row. Other than that, IFNULL was an important function in this query, without this it would not be possible to show null values if there was no Second Highest Salary.

Solution Framework:

In this problem, we have to find the Second Highest Salary from a table where we have the id and salary of the Employees. To do this, we have at first ordered the salary in descending order to get the highest salary in the first and take only the unique salaries by using DISTINCT argument.

So, we have the second Salary in Second place. To get the second salary from the second place we have used two SQL arguments. The first important one is the OFFSET. The OFFSET argument is used to identify the starting point to return rows from a result set.

Basically, it excludes the first set of records. So as we have written 1 after offset it will exclude the first row from the overall result. Then we have a LIMIT argument. The SQL LIMIT clause restricts how many rows are returned from a query.

So we will get only the first one after excluding the highest salary by OFFSET argument. So, essentially we are getting the Second Highest Salary. Then we use the IFNULL function because it returns null if there is no value. So, if there is no Second Highest Salary we will get null as a result.

Step 1

In the first step, we have got all the unique salaries by the DISTINCT argument. Then ordered all the salaries by using ORDER BY and DESC arguments.

Step 2

Then in the second step, we have used a LIMIT and OFFSET argument. Here the OFFSET argument will work first to exclude the first row i.e the Highest Salary. Then the LIMIT argument will work to get the first row after excluding the first row i.e. we will get the second row which holds the second highest salary.

Step 3

In the third and last step, we have added the IFNULL function to get null values if there is no Second Highest Salary present in the result.

Question 3: Human Traffic of Stadium

Write an SQL query to display the records with three or more rows with consecutive id’s, and the number of people is greater than or equal to 100 for each.

Return the result table ordered by visit_date in ascending order.

Difficulty level: Hard

Solution Overview:

This was definitely a difficult problem where we needed to use self join, a complex where clause, and needed to remove the duplicate rows using DISTINCT.

Solution Framework:

The output of this problem demands to have the id’s where the number of people will be greater than 100 and there should have at least 3 consecutive id’s.

So, the first trick of this problem is to join the table by itself three times as we have to check three consecutive IDs. Then we are using the where clause to take only the rows where the number of people>100.

Then, we are checking for the consecutiveness of ids. So taking the temporary table t1 in the left, middle, and right we are checking the difference between the id of the other tables which is helping us to check the consecutiveness of the ids. Then we used the DISTINCT clause to take only distinct rows as we might get duplicate rows for the self join.

Note: Here self join means joining one table with itself.

Step 1

The first step is to get the days with 100 people and join this table with itself so that in the next step we can check for consecutiveness of the ids. From the output, we will see there are 6 days with more than 100 people. So there are 216 records in total after the self-join as there are 6 rows in the table, so joining in three times with each row will make it 6*6*6. The first 3 columns are from t1, the next 3 ones are from t2, and the last 3 are from t3.

Step 2

Considering t1, t2 and t3 are identical, we can take one of them to consider what conditions we should add to filter the data and get the final result.

Taking t1 for example, it could exist in the beginning of the consecutive 3 days, or the middle, or the last.

• t1 in the beginning: (t1.id – t2.id = 1 and t1.id – t3.id = 2 and t2.id – t3.id =1) — t1, t2, t3

• t1 in the middle: (t2.id – t1.id = 1 and t2.id – t3.id = 2 and t1.id – t3.id =1) — t2, t1, t3

• t1 in the end: (t3.id – t2.id = 1 and t2.id – t1.id =1 and t3.id – t1.id = 2) — t3, t2, t1

So, these conditions will help us to check the consecutiveness of the id’s. We are making one id constant and checking whether the other 2 id have a difference of 1 and 2 with the constant id. If this is so, then we have got the consecutiveness.

Step 3

After the second step we can see there are some duplicate rows that we should get rid of. So we have used DISTINCT to take only the unique rows from t1.