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
Cats:
| name | varchar |
| breed | varchar |
| weight | float |
| color | varchar |
| age | int |
Show Desired Output
Desired output:
| name | weight | by_half | thirds | quart |
| Tigger | 3.80 | 1 | 1 | 1 |
| Molly | 4.20 | 1 | 1 | 1 |
| Ashes | 4.50 | 1 | 1 | 1 |
| Charlie | 4.80 | 1 | 1 | 2 |
| Smudge | 4.90 | 1 | 2 | 2 |
| Felix | 5.00 | 1 | 2 | 2 |
| Puss | 5.10 | 2 | 2 | 3 |
| Millie | 5.40 | 2 | 2 | 3 |
| Alfie | 5.50 | 2 | 3 | 3 |
| Misty | 5.70 | 2 | 3 | 4 |
| Oscar | 6.10 | 2 | 3 | 4 |
| Smokey | 6.10 | 2 | 3 | 4 |
Query Window:
Correct output but can you use 'window'?
Show Answer
Answer:
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