Blog

 DS : Advance SQL - Scenarios & Solution Note: Try to avoid  GROUP BY  clause to solve the problems For the problems use the  Health Insurance Claim  dataset. You can get the details as well as the dataset from  here . Problem 1:  What are the top 5 patients who claimed the highest insurance amounts? Problem 2:  What is the average insurance claimed by patients based on the number of children they have? Problem 3:  What is the highest and lowest claimed amount by patients in each region? Problem 4:  What is the percentage of smokers in each age group? Problem 5:  What is the difference between the claimed amount of each patient and the first claimed amount of that patient? Problem 6:  For each patient, calculate the difference between their claimed amount and the average claimed amount of patients with the same number of children. Problem 7:  Show the patient with the highest BMI in each region and their respective ran...

 Advance SQL What are Window Functions? Window functions in SQL are a type of analytical function that perform calculations across a set of rows that are related to the current row, called a "window". A window function calculates a value for each row in the result set based on a subset of the rows that are defined by a window specification. The window specification is defined using the OVER() clause in SQL, which specifies the partitioning and ordering of the rows that the window function will operate on. The partitioning divides the rows into groups based on a specific column or expression, while the ordering defines the order in which the rows are processed within each group. Data Set: CREATE TABLE marks ( student_id INTEGER PRIMARY KEY AUTO_INCREMENT , name VARCHAR ( 255 ), branch VARCHAR ( 255 ), marks INTEGER ) ; INSERT INTO marks ( name , branch , marks ) VALUES ( 'Nitish' , 'EEE' , 82 ), ( 'Rishabh' , 'EEE' , 91 ), (...