Top Percentile Fraud - Practice Coding Problems

Column Name	Type
policy_id	int
state	varchar
fraud_score	int

Input & Output

example_1.sql — Basic Case

$ Input: Fraud table: | policy_id | state | fraud_score | |-----------|-------|-------------| | 1 | CA | 75 | | 2 | CA | 85 | | 3 | CA | 90 | | 4 | CA | 95 | | 5 | NY | 70 | | 6 | NY | 80 | | 7 | NY | 88 | | 8 | NY | 92 |

› Output: | policy_id | state | fraud_score | |-----------|-------|-------------| | 4 | CA | 95 | | 8 | NY | 92 |

💡 Note: For CA with 4 records, top 5% means the highest scoring record (95). For NY with 4 records, top 5% is also the highest record (92). Results are ordered by state ASC, fraud_score DESC.

example_2.sql — Multiple Top Records

$ Input: Fraud table: | policy_id | state | fraud_score | |-----------|-------|-------------| | 1 | TX | 60 | | 2 | TX | 65 | | 3 | TX | 70 | | 4 | TX | 75 | | 5 | TX | 80 | | 6 | TX | 85 | | 7 | TX | 90 | | 8 | TX | 95 | | 9 | TX | 97 | | 10 | TX | 99 |

› Output: | policy_id | state | fraud_score | |-----------|-------|-------------| | 10 | TX | 99 |

💡 Note: TX has 10 records, so top 5% includes 1 record (10 * 0.05 = 0.5, rounded up to 1). The highest fraud score is 99 with policy_id 10.

example_3.sql — Tie-breaking by Policy ID

$ Input: Fraud table: | policy_id | state | fraud_score | |-----------|-------|-------------| | 101 | FL | 88 | | 102 | FL | 92 | | 103 | FL | 95 | | 104 | FL | 95 | | 105 | WA | 85 | | 106 | WA | 90 | | 107 | WA | 93 | | 108 | WA | 93 |

› Output: | policy_id | state | fraud_score | |-----------|-------|-------------| | 103 | FL | 95 | | 104 | FL | 95 | | 107 | WA | 93 | | 108 | WA | 93 |

💡 Note: FL has 4 records, top 5% includes the top record(s) with score 95. Both policies 103 and 104 have the same score, so both are included and ordered by policy_id ASC. Same logic applies to WA state.

Constraints

1 ≤ Number of records ≤ 10⁵
1 ≤ policy_id ≤ 10⁶
1 ≤ fraud_score ≤ 100
Each policy_id is unique
State names are valid US state abbreviations

Visualization

Tap to expand

Understanding the Visualization

Group by State

Partition records by state, like separating students by grade level

Calculate Rankings

Use PERCENT_RANK() to determine where each fraud score falls within its state

Filter Top 5%

Select only records with percentile_rank >= 0.95 (top 5 percentile)

Apply Final Ordering

Sort by state ASC, fraud_score DESC, policy_id ASC for consistent results

Key Takeaway

🎯 Key Insight: Window functions with PARTITION BY allow us to calculate percentiles within groups efficiently, making this perfect for state-wise ranking problems. The PERCENT_RANK() function handles the complex math automatically!

Asked in

G Google 25 a Amazon 18 ⊞ Microsoft 15 f Meta 12

The optimal solution uses window functions with PERCENT_RANK() OVER (PARTITION BY state ORDER BY fraud_score ASC) to efficiently calculate percentile ranks within each state group. Filter records where percentile_rank >= 0.95 to get the top 5%, then apply the required ordering. This approach achieves O(n log n) time complexity with minimal space usage.

Common Approaches

Approach	Time	Space	Notes
✓ Two-Pass Window Functions	O(n log n)	O(n)	First calculate percentile ranks, then filter top 5% records
Single Query Window Functions (Optimal)	O(n log n)	O(1)	Calculate percentile ranks and filter in one optimized query
Brute Force (Nested Subqueries)	O(n²)	O(1)	Use nested subqueries to count records and calculate percentile for each state

Two-Pass Window Functions — Algorithm Steps

Calculate PERCENT_RANK() for each record within its state group
Store intermediate results with percentile ranks
Filter records where percentile rank >= 0.95 (top 5%)
Order final results by state, fraud_score, and policy_id

Visualization

Tap to expand

Step-by-Step Walkthrough

Calculate Ranks

Use PERCENT_RANK() within each state partition

Filter Top 5%

Select records with percentile_rank >= 0.95

Code -

solution.c — C

/* SQL solution using two-pass window functions */
const char* query = """
WITH ranked_fraud AS (
    SELECT 
        policy_id,
        state,
        fraud_score,
        PERCENT_RANK() OVER (
            PARTITION BY state 
            ORDER BY fraud_score ASC
        ) as percentile_rank
    FROM Fraud
)
SELECT policy_id, state, fraud_score
FROM ranked_fraud
WHERE percentile_rank >= 0.95
ORDER BY state ASC, fraud_score DESC, policy_id ASC;
""";

/* Function to execute optimized query */
int execute_optimized_query(sqlite3* db) {
    return sqlite3_exec(db, query, callback, 0, NULL);
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Window function requires sorting within each group, plus filtering pass

⚡ Linearithmic

Space Complexity

O(n)

Need to store intermediate results with percentile ranks

⚡ Linearithmic Space

23.4K Views

Medium Frequency

~25 min Avg. Time

847 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Two-Pass Window Functions — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler