DS : Advance SQL - Scenarios & Solution

-

 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 rank.

Problem 8: Calculate the difference between the claimed amount of each patient and the claimed amount of the patient who has the highest BMI in their region.

Problem 9: For each patient, calculate the difference in claim amount between the patient and the patient with the highest claim amount among patients with the same bmi and smoker status, within the same region. Return the result in descending order difference.

Problem 10: For each patient, find the maximum BMI value among their next three records (ordered by age).

Problem 11: For each patient, find the rolling average of the last 2 claims.

Problem 12: Find the first claimed insurance value for male and female patients, within each region order the data by patient age in ascending order, and only include patients who are non-diabetic and have a bmi value between 25 and 30.


Solutions

For the problems use the Health Insurance Claim dataset. You can get the details as well as the dataset from here.


USE sql_tasks;

SELECT * FROM insurance;


-- 1. What are the top 5 patients who claimed the highest insurance amounts?

SELECT *,DENSE_RANK() OVER(ORDER BY claim DESC) FROM insurance LIMIT 5;


-- 2. What is the average insurance claimed by patients based on the 

--    number of children they have?

SELECT children,avg_claim FROM (SELECT *,

AVG(claim) OVER(PARTITION BY children) AS avg_claim,

ROW_NUMBER() OVER(PARTITION BY children) AS row_num

FROM insurance) t

WHERE t.row_num = 1;



-- 3. What is the highest and lowest claimed amount by patients in each region?

SELECT region,min_claim,max_claim FROM (SELECT *,

MIN(claim) OVER(PARTITION BY region) AS min_claim,

MAX(claim) OVER(PARTITION BY region) AS max_claim,

ROW_NUMBER() OVER(PARTITION BY region) AS row_num

FROM insurance) t

WHERE t.row_num = 1;



-- 4. What is the percentage of smokers in each age group?

SELECT * FROM insurance;



-- 5. What is the difference between the claimed amount of each 

--    patient and the claimed amount of the first patient?

SELECT *,

claim - FIRST_VALUE(claim) OVER() AS diff

FROM insurance;


-- 6. For each patient, calculate the difference between their claimed amount 

--    and the average claimed amount of patients with the same number of children.

SELECT *,

claim - AVG(claim) OVER(PARTITION BY children)

FROM insurance;


-- 7. Show the patient with the highest BMI in each region and their 

--    respective overall rank.

SELECT * FROM (SELECT *,

RANK() OVER(PARTITION BY region ORDER BY bmi DESC) AS group_rank,

RANK() OVER(ORDER BY bmi DESC) AS overall_rank

FROM insurance) t

WHERE t.group_rank = 1;


-- 8. Calculate the difference between the claimed amount of each patient 

--    and the claimed amount of the patient who has the highest BMI 

--    in their region.

SELECT *,

claim - FIRST_VALUE(claim) OVER(PARTITION BY region ORDER BY bmi DESC)

FROM insurance;




-- 9. For each patient, calculate the difference in claim amount 

--    between the patient and the patient with the highest claim amount

--    among patients with the and smoker status, 

--    within the same region. Return the result in descending order difference.

SELECT *,

(MAX(claim) OVER(PARTITION BY region,smoker) - claim) AS claim_diff

FROM insurance

ORDER BY claim_diff DESC;


-- 10. For each patient, find the maximum BMI value among their next three 

--     records (ordered by age).

SELECT *,

MAX(bmi) OVER(ORDER BY age ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING)

FROM insurance;


-- 11. For each patient, find the rolling average of the last 2 claims.

SELECT *,

AVG(claim) OVER(ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING)

FROM insurance;



-- 12. Find the first claimed insurance value for male and female patients, 

--     within each region order the data by patient age in ascending order, 

--     and only include patients who are non-diabetic and have a bmi value 

--     between 25 and 30.

WITH filtered_data AS (

SELECT * FROM insurance

    WHERE diabetic = 'No' AND bmi BETWEEN 25 AND 30

)


SELECT region,gender,first_claim FROM (SELECT *,

FIRST_VALUE(claim) OVER(PARTITION BY region,gender ORDER BY age) AS first_claim,

ROW_NUMBER() OVER(PARTITION BY region,gender ORDER BY age) AS row_num

FROM filtered_data) t

WHERE t.row_num = 1



















Comments

Post a Comment

Popular posts from this blog

A Road-Map to Become Solution Architect

-
A Road-Map to Become Solution Architect  Becoming a Solution Architect is an excellent career goal that requires a combination of technical expertise, problem-solving skills, and business acumen. Here's a roadmap to guide you on the journey: 1. Understand the Role of a Solution Architect A Solution Architect designs and oversees the implementation of systems or solutions to meet specific business needs. They ensure that the technology aligns with organizational goals, is scalable, secure, and meets performance standards. Key Responsibilities: Collaborate with stakeholders to understand business needs. Design technical architectures and solutions. Oversee the implementation of solutions by development teams. Ensure compliance with technical and business standards. Evaluate and recommend new tools, frameworks, and platforms. 2. Develop a Strong Foundation in Technology A Solution Architect must have a deep understanding of various technologies, frameworks, and methodologies. Key Sk...
Read more

Pickle Vs M, VS ONNX vs SavedModel vs TorchScript

-
  Pickle Vs M, VS  ONNX vs SavedModel vs TorchScript Let’s break it down in terms of purpose, use cases, compatibility, and safety : 🥒 Pickle (.pkl) Pickle is a Python-specific serialization format for objects, including machine learning models. Feature Details Use Case Serializing Python objects (including scikit-learn, XGBoost models) Compatibility Python-only (tight coupling with specific versions) Frameworks scikit-learn, XGBoost, LightGBM, etc. Speed Fast to load/save Portability ❌ Low — not portable across languages or platforms Security ⚠️ Unsafe to unpickle untrusted data (can execute arbitrary code) Deployment Typically for offline inference or Python-based pipelines ✅ Best For : Local development, internal tools, reproducible experiments ❌ Not Ideal For : Cross-platform deployment, mobile/edge/cloud scaling 🧠 TensorFlow SavedModel (MD) SavedModel is TensorFlow’s official format for storing trained models for production use. ...
Read more

Module 3: Fine-Tuning and Customizing Generative AI Models

-
  Module 3: Fine-Tuning and Customizing Generative AI Models Overview: In this module, we will explore how to fine-tune and customize pre-trained generative AI models to meet specific needs and tasks. Fine-tuning allows developers to adapt powerful, pre-existing models (such as GPT-3, BERT, or GANs) to domain-specific applications and data, which is often more efficient than training a model from scratch. You'll learn how to fine-tune various types of generative models for use cases like code generation, text generation, image synthesis, and more. By the end of this module, you will have the skills to tailor pre-trained models to your specific software development needs. Lesson 3.1: Understanding the Fine-Tuning Process 3.1.1: What is Fine-Tuning? Fine-tuning is the process of taking a pre-trained model and adapting it to a specific task or dataset. Pre-trained models are typically trained on large, diverse datasets (e.g., GPT-3 trained on vast text data). Fine-tuning helps ...
Read more