Swiss Tourism Portfolio Project

About the project

I have chosen to analyze the Swiss tourism sector to showcase my SQL and Tableau skills due to my personal connection to both the hospitality sector and Switzerland. In fact, I grew up south of the Swiss alps and studied hospitality management at EHL in Lausanne. The hospitality industry has been one of the hardest-hit industries by Covid-19 and many of my friends and former colleagues are still trying to navigate the new dynamics caused by the pandemic.

Methodology

I am exploring, cleaning and analyzing data using SQL code in BigQuery. For the visualizations I am working with Tableau Public.

Business Task

In this portfolio project, I am looking at the evolution of the Swiss tourism over the past two decades, specifically focusing on hotels and spas. I am looking at arrivals and overnight stays in the different cantons, as well as the arrivals by country of origin. By the end of the analysis I will be able to answer following questions:

How is the Swiss tourism changing over time and what impact has Covid-19 had on hotels.
What cantons are most attractive for hotel investors? Where do we have the most arrivals, overnights and occupancy?
What seasons are the busiest overall and per canton? What is the average length of stay?
What countries should a hotel target in their marketing campaigns? Where do most tourists come from? How did Covid-19 change the tourists mix?
Is there any relationship between the strength of the Swiss Franc, also considered as “safe haven” for investments in times of uncertainty, and the number of European tourists?

Source

The data has been provided by the Federal Statistical Office of Switzerland https://www.bfs.admin.ch/bfs/en/home/statistics/tourism.html .

Data preparation and cleaning

Change schema “Arrivals_Overnights”

In the Arrivals_Overnights table arrivals and overnights were attributed a string type instead of integer. Check if there are non numeric values in there.

SELECT Arrivals, Overnight_stays
FROM `my-project-5791-354915.PortfolioCH.Arrivals_Overnights`
ORDER BY Arrivals ASC

Missing values in the table are replaced with (…). Use safe_cast to replace those values with nulls and overwrite the table to update it. Also, we want all of the table titles in lower case for consistency reasons.

SELECT
Year AS year,
Month AS month,
Canton AS canton,
Visitors__country_of_residence AS visitors_residence,
SAFE_CAST(Arrivals AS INTEGER)AS arrivals, 
SAFE_CAST(Overnight_stays AS INTEGER) AS  overnight_stays
FROM `my-project-5791-354915.PortfolioCH.Arrivals_Overnights`

Change schema “Supply_Demand”

Rename columns, change data types and turn non numeric values into null, then overwrite old table.

SELECT
Year AS year,
Month AS month,
Canton AS canton,
SAFE_CAST(Establishments AS INTEGER)AS establishments, 
SAFE_CAST(Rooms AS INTEGER) AS  rooms,
SAFE_CAST(Beds AS INTEGER) AS  beds,
SAFE_CAST(Arrivals AS INTEGER) AS  arrivals,
SAFE_CAST(Overnight_stays AS INTEGER) AS  overnight_stays,
SAFE_CAST(Room_nights AS INTEGER) AS  room_nights,
SAFE_CAST(Room_occupancy AS FLOAT64) AS  room_occupancy,
SAFE_CAST(Bed_occupancy AS FLOAT64) AS  bed_occupancy,
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`

Change schema “EUR_CHF_Exchange_Rates”

Translate all the column names to English, change data type from string to date and overwrite old table.

SELECT 
CAST (PARSE_DATE('%d.%m.%Y',  Datum) AS date) AS date,
Schlusskurs AS closing_rate,
Er__ffnung AS opening_rate,
Tageshoch AS daily_max,
Tagestief AS daily_min
FROM `my-project-5791-354915.PortfolioCH.EUR_CHF_Exchange_Rates`

Change schema “European_Currencies”

Rename columns.

SELECT 
string_field_0 AS country,
string_field_1 AS currency,
string_field_2 AS currency_abbr
FROM `my-project-5791-354915.PortfolioCH.European_Currencies`; 

/* Now delete row 1 of the table to remove the old column names */

DELETE `my-project-5791-354915.PortfolioCH.European_Currencies` 
WHERE country = "Country or territory";

Data cleaning “Supply_Demand”


-- Dealing with missing data.

SELECT *
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
WHERE year is null OR month is null OR canton is null;

-- No missing data for year, month or canton.

SELECT *
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
WHERE establishments is null;

/* There are 182 rows with missing data . Check if there is a bias in the missing data (is it all in the same year, month or canton?) */

SELECT distinct year
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
WHERE establishments is null;

/* Missing data is only from 2022. I am assuming that it is data from the end of the data collection to the end of the year 2022. Check to confirm. */

SELECT distinct month
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
WHERE establishments is null;

/* Looks like data was collected until May 2022, so the missing data is indeed not yet been collected and we can exclude the missing values from the analysis. Lets check quick if there are missing values besides the ones due to the date.*/

SELECT *
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
WHERE establishments is NOT null 
and (rooms is null OR beds is null
OR overnight_stays is null);

-- There are no more missing values.

Data cleaning “Arrivals_Overnights”


-- Dealing with missing data.

SELECT *
FROM `my-project-5791-354915.PortfolioCH.Arrivals_Overnights`
WHERE year is null OR month is null OR canton is null OR visitors_residence is null;

-- No missing data for year, month, region, visitors_residence

SELECT *
FROM `my-project-5791-354915.PortfolioCH.Arrivals_Overnights`
WHERE arrivals is null OR overnight_stays is null AND year >2022;

SELECT visitors_residence, COUNT(visitors_residence)
FROM `my-project-5791-354915.PortfolioCH.Arrivals_Overnights`
WHERE arrivals is null OR overnight_stays is null AND year >2022
GROUP BY visitors_residence;

SELECT DISTINCT year
FROM `my-project-5791-354915.PortfolioCH.Arrivals_Overnights`
WHERE arrivals is null OR overnight_stays is null AND year >2022 
ORDER BY year;

SELECT DISTINCT canton
FROM `my-project-5791-354915.PortfolioCH.Arrivals_Overnights`
WHERE arrivals is null OR overnight_stays is null AND year >2022;

/*The missing data for overnight stays and arrivals seems to be spread across different years, regions and visitors residences. There does not seem to be data bias and I will disregard the missing values (38196 observations) */

-- Check if canton in the 2 tables are complete (all 26 cantons) and consistent in spelling.

SELECT a.canton, s.canton
FROM `my-project-5791-354915.PortfolioCH.Arrivals_Overnights` a
FULL OUTER JOIN `my-project-5791-354915.PortfolioCH.Supply_Demand` s
ON a.canton = s.canton
GROUP BY a.canton, s.canton;

-- We have all the cantons and they are spelled the same way, so they could be used to link the tables. 

-- Check if the tables have consistent information on arrivals and overnight stays.

WITH Arrivals_and_Overnights AS 
(
SELECT year, SUM(arrivals) AS a_arrivals, SUM(overnight_stays) AS a_overnight_stays
FROM `my-project-5791-354915.PortfolioCH.Arrivals_Overnights`
GROUP BY year
ORDER BY year),

Supply_Demand AS
(SELECT year, SUM(arrivals) AS s_arrivals, SUM(overnight_stays) AS s_overnight_stays
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
GROUP BY year
ORDER BY year)

SELECT a.year, a_arrivals,s_arrivals,ROUND(100*(a_arrivals-s_arrivals)/(a_arrivals),1) AS diff_arrivals, a_overnight_stays,  s_overnight_stays, ROUND(100*(a_overnight_stays-s_overnight_stays)/a_overnight_stays,1) AS diff_overnights
FROM Arrivals_and_Overnights a FULL OUTER JOIN Supply_Demand s
ON a.year= s.year
ORDER BY ABS(diff_overnights) DESC;

/* We confirmed that overnight_stays and arrivals data is almost identical (max difference 1% between the tables). We can therefore use either table for the analysis of overnights and arrivals.*/

Data exploration and analysis

Overview

Summarize arrivals, overnight stays and length of stay per month for the whole country (min, max and avg for the years 2005-2021)

WITH TOT_CH AS (
SELECT year, month, SUM(arrivals) AS arrivals_CH, SUM(overnight_stays) AS overnights_CH,round(SUM(overnight_stays)/ SUM(arrivals),1) AS avg_length_of_stay
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
WHERE year<2022 -- data for 2022 not complete yet (only until May)
GROUP BY year, month)

SELECT year, MAX(arrivals_CH) AS max_arrivals,MIN(arrivals_CH) AS min_arrivals,
ROUND(AVG(arrivals_CH)) AS avg_arrivals, ROUND(STDDEV(arrivals_CH)) AS stdev_arrivals, MAX(overnights_CH) AS max_overnights,MIN(overnights_CH) AS min_overnights,ROUND(AVG(overnights_CH)) AS avg_overnights, ROUND(STDDEV(overnights_CH)) AS stdev_overnights, ROUND(AVG(avg_length_of_stay),1) AS avg_length_of_stay
FROM TOT_CH
GROUP BY year;

Output overview:

output preview

Top 10 arrivals by country of origin (2005-2022).


WITH visitors_ranking AS
(
SELECT  year, visitors_residence, SUM(arrivals)AS arrivals_CH,
RANK() over (partition by year order by SUM(arrivals) DESC) as rank,
FROM `my-project-5791-354915.PortfolioCH.Arrivals_Overnights`
WHERE arrivals IS NOT NULL 
GROUP BY year, visitors_residence
ORDER BY year),

tot_yearly_arrivals AS(
SELECT year, visitors_residence, arrivals_CH, 
  rank, sum(arrivals_CH) OVER (partition by year)AS tot_yearly_arr
  FROM visitors_ranking)

SELECT year, visitors_residence, arrivals_CH, rank, ROUND(100*arrivals_CH/tot_yearly_arr,1) AS perc_yearly_arr
FROM tot_yearly_arrivals
WHERE rank BETWEEN 1 AND 10
ORDER BY year;

Output overview:

Domestic vs international tourism per year

WITH Domestic_International AS 
(
  SELECT year,visitors_residence, sum(arrivals) AS arrivals_CH,
sum(overnight_stays) AS overnights_CH,ROUND(sum(overnight_stays)/sum(arrivals),1) AS length_stay,
CASE WHEN visitors_residence = "Switzerland" THEN "domestic"
WHEN visitors_residence <> "Switzerland" THEN "international"
END AS dom_int
FROM `my-project-5791-354915.PortfolioCH.Arrivals_Overnights`
WHERE year <2022
GROUP BY year, visitors_residence
ORDER BY year),

summary_dom_int AS 
(
SELECT year,dom_int, SUM(arrivals_CH) AS arr_dom_int, SUM(overnights_CH) AS overn_dom_int, ROUND(AVG(length_stay),1) AS length_stay
FROM Domestic_International
GROUP BY year, dom_int
ORDER BY year)

SELECT year, summary_dom_int.dom_int, summary_dom_int.arr_dom_int, summary_dom_int.overn_dom_int, ROUND(AVG(length_stay),1) AS length_stay,
ROUND(summary_dom_int.arr_dom_int*100/sum(arr_dom_int) OVER(partition by year),1) AS perc_arr,
ROUND(summary_dom_int.overn_dom_int*100/sum(summary_dom_int.overn_dom_int) OVER(partition by year),1) AS perc_overn
FROM summary_dom_int
GROUP BY year, summary_dom_int.dom_int, summary_dom_int.arr_dom_int, summary_dom_int.overn_dom_int
ORDER BY year;

Output overview:

Most attractive cantons for hotel investors


/* Rank cantons by their monthly overnight stays  (average of the past 5 years)*/
SELECT canton, ROUND(AVG(overnight_stays)) AS avg_overnights
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
WHERE year BETWEEN 2017 AND 2021
GROUP BY canton
ORDER BY avg_overnights DESC;

/* Rank cantons by their monthly arrivals  (average of the past 5 years)*/
SELECT canton, ROUND(AVG(arrivals)) AS avg_arrivals
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
WHERE year BETWEEN 2017 AND 2021
GROUP BY canton
ORDER BY avg_arrivals DESC;

/* Rank cantons by their monthly room occupancy rate (average of the past 5 years)*/
SELECT canton, ROUND(AVG(room_occupancy)) AS avg_room_occ
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
WHERE year BETWEEN 2017 AND 2021
GROUP BY canton
ORDER BY avg_room_occ DESC;

Output overview- Rank cantons by their monthly overnight stays:

Output overview- Rank cantons by their monthly arrivals

Output overview: Rank cantons by their monthly room occupancy rate

Influence of Covid-19 on Swiss tourism.

Average yearly bed occupancy and room occupancy in Switzerland before Covid (avg 2017 until February 2020) vs post-Covid (2020-May 2022)

SELECT 
CASE 
WHEN year <2020 THEN "Avg 2017-2019"
WHEN year = 2020 THEN "Covid Peak (2020)"
WHEN year = 2021 THEN "2021"
WHEN year = 2022 THEN "Jan-May 2022"
END AS Period,
ROUND(AVG(room_occupancy)) AS avg_room_occ, ROUND(AVG(bed_occupancy)) AS avg_bed_occ
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
WHERE year >= 2017
GROUP BY Period;

Output overview:

Room occupancy declined slightly since Cpvid-19, but not dramatically. Check if the average monthly room availability in Switzerland has changed (did hotels have to close due to the pandemic? Monthly avg 2017, 2018 & 2019 vs post-covid (2020-April 2022).

WITH ROOMS_TOT_CH AS (
SELECT year, month,SUM(rooms) AS rooms_CH --sum rooms= daily rooms all cantons combined (all Switzerland)
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
WHERE year>= 2017
GROUP BY year, month)

SELECT year,ROUND(AVG(rooms_CH)) AS avg_rooms_CH,
CASE 
WHEN year = 2017 THEN 0
WHEN year > 2017 THEN 
ROUND(((AVG(rooms_CH) - LAG(AVG(rooms_CH)) OVER (ORDER BY year))/LAG(AVG(rooms_CH)) OVER (ORDER BY year))*100,2)
END AS rooms_change
FROM ROOMS_TOT_CH
GROUP BY year
ORDER BY year;

Output overview:

There was a drop in room availability in 2020 by almost 10%, likely due to the Covid-19 outbreak and related lock downs and restrictions. By 2021 the availability was up 5.8% again and in 2022 there are more rooms available than prior to covid (+ 1.4% since 2019).

With room occupancy slightly down and decreased room availability for 2020, lets look at the effect of Covid on overnight stays.


WITH OVERNIGHTS_TOT_CH AS (
SELECT year, month,SUM(overnight_stays) AS overnights_CH -- combined overnight stays for the country
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
WHERE year>= 2018
GROUP BY year, month)

SELECT year,ROUND(AVG(overnights_CH)) AS avg_overnights_CH,
CASE 
WHEN year = 2018 THEN 0
WHEN year > 2018 THEN 
ROUND(((AVG(overnights_CH) - LAG(AVG(overnights_CH)) OVER (ORDER BY year))/LAG(AVG(overnights_CH)) OVER (ORDER BY year))*100,2)
END AS overnights_change
FROM OVERNIGHTS_TOT_CH
GROUP BY year
ORDER BY year;

Output overview:

Covid and arrivals.

WITH ARRIVALS_TOT_CH AS (
SELECT year, month,SUM(arrivals) AS arrivals_CH -- combined arrivals for the country
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
WHERE year>= 2018
GROUP BY year, month)

SELECT year,ROUND(AVG(arrivals_CH)) AS avg_arrivals_CH,
CASE 
WHEN year = 2018 THEN 0
WHEN year > 2018 THEN 
ROUND(((AVG(arrivals_CH) - LAG(AVG(arrivals_CH)) OVER (ORDER BY year))/LAG(AVG(arrivals_CH)) OVER (ORDER BY year))*100,2)
END AS arrivals_change
FROM ARRIVALS_TOT_CH
GROUP BY year
ORDER BY year;

Output overview:

Summary of Covid changes

WITH COVID_EFFECT AS (
SELECT year, month,
SUM(rooms) AS rooms_CH, --sum rooms= daily rooms all cantons combined (all Switzerland)
SUM(arrivals) AS arrivals_CH,
SUM(overnight_stays) AS overnights_CH,
AVG(room_occupancy) AS occupancy_CH
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
WHERE year>= 2017
GROUP BY year, month)

SELECT year,
ROUND(AVG(rooms_CH)) AS avg_rooms_CH,
CASE 
WHEN year = 2017 THEN 0
WHEN year > 2017 THEN 
ROUND(((AVG(rooms_CH) - LAG(AVG(rooms_CH)) OVER (ORDER BY year))/LAG(AVG(rooms_CH)) OVER (ORDER BY year))*100,2)
END AS rooms_change,
ROUND(AVG(arrivals_CH)) AS avg_arrivals_CH,
CASE 
WHEN year = 2017 THEN 0
WHEN year > 2017 THEN 
ROUND(((AVG(arrivals_CH) - LAG(AVG(arrivals_CH)) OVER (ORDER BY year))/LAG(AVG(arrivals_CH)) OVER (ORDER BY year))*100,2)
END AS arrivals_change,
ROUND(AVG(overnights_CH)) AS overnights_CH,
CASE 
WHEN year = 2017 THEN 0
WHEN year > 2017 THEN 
ROUND(((AVG(overnights_CH) - LAG(AVG(overnights_CH)) OVER (ORDER BY year))/LAG(AVG(overnights_CH)) OVER (ORDER BY year))*100,2)
END AS overnights_change,
ROUND(AVG(occupancy_CH)) AS avg_occupancy_CH,
CASE 
WHEN year = 2017 THEN 0
WHEN year > 2017 THEN 
ROUND((AVG(occupancy_CH) - LAG(AVG(occupancy_CH)) OVER (ORDER BY year)),2)
END AS occupancy_change
FROM COVID_EFFECT
GROUP BY year
ORDER BY year;

Output overview:

Origin of visitors by canton before and after Covid (Top 5 for 2019 and 2021).

WITH Canton_by_origin AS (
    SELECT year, canton,visitors_residence, SUM(arrivals) AS arrivals_per_year
FROM `my-project-5791-354915.PortfolioCH.Arrivals_Overnights`
WHERE year=2019 OR year=2021 
GROUP BY year, canton, visitors_residence
ORDER BY year, canton),

Canton_by_origin_ranked AS (
  SELECT year, canton,visitors_residence, arrivals_per_year,RANK() OVER (PARTITION BY year,canton ORDER BY arrivals_per_year DESC) AS rank
FROM Canton_by_origin
ORDER BY year, canton, rank),

Canton_by_origin_perc AS(
SELECT year, canton,visitors_residence, arrivals_per_year, rank,ROUND(100*arrivals_per_year/sum(arrivals_per_year) OVER (PARTITION BY year, canton),1) AS arrivals_percent
FROM Canton_by_origin_ranked )

SELECT year, canton,visitors_residence, arrivals_per_year,rank, arrivals_percent 
FROM Canton_by_origin_perc
WHERE  rank <=5 
ORDER BY year, canton, rank

Output overview:

Tourism by season and canton.

SELECT year, month, canton, sum(arrivals) AS arrivals,
CASE
WHEN month IN ("December","January","February") THEN "Winter"
WHEN month IN ("March","April","May") THEN "Spring"
WHEN month IN ("June","July","August") THEN "Summer"
WHEN month IN ("September","October","November") THEN "Fall"
END AS season
FROM `my-project-5791-354915.PortfolioCH.Arrivals_Overnights`
GROUP BY year, month, canton
ORDER BY year;

Output overview:

Tourism by season: arrivals per season total Switzerland.

WITH season_canton AS (
  SELECT year, month, canton, sum(arrivals) AS arrivals,
CASE
WHEN month IN ("December","January","February") THEN "Winter"
WHEN month IN ("March","April","May") THEN "Spring"
WHEN month IN ("June","July","August") THEN "Summer"
WHEN month IN ("September","October","November") THEN "Fall"
END AS season
FROM `my-project-5791-354915.PortfolioCH.Arrivals_Overnights`
GROUP BY year, month, canton
ORDER BY year),

season_country AS (
  SELECT year, season, SUM(arrivals) AS arrivals
FROM season_canton
GROUP BY year, season
ORDER BY year)

SELECT year, season,arrivals, ROUND(100*arrivals
/sum(arrivals) OVER (PARTITION BY year),1) AS arrivals_percent
FROM season_country
WHERE year <2022
ORDER BY year;

Output overview:

Tourism by season and canton: arrivals per season per canton (percent)

WITH season_canton AS (
  SELECT year, month, canton, sum(arrivals) AS arrivals,
CASE
WHEN month IN ("December","January","February") THEN "Winter"
WHEN month IN ("March","April","May") THEN "Spring"
WHEN month IN ("June","July","August") THEN "Summer"
WHEN month IN ("September","October","November") THEN "Fall"
END AS season
FROM `my-project-5791-354915.PortfolioCH.Arrivals_Overnights`
GROUP BY year, month, canton
ORDER BY year),

 season_canton_2 AS(
   SELECT year,canton, season, SUM(arrivals) AS arrivals
FROM season_canton
GROUP BY year, canton,season
ORDER BY year,canton)

SELECT year, canton, season,arrivals, ROUND(100*arrivals/SUM(arrivals) OVER (PARTITION BY year, canton),1) AS percentage_arrivals
FROM season_canton_2
WHERE year >2005 AND year<2022
ORDER BY year, canton;

Output overview:

Relationship between the strength of the Swiss currency (CHF) against the EURO and European tourism (only countries with EUR).


WITH rate AS(
  SELECT date,EXTRACT(day FROM date) AS day,EXTRACT(month FROM date) AS month,
  EXTRACT(year FROM date) AS year,closing_rate,FORMAT_DATE('%B',date) AS month_str
FROM `my-project-5791-354915.PortfolioCH.EUR_CHF_Exchange_Rates`
ORDER BY date),

rate_aggr AS(
  SELECT year, month_str,CONCAT(year, "_", month_str) AS year_month, ROUND(AVG(closing_rate),2) AS avg_monthly_rate
FROM rate
GROUP BY year, month_str, year_month
ORDER BY year, month_str),

arrivals_for_join AS(
  SELECT *, CONCAT(year, "_", month) AS year_month
  FROM `my-project-5791-354915.PortfolioCH.Arrivals_Overnights`),

join_rate_arrivals AS(
  SELECT a.*, r.avg_monthly_rate
FROM rate_aggr r JOIN arrivals_for_join a
ON r.year_month = a.year_month),

rate_updated AS(
  SELECT *, CASE WHEN country IN ("Estonia", "Latvia", "Lituania") THEN "Baltic States"
ELSE country END AS country_summarized
 FROM `my-project-5791-354915.PortfolioCH.European_Currencies` 
 WHERE currency_abbr = "EUR"), 

 join_arrivals_currency AS (SELECT a.*, r.currency_abbr  
 FROM join_rate_arrivals a
 LEFT JOIN rate_updated r
 ON visitors_residence = country_summarized),
 
 join_arrivals_currency2 AS(
   SELECT *, CASE WHEN visitors_residence = "Other European Countries" THEN "EUR"
   WHEN visitors_residence = "Switzerland" THEN "CHF"
 ELSE currency_abbr END AS currency
 FROM join_arrivals_currency)
 
 SELECT year, month, visitors_residence, currency, sum(arrivals) AS arrivals, sum(overnight_stays) AS overnight_stays, avg(avg_monthly_rate) AS average_rate
 FROM join_arrivals_currency2
 WHERE currency IN ("EUR", "CHF") AND year > 2020
 GROUP BY currency, year, month, visitors_residence
 ORDER BY year;

Output overview:

Change in room occupancy per year and canton.

WITH yearly_data AS(
  SELECT year, canton, SUM(establishments) AS establishments, SUM(rooms) as rooms,SUM(arrivals)as arrivals, SUM(overnight_stays) AS overnight_stays, SUM(room_nights) AS room_nights,ROUND(AVG(room_occupancy),1) AS room_occupancy, ROUND(AVG(bed_occupancy),1) AS bed_occupancy
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
WHERE year < 2022 AND arrivals IS NOT NULL
GROUP BY year, canton
ORDER BY year, arrivals DESC)

SELECT *,ROUND(room_occupancy-lag(room_occupancy) OVER (PARTITION BY canton ORDER BY year ),1) AS occupancy_difference
FROM yearly_data
ORDER BY canton, year;

Output overview:

Length of stay per canton in 2019

SELECT year, canton, sum(arrivals) AS arrivals, sum(overnight_stays) AS overnight_stays, ROUND(sum(overnight_stays)/sum(arrivals),1) AS length_of_stay
FROM `my-project-5791-354915.PortfolioCH.Supply_Demand`
WHERE year = 2019
GROUP BY year, canton
ORDER BY year

Output overview:

Swiss Tourism Portfolio Project

Olivia Engl

2022-08-19

About the project

Methodology

Business Task

Source

Data preparation and cleaning

Change schema “Arrivals_Overnights”

Change schema “Supply_Demand”

Change schema “EUR_CHF_Exchange_Rates”

Change schema “European_Currencies”

Data cleaning “Supply_Demand”

Data cleaning “Arrivals_Overnights”

Data exploration and analysis

Overview

Top 10 arrivals by country of origin (2005-2022).

Domestic vs international tourism per year

Most attractive cantons for hotel investors

Influence of Covid-19 on Swiss tourism.