We are using Postgres and on our student table, we have columns for letter grades [A, B, C, D, F]. We have two columns for previous grade and the current grade. I'm trying to find a way to run a report on how much the grades have changed for each record. So essentially, I want a field for current_grade-previous_grade but since the values are text, I can't do that comparison easily. I was hoping that parseInt in AWS QuickSight will parse a value for the grades but the documentation says it ignores non-numerical values. Since AWS QuickSight doesn't seem to solve this problem, how would I do this in my query in SQL when I don't have write access and can't create generated columns for this? I was hoping for something simple like:
SELECT id, report_card->'grade' as current_grade, report_card->'previous_grade' as old_grade, old_grade-current_grade FROM Students;
Quicksight seems to support locate which you might be able to tweak to your advantage. It's as if you're assigning numbers to grades in current_grade and previous_grade column
locate('ABCDEF', current_grade) - locate('ABCDEF', previous_grade)
If that doesn't work, they also have ifelse, which you could modify along these lines
ifelse(current_grade="A", 1, current_grade="B", 2, ......,current_grade="F", 6) -
ifelse(previous_grade="A", 1, previous_grade="B", 2, ......,previous_grade="F", 6)