SQL: Complex Expression Result

HardSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is SQL: Complex Expression Result. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Problem

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

request_id	request_code	status	facility_id
101	367	Completed	1
102	153	Completed	1
103	407	Completed	2
105	222	Pending	3
107	999	Completed	4
112	12	Completed	1

Schema

authorization_requests

Column	Type	Description
request_idPK	INT	Unique authorization request identifier
patient_id	INT	Referenced patient
facility_id	INT	Referenced HCA Healthcare facility
request_code	VARCHAR(20)	Authorization code to score
status	VARCHAR(20)	Current request status

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient display name

facilities

Column	Type	Description
facility_idPK	INT	Unique facility identifier
facility_name	VARCHAR(120)	HCA Healthcare facility name
is_active	BOOLEAN	Whether the facility is active for reporting

Tablesauthorization_requestspatientsfacilities

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results

SQL: Complex Expression Result

HardSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is SQL: Complex Expression Result. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Problem

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

request_id	request_code	status	facility_id
101	367	Completed	1
102	153	Completed	1
103	407	Completed	2
105	222	Pending	3
107	999	Completed	4
112	12	Completed	1

Schema

authorization_requests

Column	Type	Description
request_idPK	INT	Unique authorization request identifier
patient_id	INT	Referenced patient
facility_id	INT	Referenced HCA Healthcare facility
request_code	VARCHAR(20)	Authorization code to score
status	VARCHAR(20)	Current request status

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient display name

facilities

Column	Type	Description
facility_idPK	INT	Unique facility identifier
facility_name	VARCHAR(120)	HCA Healthcare facility name
is_active	BOOLEAN	Whether the facility is active for reporting

Tablesauthorization_requestspatientsfacilities

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results