In the previous module, you learnt about databases and their various constituents. You also understood the steps involved in designing a database, which are as follows:
- Ideating and designing an ERD.
- Adding a database schema, its various tables, the values in each table and the constraints.
- Querying a database using several SQL statements to extract valuable insights from the data.
So far, you have covered all the basic concepts of SQL. However, you should also be aware of some advanced concepts of SQL such as window functions, query optimisation, case statements, stored functions and cursors and the best practices for writing queries. These are useful tools for handling several use cases and will help you solve complex questions easily.
In the previous module, you learnt how to order employees by their salary in an example data set. One method to do this would be to categorise them by their values. For example, you can divide them into the following categories:
- Employees earning less than ₹2,50,000 per year.
- Employees earning greater than or equal to ₹2,50,000 per year
The first category of employees would be exempted from paying any tax and need not go through some of the additional checks that the second category of employees would be required to go through. You can solve this problem statement using a filter condition in a ‘where’ clause.
Now, imagine you are working in a bank and need to identify and classify your customers on the following criteria:
- Top 10% of customers: Platinum.
- Next 10% of customers: Gold.
- Next 20% of customer: Silver.
- Rest of the customers: Regular.
Your bank intends to roll out different schemes for these classes of customers in exchange for their loyalty. But how do you solve such a seemingly complex problem statement? You will find the answer to this question in the upcoming sessions.
In this session
You will be introduced to the concept of windowing functions. You will learn about the ‘over’and ‘partition’clauses used to implement windowing. After going through this session, you should be to able to use window functions such as rank(), dense_rank() and percent_rank() in your queries.
You will also be introduced to the concept of named windows. You will learn about framesand how they move within a window. Next, you will be learnt about the various applications of windowing, including one to calculate an element known as a moving average. Finally, you will learn about the ‘lead’ and ‘lag’ functions that are used to fetch data from succeeding and preceding rows, respectively.
In the upcoming video, you will get a brief glance at the topics that will be covered in this module.
Before proceeding, make sure you download and run the script file given below. This will take care of the database creation, table creation and value insertion for the ‘market star’ schema.
People you will hear from in this session
Professor and Dean (Academics), IIIT-B
The International Institute of Information Technology, Bangalore, popularly known as IIIT Bangalore, is a premier national graduate school in India. Founded in 1999, it offers Integrated M.Tech., M.Tech., M.S. (Research) and PhD programs in the field of Information Technology.
Lead Business Analyst, Swiggy
Shreyas has completed Bachelor of Engineering from PES Institute of Technology – West Campus, Bengaluru (now PES University). He leads the analytics division for New Initiatives at Swiggy, which involves coming up with various strategies to increase brand awareness. He has worked in both consulting and product-based roles. His areas of technical expertise include SQL, Excel, PowerBI, Tableau, Qlikview and R.