Using Window Clause
Question:
This SQL function can be made simpler by using the WINDOW statement. Please try and use the WINDOW clause.
Each cat would like to see what half, third and quartile they are in for their weight.
Return: name, weight, by_half, thirds, quartile
Order by: weight Show Table Schema
Cats:
| name | varchar |
| breed | varchar |
| weight | float |
| color | varchar |
| age | int |
Correct output but can you use 'window'?
Desired output:
| name | weight | by_half | thirds | quart |
| Tigger | 3.8 | 1 | 1 | 1 |
| Molly | 4.2 | 1 | 1 | 1 |
| Ashes | 4.5 | 1 | 1 | 1 |
| Charlie | 4.8 | 1 | 1 | 2 |
| Smudge | 4.9 | 1 | 2 | 2 |
| Felix | 5.0 | 1 | 2 | 2 |
| Puss | 5.1 | 2 | 2 | 3 |
| Millie | 5.4 | 2 | 2 | 3 |
| Alfie | 5.5 | 2 | 3 | 3 |
| Misty | 5.7 | 2 | 3 | 4 |
| Oscar | 6.1 | 2 | 3 | 4 |
| Smokey | 6.1 | 2 | 3 | 4 |
select name, weight, ntile(2) over ntile_window as by_half, ntile(3) over ntile_window as thirds, ntile(4) over ntile_window as quart from cats window ntile_window AS ( ORDER BY weight) order by weight, name