Classifying animals in SQL
Question
Suppose you're given the following table showing the weights of various unique animals in a zoo:
Table: animal_weights
animal_ID | weight_lbs | zoo_ID |
---|---|---|
9992 | 1040 | 12 |
99929 | 1090 | 12 |
12993 | 2190 | 11 |
9821 | 750 | 11 |
96673 | 580 | 11 |
1411 | 690 | 12 |
1415 | 695 | 9 |
1410 | 690 | 9 |
1117 | 1000 | 9 |
1677 | 800 | 9 |
13457 | 600 | 9 |
389745 | 200 | 9 |
324589 | 950 | 9 |
7854320 | 1200 | 9 |
87345 | 1700 | 9 |
83248582 | 765 | 9 |
Using this table, write a SQL query to split the data into quartiles based animal weight (where 1 is the heaviest, 4 is the lightest). Then, within each quartile, rank the animal weights from heaviest to lightest in descending order.
Your output should be structured like this:
animal_id | weight_lbs | quartile | rank_in_quartile |
---|---|---|---|
X | X | 1 | 1 |
X | X | 1 | 2 |
X | X | 1 | 3 |
X | X | 1 | 4 |
X | X | 2 | 1 |
X | X | 2 | 2 |
etc | etc | etc | etc |
Click here to view the table in an interactive SQL fiddle.