Calculate 90th continuous percentile of each region | SQL | SQL Interview question

DML Script: datasculptor.b...
Feedback: forms.gle/NQuC...
The Data Sculptor UA-cam channel is a dedicated platform that explores the art and science of working with data. This channel caters to a diverse audience, including data enthusiasts, analysts, scientists, and anyone interested in harnessing the power of data for insightful and creative purposes.
The channel covers a wide range of topics related to data manipulation, analysis, visualization, and interpretation. The term "Data Sculptor" emphasizes the creative aspect of working with data, suggesting that it's not just about raw numbers but also about crafting meaningful stories and insights from the information available.
Viewers can expect a variety of content, including tutorials on popular data analysis tools and programming languages, discussions on emerging trends in the data field, case studies showcasing innovative data applications, and tips for effective data communication. The Data Sculptor aims to demystify complex concepts, making data-related skills accessible to both beginners and experienced professionals.
Whether you're a student looking to enter the field of data science, a business professional seeking to enhance your analytical skills, or simply someone curious about the world of data, the Data Sculptor UA-cam channel strives to provide engaging and informative content to help you navigate the exciting realm of data exploration and analysis.
Playlists:
SQL Interview Questions: • SQL Interview questions
Recursive CTE: • Recursive CTE
Power BI: • Power BI and DAX
Data Modeling: • Data Modelling
Generative AIs: • Generative AIs
Excel: • Excel
Follow me
Linkedin: / data-sculptor-93a00b2a8
Instagram: / datasculptor2895

[@srivalli5269]

Well explained without predefined functions

[@sahitikarnatakapu2829]

Great sir.. You are intelligent.

[@vijaygupta7059]

Thanks for new function, I have learn new function today

[@datasculptor2895]

Glad to hear that

[@Factstelugu5]

sql intemediate advanced topics videos/resources cheyyandi bro

[@datasculptor2895]

Sure.. definitely. Please subscribe to my channel.

[@sravankumar1767]

SELECT
Region,
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY Amount)
OVER (PARTITION BY Region) AS percentile_90
FROM
table_name;

[@datasculptor2895]

Yes. Same is mentioned in the video.

[@sravankumar1767]

SELECT
Region,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Amount)
OVER (PARTITION BY Region) AS median_amount
FROM
Sales;

[@datasculptor2895]

Yes. Same is mentioned in the video.

[@UnrealAdi]

MySQL:
WITH RankedData AS (
SELECT
Region,
revenue,
ROW_NUMBER() OVER (PARTITION BY Region ORDER BY revenue) AS rn,
COUNT(*) OVER (PARTITION BY Region) AS cnt
FROM sales2
),
PercentilePositions AS (
SELECT
Region,
revenue,
rn,
cnt,
0.9 * (cnt - 1) + 1 AS percentile_pos
FROM RankedData
),
InterpolatedData AS (
SELECT
Region,
revenue AS lower_revenue,
LEAD(revenue) OVER (PARTITION BY Region ORDER BY revenue) AS upper_revenue,
rn,
FLOOR(percentile_pos) AS lower_rn,
CEIL(percentile_pos) AS upper_rn,
percentile_pos
FROM PercentilePositions
)
SELECT *,
CASE
WHEN lower_rn = upper_rn THEN lower_revenue
ELSE lower_revenue + (upper_revenue - lower_revenue) * (percentile_pos - lower_rn)
END AS percentile_90
FROM InterpolatedData
WHERE rn = lower_rn -- OR rn = upper_rn
ORDER BY Region;

[@datasculptor2895]

Is this giving the correct result?