Adjusting student GPAs based on attendance levels in SQL
Question
You're given the following tables showing information around student attendance rates (where perc_present = the percent of the school year a given student was present) along with the GPA each student obtained.
Table: student_attendance_log;
| student_id | perc_present |
|---|---|
| 57510 | 0.9 |
| 57509 | 0.95 |
| 57508 | 0.93 |
| 57507 | 1 |
| 57506 | 0.99 |
| 57505 | 0.93 |
| 57504 | 0.8 |
| 57503 | 0.88 |
| 57501 | 0.82 |
| 57500 | 0.8 |
| 57499 | 0.91 |
| 57498 | 0.95 |
| 57497 | 0.85 |
Table: student_info
| student_id | gpa |
|---|---|
| 57510 | 3.86 |
| 57509 | 3.2 |
| 57508 | 3.9 |
| 57507 | 3.45 |
| 57506 | 3.26 |
| 57505 | 3.99 |
| 57504 | 3.43 |
| 57503 | 3.7 |
| 57501 | 3.51 |
| 57500 | 3.97 |
| 57499 | 3.43 |
| 57498 | 3.62 |
| 57497 | 3.31 |
The school takes attendance very seriously and has decided to give any student with >90% attendance a 0.15 bump in GPA (maxing out at 4.0). Write a SQL query that will return the resultant GPA for each student id.
You can view/query this table in an interactive SQL fiddle here.