Top-Decile Applicants by High School Class Rank
Scenario
The Scholarship Committee builds the Presidential Scholarship pool every cycle from one rule: every applicant the university has admitted who finished in the top 10% of their high school class. Class rank is recorded on the application, in dbo.ods_applications.appl_hs_class_rank_pct, and it is a percentile measured FROM THE TOP — 1.10 means the applicant sat 1.1% of the way down their graduating class, which is close to valedictorian, and 84.80 means near the bottom. On this column smaller is better, which is the opposite of the instinct a column named "rank" usually triggers. "Admitted" means appl_current_status is one of 'AC' (Accepted), 'DP' (Deposited) or 'MS' (Move to Student). All three are admit decisions; 'DN' (Denied), 'WD' (Withdrawn) and 'NEW' (no decision yet) are not. Get the applicant's name from dbo.ods_person by joining p.id = a.appl_applicant, and return the plain-English decision from a.current_status_desc so the committee can see who has already committed. Alias appl_applicant as applicant_id and the concatenated name as applicant_name. One thing to think about before you write the filter: 41 of the 200 applications carry no class rank at all, because small high schools and many private schools do not rank their students.
Expected output
15 rows — one per admitted applicant who finished in the top 10% of their high school class, best rank first. Columns: applicant_id (aliased from appl_applicant), applicant_name (first_name || ' ' || last_name from dbo.ods_person), appl_hs_class_rank_pct, and current_status_desc. The first row is rank 1.10 and the last row that qualifies is 9.90. Sort ascending on the rank, and break ties on appl_applicant ascending so the ordering is reproducible.