How to get running percentages or earliest date

Question

I have a table with MemberID, MemberName, ReportingQuarter, FinalExpectedDay. I need to find the earliest FinalExpectedDay where the Member will have completed 3/5 (60%), 4/5 (80%), 5/5 (90%).

Here is my data:

MemberID, MemberName, ReportingQuarter, FinalExpectedDay
1, A, 12/31/2022, 3/21/2023
1, A, 12/31/2022, 3/22/2023
1, A, 12/31/2022, 3/23/2023
1, A, 12/31/2022, 3/24/2023
1, A, 12/31/2022, 3/25/2023
2, B, 12/31/2022, 3/21/2023
2, B, 12/31/2022, 3/22/2023
2, B, 12/31/2022, 3/23/2023
2, B, 12/31/2022, 3/24/2023
2, B, 12/31/2022, 3/25/2023

My query so far:

SELECT MemberID, FinalExpectedDay, 100 as 'Percentile' 
FROM (
SELECT MemberID, FinalExpectedDay, Percentile, ROW_NUMBER () OVER (PARTITION BY MemberID ORDER BY Percentile asc) as PercentileRanking
FROM
(Select MemberID, FinalExpectedDay, PERCENT_RANK() OVER (PARTITION BY MemberID ORDER BY FinalExpectedDay ASC) as Percentile) PercentileRank
Where Percentile >=1)PercentileRanking = 1
)Y
Order by Y.MemberId, Percentile asc

PERCENT_RANK() doesn't work because the first row will be 0 instead of .20 for 20%.

How do I get to this:

MemberID, MemberName, ReportingQuarter, Percentile, FinalExpectedDay
1, A, 12/31/2022, 60, 3/23/2023
2, B, 12/31/2022, 60, 3/23/2023

Answer 1

SELECT MemberID, MemberName, ReportingQuarter, MIN(FinalExpectedDay) AS EarliestCompletionDay
FROM (
    SELECT MemberID, MemberName, ReportingQuarter, FinalExpectedDay, 
        ROW_NUMBER() OVER (PARTITION BY MemberID, ReportingQuarter ORDER BY FinalExpectedDay) AS RowNum,
        COUNT(*) OVER (PARTITION BY MemberID, ReportingQuarter) AS TotalRows
    FROM YourTable
) t
WHERE RowNum >= CEILING(TotalRows * 0.6)
GROUP BY MemberID, MemberName, ReportingQuarter

Should work for 60%