/*
Expedia, Airbnb & Tripadvisor SQL Interview Question

Find the top two hotels with the most negative reviews.
Output the hotel name along with the corresponding number of negative reviews.
Negative reviews are all the reviews with text under negative review different than "No Negative".
Sort records based on the number of negative reviews in descending order.
*/
    
-- Schema Setup
CREATE TABLE hotel_reviews (additional_number_of_scoring INT, average_score FLOAT, days_since_review VARCHAR(255), hotel_address VARCHAR(255), hotel_name VARCHAR(255), lat FLOAT, lng FLOAT, negative_review VARCHAR(50), positive_review VARCHAR(50), review_date DATETIME, review_total_negative_word_counts INT, review_total_positive_word_counts INT, reviewer_nationality VARCHAR(255), reviewer_score FLOAT, tags VARCHAR(50), total_number_of_reviews INT, total_number_of_reviews_reviewer_has_given INT);

INSERT INTO hotel_reviews VALUES
(25, 8.7, '15 days ago', '123 Street, City A', 'Hotel Alpha', 12.3456, 98.7654, 'Too noisy at night', 'Great staff and clean rooms', '2024-12-01', 5, 15, 'USA', 8.5, "Couple", 200, 10), (30, 9.1, '20 days ago', '456 Avenue, City B', 'Hotel Beta', 34.5678, 76.5432, 'Old furniture', 'Excellent location', '2024-12-02', 4, 12, 'UK', 9.0, "Solo traveler", 150, 8), (12, 8.3, '10 days ago', '789 Boulevard, City C', 'Hotel Gamma', 23.4567, 67.8901, 'No Negative', 'Friendly staff', '2024-12-03', 0, 10, 'India', 8.3,"Family", 100, 5), (15, 8.0, '5 days ago', '321 Lane, City D', 'Hotel Delta', 45.6789, 54.3210, 'Uncomfortable bed', 'Affordable price', '2024-12-04', 6, 8, 'Germany', 7.8,"Couple", 120, 7),
(20, 7.9, '8 days ago', '654 Road, City E', 'Hotel Alpha', 67.8901, 12.3456, 'Poor room service', 'Good breakfast', '2024-12-05', 7, 9, 'France', 7.5, "Solo traveler", 180, 6), (18, 9.3, '18 days ago', '987 Highway, City F', 'Hotel Beta', 34.5678, 76.5432, 'No Negative', 'Amazing facilities', '2024-12-06', 0, 20, 'USA', 9.2,"Couple", 250, 15);

-- Solution
SELECT
    hotel_name,
    COUNT(negative_review) AS negative_review_cnt
FROM hotel_reviews
WHERE negative_review <> "No negative"
GROUP BY hotel_name
ORDER BY negative_review_cnt DESC
LIMIT 2;