-- Create the cats table
CREATE TABLE cats (
    name VARCHAR(50),
    breed VARCHAR(50),
    weight FLOAT,
    color VARCHAR(50),
    age INT
);

-- Insert data into the cats table
INSERT INTO cats (name, breed, weight, color, age) VALUES
('Ashes', 'Persian', 4.5, 'Black', 5),
('Molly', 'Persian', 4.2, 'Black', 1),
('Felix', 'Persian', 5.0, 'Tortoiseshell', 2),
('Smudge', 'British Shorthair', 4.9, 'Black', 4),
('Tigger', 'British Shorthair', 3.8, 'Tortoiseshell', 2),
('Alfie', 'Siamese', 5.5, 'Brown', 5),
('Oscar', 'Siamese', 6.1, 'Black', 1),
('Millie', 'Maine Coon', 5.4, 'Tortoiseshell', 5),
('Misty', 'Maine Coon', 5.7, 'Brown', 2),
('Puss', 'Maine Coon', 5.1, 'Tortoiseshell', 2),
('Smokey', 'Maine Coon', 6.1, 'Brown', 4),
('Charlie', 'British Shorthair', 4.8, 'Black', 4);


/*
Question:

The cats would like to see the average of the weight of them, the cat just after them and the cat just before them.

The first and last cats are content to have an average weight of consisting of 2 cats not 3.

Return: name, weight, average_weight
Order by: weight
*/

select 
name,
weight, 
round(avg(weight) over (order by weight ROWs between 1 PRECEDING and 1 following), 1) as average_weight
from cats