Title: SQL Data Analysis Project

CREATE TABLE IF NOT EXISTS public.shg_booking(    booking_id integer,    hotel character varying(50) ,    booking_date date,    arrival_date date ,    lead_time integer,    nights integer,    guests smallint,    distribution_channel text ,    customer_type character varying(50) ,    country character varying(50),    deposit_type character varying(50),    avg_daily_rate text ,    status text ,    status_update date ,    cancelled smallint,    revenue text ,    revenue_loss text)

SELECT *FROM shg_booking

-- DATA CLEANING STEPS FOR AVG_DAILY_RATE COLUMNUPDATE shg_bookingSET avg_daily_rate = REPLACE(avg_daily_rate, '$', '');UPDATE shg_bookingSET avg_daily_rate = REPLACE(avg_daily_rate, '(', '-');UPDATE shg_bookingSET avg_daily_rate = REPLACE(avg_daily_rate, ')', '');UPDATE shg_bookingSET avg_daily_rate = REPLACE(avg_daily_rate, ',', '');ALTER TABLE shg_bookingALTER COLUMN avg_daily_rateTYPE numericUSING avg_daily_rate::numeric;

-- DATA CLEANING STEPS FOR REVENUE COLUMNUPDATE shg_bookingSET revenue = REPLACE(revenue, '$', '');UPDATE shg_bookingSET revenue = REPLACE(revenue, ',', '');UPDATE shg_bookingSET revenue = REPLACE(revenue, '(', '-');UPDATE shg_bookingSET revenue = REPLACE(revenue, ')', '');ALTER TABLE shg_bookingALTER COLUMN revenueTYPE numericUSING revenue :: numeric;

-- DATA CLEANING STEPS FOR REVENUE LOSS COLUMNUPDATE shg_bookingSET revenue_loss = REPLACE(revenue_loss, '$', '');UPDATE shg_bookingSET revenue_loss = REPLACE(revenue_loss, ',', '');UPDATE shg_bookingSET revenue_loss = REPLACE(revenue_loss, '(', '-');UPDATE shg_bookingSET revenue_loss = REPLACE(revenue_loss, ')', '');ALTER TABLE shg_bookingALTER COLUMN revenue_lossTYPE numericUSING revenue_loss :: numeric;

SELECT COUNT (*),EXTRACT (month FROM booking_date) AS monthFROM shg_bookingGROUP BY monthORDER BY month;

SELECT distribution_channel,        COUNT (booking_id) AS total_bookings,    COUNT (*) * 100.0 /SUM (COUNT(*)) OVER () AS contribution_percentageFROM shg_bookingWHERE country is not NULLGROUP BY distribution_channelORDER BY total_bookings DESC;

SELECT distribution_channel,        SUM(avg_daily_rate)FROM shg_bookingGROUP BY distribution_channel;

SELECT distribution_channel,       ABS (SUM(revenue_loss)) AS revenuelossFROM shg_bookingWHERE status = 'Canceled'GROUP BY distribution_channelORDER BY revenueloss ASC;

SELECT country,       SUM (revenue) AS totalcountryrevenueFROM shg_bookingGROUP BY countryORDER BY totalcountryrevenue DESCLIMIT 10;

SELECT EXTRACT (month FROM booking_date) AS months,SUM (revenue) AS totalrevenuepermonthFROM shg_bookingGROUP BY monthsORDER BY months;

-- Revenue Trend by Customer typeSELECT customer_type,         SUM(revenue) AS revenueFROM shg_bookingWHERE country IS NOT NULLGROUP BY customer_typeORDER BY revenue DESC;

SELECT country,       SUM (guests) AS totalguestsFROM shg_bookingWHERE country IS NOT NULLGROUP BY countryORDER BY totalguests DESC;

--1 Total Avg length of stay SELECT AVG (nights) AS total_avglength_of_stay,       TRUNC( AVG (nights), 2)FROM shg_bookingWHERE country IS NOT NULL

-- Across distribution channelSELECT shg.distribution_channel,    AVG (nights) AS averagelengthday,    TRUNC (AVG (nights), 2)FROM shg_booking AS shgWHERE country IS NOT NULLGROUP BY shg.distribution_channel--Across customer typesSELECT shg.customer_type,    AVG (nights) AS averagelengthday,    TRUNC (AVG (nights), 2)FROM shg_booking AS shgWHERE country IS NOT NULLGROUP BY shg.customer_type

/* 2.  Are there patterns in check-out dates that can inform staffing and resource allocationstrategies? */SELECT status_update,        COUNT(booking_id) AS number_of_bookings,     SUM(guests) AS number_of_guestsFROM shg_bookingWHERE country IS NOT NULL AND status = 'Check-Out'GROUP BY status_updateORDER BY status_update;-- BY day of weekSELECT CASE WHEN EXTRACT(dow from status_update) = 0 THEN 'Sunday'   WHEN EXTRACT(dow from status_update) = 1 THEN 'Monday'   WHEN EXTRACT(dow from status_update) = 2 THEN 'Tuesday'   WHEN EXTRACT(dow from status_update) = 3 THEN 'Wednesday'   WHEN EXTRACT(dow from status_update)  = 4 THEN 'Thursday'   WHEN EXTRACT(dow from status_update)  = 5 THEN 'Friday'   WHEN EXTRACT(dow from status_update)  = 6 THEN 'Saturday'   END AS day_of_week,  COUNT(booking_id) AS number_of_bookings,  SUM(guests) AS number_of_guestsFROM shg_bookingWHERE country IS NOT NULL AND status = 'Check-Out'GROUP BY day_of_weekORDER BY COUNT(booking_id) DESC;  --> There seems to be more activities on Sundays

-- How does the presence/absence of a deposit impact the likelihood of cancellations and revenue generationSELECT deposit_type,       COUNT (*)FROM shg_bookingWHERE status = 'Canceled' AND country IS NOT NULLGROUP BY deposit_type ORDER BY COUNT (*) DESC;  --The absence of deposit leads to more cancelationSELECT deposit_type,    SUM (revenue) AS total_revenueFROM shg_bookingWHERE country IS NOT NULLGROUP BY deposit_typeORDER BY total_revenue; --The absence of Deposit also yielded more revenue compared to other deposit type

--Analysis of Corporate Bookings/* proportion of corporate bookings and how it affects ADR compare to other customer types */SELECT       SUM(CASE WHEN distribution_channel = 'Corporate' THEN 1 END) AS corporate_booking,       COUNT(booking_id) AS total_bookings,    (SUM(CASE WHEN distribution_channel = 'Corporate' THEN 1 END) * 100.0) / COUNT (booking_id) AS corporate_booking_proportionFROM shg_bookingWHERE country IS NOT NULL-- ii.Average daily rate with Customer TypeSELECT customer_type,       SUM(avg_daily_rate) as avg_daily_rateFROM shg_bookingWHERE distribution_channel = 'Corporate' AND country IS NOT NULLGROUP BY customer_typeORDER BY avg_daily_rate DESC;

/* 1. Are there specific trends or patterns related to corporate bookings that can informbusiness strategies? */-- Based on BookingSELECT distribution_channel,        COUNT(booking_id)FROM shg_bookingWHERE country IS NOT NULLGROUP BY distribution_channel;-- Based on RevenueSELECT distribution_channel, SUM(revenue)FROM shg_bookingWHERE country IS NOT NULLGROUP BY distribution_channel;-- Based on CancellationSELECT distribution_channel,        COUNT(*)FROM shg_bookingWHERE country IS NOT NULL AND status = 'Canceled'GROUP BY distribution_channelORDER BY COUNT(*);-- Based on lead timeSELECT distribution_channel,        AVG(lead_time) AS avg_leadtimeFROM shg_bookingWHERE country IS NOT NULLGROUP BY distribution_channelORDER BY avg_leadtime;

SELECT        CASE        WHEN lead_time >= 0 AND lead_time <= 120  THEN '4months'      WHEN lead_time > 120 AND lead_time <= 240 THEN '8months'     WHEN lead_time > 240 AND lead_time <= 360 THEN '1yr'     WHEN lead_time > 360 AND lead_time <= 480 THEN '1yr 4 months'     WHEN lead_time > 480 AND lead_time <= 600 THEN '1yr 8 months'     WHEN lead_time > 600 THEN 'over 1yr 8 months'     END AS Lead_time_range,     SUM (revenue) AS Total_revenueFROM shg_bookingWHERE country IS NOT NULLGROUP BY lead_time_rangeORDER BY Total_revenue DESC;  --Leadtime between four months had the highest revenue

-- 2 Based on cancellationSELECT       CASE            WHEN lead_time >= 0 AND lead_time <= 120  THEN '4months'      WHEN lead_time > 120 AND lead_time <= 240 THEN '8months'     WHEN lead_time > 240 AND lead_time <= 360 THEN '1yr'     WHEN lead_time > 360 AND lead_time <= 480 THEN '1yr 4 months'     WHEN lead_time > 480 AND lead_time <= 600 THEN '1yr 8 months'     WHEN lead_time > 600 THEN 'over 1yr 8 months'     END AS Lead_time_range,     COUNT (*) AS canceled_bookingsFROM shg_bookingWHERE country IS NOT NULL AND status = 'Canceled'GROUP BY lead_time_rangeORDER BY canceled_bookings;

-- 1.  What is the revenue contribution of online travel agents compared to offline travel agents?SELECT distribution_channel,       SUM (revenue) AS total_revenueFROM shg_bookingWHERE country IS NOT NULL AND status = 'Canceled' AND (distribution_channel = 'Online Travel Agent' OR distribution_channel = 'Offline Travel Agent')GROUP BY distribution_channel;

-- Revenue and cancellation rate across both channelSELECT distribution_channel,       SUM (revenue) AS total_revenue,    SUM(revenue) * 100.0 / SUM(SUM(revenue)) OVER () AS revenue_rateFROM shg_bookingWHERE country IS NOT NULL AND status = 'Canceled' AND (distribution_channel = 'Online Travel Agent' OR distribution_channel = 'Offline Travel Agent')GROUP BY distribution_channelORDER BY total_revenue;SELECT distribution_channel,       COUNT(booking_id) AS total_booking,   SUM(CASE WHEN status = 'Canceled' THEN 1 END) AS canceled_bookings,    (SUM(CASE WHEN status = 'Canceled' THEN 1 END) * 100.0) / COUNT(booking_id) AS cancelation_rateFROM shg_bookingWHERE country IS NOT NULL AND (distribution_channel = 'Online Travel Agent' OR distribution_channel = 'Offline Travel Agent')GROUP BY distribution_channel;