The input table (score_category) categorizes the students (in the table students) by score in a given subject. There are 100 students grouped into three categories - excellent (score > 90), very good (80 < score < 90) and fair (score < 80) - as shown in the SQL case statement below
Sample Example Input Table students
id |
score |
1 |
5 |
2 |
83 |
3 |
95 |
4 |
95 |
5 |
90 |
6 |
55 |
7 |
40 |
8 |
57 |
9 |
65 |
10 |
27 |
... |
... |
CREATE TABLE score_category DISTRIBUTE BY hash(id) AS (
SELECT *, CASE
WHEN score <= 80 THEN 'fair'
WHEN score > 80 AND score < 90 THEN 'very good'
WHEN score >= 90 THEN 'excellent'
END AS stratum FROM students
);
Sample Example Input Table score_category
id |
score |
stratum |
1 |
5 |
fair |
2 |
83 |
very good |
3 |
95 |
excellent |
4 |
95 |
excellent |
5 |
90 |
excellent |
6 |
55 |
fair |
7 |
40 |
fair |
8 |
57 |
fair |
9 |
65 |
fair |
10 |
27 |
fair |