TRAVEL DESTINATIONS ANALYSIS

---top 20 Luxury destinations all over the world with high culture rating based on user ratings for culture, nature, cuisine, and adventure.

WITH total_rating as (
	SELECT city,
		country,
		region,
		culture + nature + cuisine + adventure as culture_rating
	FROM 'travel_data.csv'
)
SELECT r.city,
	r.country,
	r.region,
	culture_rating,
	(CASE WHEN culture_rating >= 17 THEN 'Excellent'
		WHEN culture_rating >= 14 THEN 'Very Good'
	    WHEN culture_rating >= 11 THEN 'Satisfactory'
	    WHEN culture_rating >= 8 THEN 'Meh'
	    ELSE 'Not Worth it' END) AS recommendation
FROM total_rating as r
INNER JOIN 'travel_data.csv' AS td
	ON td.city = r.city
WHERE budget_level = 'Luxury'
ORDER BY culture_rating DESC
LIMIT 20;

--top 20 budget travel destinations in different continents that is perfect for people who love outdoor activities. User-ranked destinations considered based on adventure, beaches, and nature. 

WITH outdoor_rating AS (
	SELECT city,
			country,
			region,
			adventure + beaches + nature as outdoors_score
	FROM 'travel_data.csv'
)
	
SELECT o.city,
		o.country,
		o.region,
		outdoors_score
FROM outdoor_rating as o
INNER JOIN 'travel_data.csv' as td
ON o.city = td.city
WHERE budget_level = 'Budget'
ORDER BY outdoors_score DESC
LIMIT 20;

--top 10 mid-range destinations in Europe perfect for a short weekend and nightlife getaway

WITH getaway AS (
	SELECT city,
		nightlife + urban + cuisine AS wkend_score
	FROM 'travel_data.csv'
)

SELECT g.city,
	td.nightlife,
	td.cuisine,
	wkend_score
FROM getaway as g
INNER JOIN 'travel_data.csv' AS td
ON g.city = td.city
WHERE td.region = 'Europe' AND
	budget_level = 'Mid-range' AND
	ideal_durations = 'Short trip' OR ideal_durations ='One week'
ORDER BY wkend_score
LIMIT 10;

--selecting travel destinations that have an average warm climate in January

SELECT city,
	country,
	avg_temp_monthly-> '1' ->> 'max' AS temp_december
FROM 'travel_data.csv'
WHERE CAST(temp_december AS INT) > 20
ORDER BY temp_december DESC
LIMIT 10;

--showing average temperatures in Kyoto for the months January to March

SELECT city,
	country,
	avg_temp_monthly-> '1' ->> 'avg' AS january_temp,
	avg_temp_monthly-> '2' ->> 'avg' AS february_temp,
	avg_temp_monthly-> '3' ->> 'avg' AS march_temp
FROM 'travel_data.csv'
WHERE city = 'Kyoto';

--30 destinations in Asia for different budget types perfect for a one week trip

WITH budget_d AS (
	SELECT CONCAT (city, ',', country) as destination,
			budget_level,
			culture
	FROM 'travel_data.csv'
	WHERE region = 'asia' AND
		budget_level = 'Budget'
	LIMIT 10
),

midrange_d AS (
	SELECT CONCAT (city, ',', country) as destination,
			budget_level,
			culture
	FROM 'travel_data.csv'
	WHERE region = 'asia' AND
		budget_level = 'Mid-range'
	LIMIT 10
),

luxury_d AS (
	SELECT CONCAT (city, ',', country) as destination,
			budget_level,
			culture
	FROM 'travel_data.csv'
	WHERE region = 'asia' AND
		budget_level = 'Luxury'
	LIMIT 10
)
	
SELECT destination,
	budget_level,
	culture
FROM budget_d
UNION ALL
SELECT destination,
	budget_level,
	culture
FROM midrange_d
UNION ALL
SELECT destination,
	budget_level,
	culture
FROM luxury_d
ORDER BY budget_level ASC, culture DESC;

--top backpacking destinations in the world per region perfect for extended trips ('Long trip'), for budget travelers

SELECT CONCAT (city, ', ', country) as destination,
	region,
	culture,
	nightlife,
	adventure,
	cuisine,
	wellness,
	culture + nightlife + adventure + cuisine + wellness AS traveler_score
FROM 'travel_data.csv'
WHERE budget_level = 'Budget' AND ideal_durations = 'Long trip'
ORDER BY region, traveler_score DESC;