SQL advance concepts

Advance concepts mostly involves knowledge of window functions, cleaning the data format, working with different date formats, etc. Before proceeding, you should actually go through different ways of casting data types and other widows functions.


1.Create the cumulative revenue column on a restaurant data where the table contains revenue of each of the restaurants and you are supposed to create another column having running sum

select R1.RestaurantId, R1.Revenue, SUM(R2.Revenue) as Cumulative_Revenue
from Restaurant R1
inner join Restaurant R2 
on R1.RestaurantId >= R2.RestaurantId
group by 1,2
order by 1

2. What is the difference between group by and partition by?
Group by and partition by both are used on the complete dataset and is primarily used to apply functions on a different groups of the table.

On one hand, group by aims at reducing the number of rows of the table, but partition by does not reduce the number of rows.

Group by modifies the query but partition by works with ranking functions like row number(), rank(),dense_rank()

3. What are the different value related window function?

Lead() – The LEAD() window function returns the value for the row after the current row in a partition. If no row exists, null is returned
Lag() – The LAG() window function returns the value for the row before the current row in a partition. If no row exists, null is returned
Last_Value() – The FIRST_VALUE window function returns the value of the specified expression with respect to the first row in the window frame
First_Value() – The LAST_VALUE window function returns the value of the specified expression with respect to the last row in the window frame.

4. Use Lag function in a query to get the Lag column

Restaurant	Price	Lag
Oliver’s	2000	Null
PBN	3000	2000
Social	4000	3000
Harry’s	5000	4000

SELECT Restaurant,
Price, 
lag(Price,1) over (Restaurant) as Lag 
from Restaurant

5. Use the Lead function to get a lead column

Restaurant	Price	Lead
Oliver’s	2000	3000
PBN	3000	4000
Social	4000	5000
Harry’s	5000	Null

SELECT Restaurant,
Price, 
lead(Price,1) over (Restaurant) as Lead 
from Restaurant

6. Show the use of First_Value()

Restaurant	Id	Price	First
Oliver’s	1	2000	2000
PBN	1	3000	2000
Social	2	4000	4000
Harry’s	2	5000	4000
To Be Continued	2	6000	4000
Red Rhino	3	7000	7000
Rasta Cafe	3	8000	7000

Select Restaurant,
Id,
Price,
First_Value(Price) Over(Partition By Id) as First
From Restaurant

7. What are the aggregate window function?

Sum()
Average()
Count()
Min()
Max()
We are not going to discuss the definition or syntax of these functions

8. What are the ranking functions?
Ranking functions are important to keep in your mind. The most used ranking functions are:-

1. RANK()
2. DENSE_RANK()
3. ROW_NUMBER()
4. NTILE()

9. Which of the following group function ignores NULL value?(Tredence)
MAX
COUNT
SUM
All of the above

A. All of the above

10. What is the use of FETCH command?(Flipkart)
The FETCH argument is used to return a set of number of rows. FETCH can’t be used itself, it is used in conjuction with OFFSET.

SELECT column_name(s)
FROM table_name
ORDER BY column_name
OFFSET rows_to_skip
FETCH NEXT number_of_rows ROWS ONLY;

11. How to find count of duplicate rows?(Flipkart)

Select rollno, count (rollno) from Student
Group by rollno
Having count (rollno)>1
Order by count (rollno) desc;

12. You already know how to get the maximum and second maximum from a table. You also have a fair bit of idea to fetch the maximum from each department(as in form a table of employees with their department present in another column). Now fetch the second highest salary from each department

Select Dept, Max(Salary)
From (Select E1.Dept, E1.Salary From Emp E,Select (Dept,Max(Salary) as salary from Emp group by Dept)E1
Where E.Dept = E1.Dept
And (E.Salary < E1.Salary) X
Group By Dept

We will add more questions in the upcoming articles.

Keep Learning 🙂

The Data Monk