HCA Healthcare's authorization audit needs a derived score for each completed request code. For a code such as 367, calculate 3^1 + 6^2 + 7^3 = 382, where each digit is raised to its one-based position.
Write a PostgreSQL query that joins HCA request, patient, and facility data, calculates the score, and ranks requests within each active facility.
Requirements
- Include only requests with
status = 'Completed', an active facility, and a three-digit numeric request_code.
- Split each code into digits and raise each digit to its one-based position.
- Return the request, patient, facility, calculated score, and descending score rank within the facility.
- Use CTEs and a window function, and sort by facility name, score descending, and request ID.
Representative data
