BA Reviews
(Download the CSV file used)

/* To see the file*/  

SELECT * from Reviews LIMIT 5;  

author	date	place	aircraft	traveller_type	seat_type	route	date_flown	recommended	trip_verified	rating	seat_comfort	cabin_staff_service	food_beverages	ground_service	value_for_money	entertainment
Gary Storer	03/10/2023	United Kingdom	A380	Couple Leisure	Economy Class	Chicago to Manchester via Heathrow	01/10/2023	no	Not Verified	2	2	3	1	2	2	-1
A Jensen	02/10/2023	United Kingdom	A320	Business	Business Class	London Heathrow to Munich	01/09/2023	no	Verified	2	2	1	2	1	1	-1
John Rockett	02/10/2023	United Kingdom	A320	Couple Leisure	Business Class	Heathrow to Istanbul	01/09/2023	no	Not Verified	2	2	3	2	1	1	-1
Tatiana Bobrovskaya	02/10/2023	United Kingdom	A320	Business	Economy Class	London to Geneva	01/10/2023	no	Verified	3	4	4	2	1	1	-1
Tom Slowbe	28/09/2023	United States	777-300 and A320	Couple Leisure	First Class	Dallas to Dubrovnik via Heathrow	01/09/2023	no	Verified	1	1	4	4	3	2	3

/* Seeing the average customer rating grouped by Seat Type over the years */  

SELECT  
    seat_type,  
    year(date),  
    round(AVG(rating),1)  
FROM  
    REVIEWS  
GROUP BY 1, 2  
ORDER BY 1,2 ASC;  

/* Modification of the above code to compare the rating from this year against the score from the previous year.*/  

WITH OrderedReviews AS (  
SELECT  
    seat_type,  
    year(date) AS Year,  
    round(AVG(rating),1) As YearlyRating  
FROM  
    REVIEWS  
GROUP BY 1, 2  
ORDER BY 1,2 ASC)  

SELECT  
    seat_type,  
    Year, YearlyRating AS 'Yearly Rating',  
    LAG(YearlyRating,1, null) OVER (PARTITION BY seat_type Order By Year) AS 'Previous Yearly Rating'  
FROM  
    OrderedReviews;  

/* I've used the same code as above but compared Cabin Staff Service over the years. We could repeat running the code for every review variable.*/  

WITH OrderedReviews AS (  
SELECT seat_type, year(date) AS Year, round(AVG(cabin_staff_service),1) As YearlyRating from REVIEWS GROUP BY 1, 2 ORDER BY 1,2 ASC)  

SELECT seat_type, Year, YearlyRating AS 'Yearly Rating - Cabin Staff Service', LAG(YearlyRating,1, null) OVER (PARTITION BY seat_type Order By Year) AS 'Previous Yearly Rating' FROM OrderedReviews;  

/* Seeing the average rating for each aircraft - but only showing results where more than 10 reviews per aircraft have been given.*/  

SELECT  
    aircraft,  
    ROUND(AVG(rating),1) AS Average_Rating,  
    COUNT(*) AS No_Of_Reviews  
FROM  
    Reviews  
GROUP BY  
    aircraft  
HAVING  
    No_Of_Reviews > 10  
ORDER BY 2 DESC;