## Register Now

It will take less than 1 minute to register for lifetime. Bonus Tip - We don't send OTP to your email id Make Sure to use your own email id for free books and giveaways

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``````

 Restaurant Price Lead Oliver’s 2000 3000 PBN 3000 4000 Social 4000 5000 Harry’s 5000 Null
```SELECT Restaurant,
Price,
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 FirstFrom 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_nameORDER BY column_nameOFFSET rows_to_skipFETCH NEXT number_of_rows ROWS ONLY;`

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

`Select rollno, count (rollno) from StudentGroup by rollnoHaving count (rollno)>1Order 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