00:00:00

Description

Editorial

Department Exam Percentile Ranking

MEDIUM20 pts

An academic analytics platform tracks exam marks for students across departments. Program heads want a normalized way to compare each student against peers in the same department.

Table: Students

student_id: unique student identifier
department_id: department identifier
mark: exam score

Task: For every student row, compute a department-relative rank percentage using: percentage = (rank - 1) * 100 / (department_size - 1)

Ranking behavior:

Rank marks in descending order inside each department.
Highest mark gets rank 1.
Tied marks share the same rank (competition ranking).

Edge behavior:

If a department has only one student, that student's percentage is 0.00.

Output requirements:

Return exactly: student_id, department_id, percentage
Round percentage to 2 decimal places
Output order is not important

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Students:
| student_id | department_id | mark |
|------------|---------------|------|
| 101        | 10            | 95   |
| 102        | 10            | 88   |
| 103        | 10            | 70   |
| 201        | 20            | 90   |
| 202        | 20            | 90   |

Output

[
  {"student_id":101,"department_id":10,"percentage":0.0},
  {"student_id":102,"department_id":10,"percentage":50.0},
  {"student_id":103,"department_id":10,"percentage":100.0},
  {"student_id":201,"department_id":20,"percentage":0.0},
  {"student_id":202,"department_id":20,"percentage":0.0}
]

Explanation

Department 10 has three students with ranks 1, 2, 3 mapping to 0, 50, and 100. Department 20 has a tie at the top, so both students receive rank 1 and percentage 0.

Example

Input

Students:
| student_id | department_id | mark |
|------------|---------------|------|
| 301        | 30            | 77   |

Output

[
  {"student_id":301,"department_id":30,"percentage":0.0}
]

Explanation

A single-student department always yields 0.00 because the denominator department_size - 1 is zero.

Example

Input

Students:
| student_id | department_id | mark |
|------------|---------------|------|
| 401        | 40            | 92   |
| 402        | 40            | 85   |
| 403        | 40            | 85   |
| 404        | 40            | 60   |

Output

[
  {"student_id":401,"department_id":40,"percentage":0.0},
  {"student_id":402,"department_id":40,"percentage":33.33},
  {"student_id":403,"department_id":40,"percentage":33.33},
  {"student_id":404,"department_id":40,"percentage":100.0}
]

Explanation

Marks 85 and 85 share rank 2. With four students, percentage for rank 2 is (2 - 1) * 100 / 3 = 33.33.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in Students <= 200000
student_id values are unique
1 <= student_id <= 2000000000
1 <= department_id <= 2000000000
0 <= mark <= 100000
Ranking uses descending mark order per department
Equal marks must share rank (RANK semantics)
If department size is 1, percentage must be 0.00
Round percentage to exactly 2 decimal places
Return columns exactly as: student_id, department_id, percentage
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: student_id, department_id, percentage

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Students =

[{"student_id":101,"department_id":10,"mark":95},{"student_id":102,"department_id":10,"mark":88},{"student_id":103,"department_id":10,"mark":70},{"student_id":201,"department_id":20,"mark":90},{"student_id":202,"department_id":20,"mark":90}]

Loading problem...

101

00:00:00

Description

Editorial

Department Exam Percentile Ranking

MEDIUM20 pts

An academic analytics platform tracks exam marks for students across departments. Program heads want a normalized way to compare each student against peers in the same department.

Table: Students

student_id: unique student identifier
department_id: department identifier
mark: exam score

Task: For every student row, compute a department-relative rank percentage using: percentage = (rank - 1) * 100 / (department_size - 1)

Ranking behavior:

Rank marks in descending order inside each department.
Highest mark gets rank 1.
Tied marks share the same rank (competition ranking).

Edge behavior:

If a department has only one student, that student's percentage is 0.00.

Output requirements:

Return exactly: student_id, department_id, percentage
Round percentage to 2 decimal places
Output order is not important

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Students:
| student_id | department_id | mark |
|------------|---------------|------|
| 101        | 10            | 95   |
| 102        | 10            | 88   |
| 103        | 10            | 70   |
| 201        | 20            | 90   |
| 202        | 20            | 90   |

Output

[
  {"student_id":101,"department_id":10,"percentage":0.0},
  {"student_id":102,"department_id":10,"percentage":50.0},
  {"student_id":103,"department_id":10,"percentage":100.0},
  {"student_id":201,"department_id":20,"percentage":0.0},
  {"student_id":202,"department_id":20,"percentage":0.0}
]

Explanation

Department 10 has three students with ranks 1, 2, 3 mapping to 0, 50, and 100. Department 20 has a tie at the top, so both students receive rank 1 and percentage 0.

Example

Input

Students:
| student_id | department_id | mark |
|------------|---------------|------|
| 301        | 30            | 77   |

Output

[
  {"student_id":301,"department_id":30,"percentage":0.0}
]

Explanation

A single-student department always yields 0.00 because the denominator department_size - 1 is zero.

Example

Input

Students:
| student_id | department_id | mark |
|------------|---------------|------|
| 401        | 40            | 92   |
| 402        | 40            | 85   |
| 403        | 40            | 85   |
| 404        | 40            | 60   |

Output

[
  {"student_id":401,"department_id":40,"percentage":0.0},
  {"student_id":402,"department_id":40,"percentage":33.33},
  {"student_id":403,"department_id":40,"percentage":33.33},
  {"student_id":404,"department_id":40,"percentage":100.0}
]

Explanation

Marks 85 and 85 share rank 2. With four students, percentage for rank 2 is (2 - 1) * 100 / 3 = 33.33.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in Students <= 200000
student_id values are unique
1 <= student_id <= 2000000000
1 <= department_id <= 2000000000
0 <= mark <= 100000
Ranking uses descending mark order per department
Equal marks must share rank (RANK semantics)
If department size is 1, percentage must be 0.00
Round percentage to exactly 2 decimal places
Return columns exactly as: student_id, department_id, percentage
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: student_id, department_id, percentage

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Students =

Department Exam Percentile Ranking

Hints

Department Exam Percentile Ranking

Hints