Pairing animals together based on weight difference in SQL
Question
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 |
Suppose we want to return a list of animal pairs that are within 100lbs of each other (inclusive). For example, we can see that animals with ID 9992 (weight 1040lbs) and 99929 (weight 1090lbs) are within 100lbs of each other, so that particular snippet would return:
| animal_A | animal_B |
|---|---|
| 9992 | 99929 |
| 99929 | 9992 |
Given this table/information, write a SQL query to return all the pairs of animals within 100lbs of each other. You can view/query the table in an interactive SQL fiddle here.