tembo_hotel_analysis.sql

set search_path to tembo_hotel;

select * from tembo_hotel.staging_bookings sb;

--> create a duplicate table

create table cleaned_staging_bookings as select * from staging_bookings;

select * from cleaned_staging_bookings;

--> cleaning part
-->apply INITCAP

update cleaned_staging_bookings
set guest_name = initcap(guest_name),
staff_name = initcap(staff_name),
guest_city = initcap(guest_city),
guest_nationality = initcap(guest_nationality),
room_type = initcap(room_type),
staff_department = initcap(staff_department),
payment_method = initcap(payment_method),
booking_status = initcap(booking_status),
service_used = initcap(service_used)
;

select * from cleaned_staging_bookings;


--> cleaning phone number

update cleaned_staging_bookings
SET guest_phone = replace(guest_phone,'-','');

select * from cleaned_staging_bookings;

update cleaned_staging_bookings
SET guest_phone = REGEXP_REPLACE(guest_phone, '^(\+254|254)', '0')
WHERE guest_phone ~ '^(\+254|254)';

select * from cleaned_staging_bookings
limit 40;

--> cleaning guest city column

SELECT distinct(guest_city) FROM cleaned_staging_bookings
ORDER BY GUEST_CITY;

update cleaned_staging_bookings
SET guest_city = replace(guest_city, 'Thikax', 'Thika');

select * from cleaned_staging_bookings
limit 40;

--> Trim

update cleaned_staging_bookings
SET
guest_name = TRIM(guest_name),
    guest_phone = TRIM(guest_phone),
    guest_city = TRIM(guest_city),
    guest_nationality = TRIM(guest_nationality),
    room_no = TRIM(room_no),
    room_type = TRIM(room_type),
    check_in_date = TRIM(check_in_date),
    check_out_date = TRIM(check_out_date),
    staff_name = TRIM(staff_name),
    staff_department = TRIM(staff_department),
    staff_salary = TRIM(staff_salary),
    payment_method = TRIM(payment_method),
    booking_status = TRIM(booking_status),
    total_amount = TRIM(total_amount),
    service_used = TRIM(service_used),
    service_price = TRIM(service_price),
    guest_rating = TRIM(guest_rating);




select * from cleaned_staging_bookings
limit 40;


SELECT distinct(room_type) FROM cleaned_staging_bookings
ORDER BY room_type;


UPDATE cleaned_staging_bookings
SET room_type =
CASE
    WHEN LOWER(room_type) IN ('std','standard') THEN 'Standard'
    WHEN LOWER(room_type) IN ('dlx','deluxe') THEN 'Deluxe'
    WHEN LOWER(room_type) LIKE '%suite%' THEN 'Suite'
    WHEN LOWER(room_type) LIKE '%penthouse%' THEN 'Penthouse'
    ELSE INITCAP(room_type)
END;

select * from cleaned_staging_bookings
limit 40;

--> cleaning payment_method column


SELECT distinct(payment_method) FROM cleaned_staging_bookings
ORDER BY payment_method;

UPDATE cleaned_staging_bookings
SET payment_method = replace(payment_method, 'Mpesa', 'M-Pesa');

select * from cleaned_staging_bookings
limit 40;


-->staff_salary
SELECT distinct(staff_salary) FROM cleaned_staging_bookings
ORDER BY staff_salary;

UPDATE cleaned_staging_bookings
SET staff_salary = replace(staff_salary, 'KES ,', '');


select * from cleaned_staging_bookings
limit 40;

--> total_amount

SELECT distinct(total_amount) FROM cleaned_staging_bookings
ORDER BY total_amount;

UPDATE cleaned_staging_bookings
SET total_amount = replace(total_amount, 'KES ,', '');
UPDATE cleaned_staging_bookings
 set total_amount = replace(total_amount, ',', '');
UPDATE cleaned_staging_bookings
set total_amount = replace(total_amount, 'KES ', '');


select * from cleaned_staging_bookings
limit 40;

--> Dates

--Cleaning up the dates columns check_in_date
-- This query looks for dates that have the / separator and converts them to date and casts them to text
update cleaned_staging_bookings
set check_in_date = to_date(check_in_date, 'DD-MM-YYYY')::text
where check_in_date like '%/%';
 
-- This also follows the same concept as above but for the date values separated by - and are of length 8 i.e 01-02-23
update cleaned_staging_bookings
set check_in_date = to_date(check_in_date, 'DD-MM-YY')::text
where check_in_date like '%-%' and length (check_in_date) = 8;
 
-- For this, the separator is - but the count of the date characters is 10 i.e. 01-02-2023.
-- The split_part in this scenario splits the date values by '-' and focuses on the specified part of the split values, in this case, 1.
-- This corresponds to our month separate part from the date. It then casts it to an integer and checks if it is less than 12.
update cleaned_staging_bookings
set check_in_date = to_date(check_in_date, 'MM-DD-YYYY')::text
where check_in_date like '%-%' and length (check_in_date) = 10 and split_part (check_in_date,'-',1)::integer <=12;
 
update cleaned_staging_bookings
set check_in_date = to_date(check_in_date, 'DD-MM-YYYY')::text
WHERE check_in_date = '15-11-2024';
 
 
-- check_out_date
UPDATE cleaned_staging_bookings
SET check_out_date = to_date(check_out_date, 'DD-MM-YYYY') ::TEXT
WHERE check_out_date LIKE '%/%';
 
 
UPDATE cleaned_staging_bookings
SET check_out_date = to_date(check_out_date, 'DD-MM-YY') :: text
WHERE check_out_date LIKE '%-%' AND length(check_out_date) = 8 AND split_part(check_out_date, '-',2):: integer <=12;
 
UPDATE cleaned_staging_bookings
SET check_out_date = to_date(check_out_date, 'MM-DD-YYYY') ::TEXT
WHERE check_out_date LIKE '%-%' AND split_part(check_out_date, '-',1)::integer <=12;
 
UPDATE cleaned_staging_bookings
SET check_out_date = to_date(check_out_date, 'DD-MM-YYYY') :: text
WHERE check_out_date = '17-11-2024';


-->Add temporary date columns
ALTER TABLE cleaned_staging_bookings ADD COLUMN check_in_date_temp DATE;
ALTER TABLE cleaned_staging_bookings ADD COLUMN check_out_date_temp DATE;
 
-- Populate with correct conversion (adjust format mask to your actual data)
UPDATE cleaned_staging_bookings
SET check_in_date_temp = TO_DATE(check_in_date, 'YYYY-MM-DD'),
check_out_date_temp = TO_DATE(check_out_date, 'YYYY-MM-DD');
 
-- Swap if necessary
UPDATE cleaned_staging_bookings
SET
check_in_date_temp = check_out_date_temp,
check_out_date_temp = check_in_date_temp
WHERE check_in_date_temp > check_out_date_temp;
 
-- Recalculate nights_stayed
update cleaned_staging_bookings
SET nights_stayed = (check_out_date_temp - check_in_date_temp);
 
-- Drop old text columns and rename
ALTER TABLE cleaned_staging_bookings DROP COLUMN check_in_date, DROP COLUMN check_out_date;
ALTER TABLE cleaned_staging_bookings RENAME COLUMN check_in_date_temp TO check_in_date;
ALTER TABLE cleaned_staging_bookings RENAME COLUMN check_out_date_temp TO check_out_date;
 
-- Change column types to DATE
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN check_in_date TYPE DATE,
ALTER COLUMN check_out_date TYPE DATE;


select * from cleaned_staging_bookings
limit 50;

--> to remove duplicates.

SELECT booking_id,count(booking_id) AS countid_
FROM cleaned_staging_bookings
GROUP BY booking_id
having count(booking_id) >1;

WITH duplicates AS (
    SELECT ctid,  -- unique physical row identifier in PostgreSQL
           booking_id,
           ROW_NUMBER() OVER (PARTITION BY booking_id ORDER BY ctid) AS rn
    FROM cleaned_staging_bookings
)
DELETE FROM cleaned_staging_bookings



select * from cleaned_staging_bookings
limit 50;
WHERE ctid IN (
    SELECT ctid FROM duplicates WHERE rn > 1
);


-- 1. booking_id --VARCHAR(50)
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN booking_id TYPE VARCHAR(50)
USING CAST(booking_id AS VARCHAR(50));
 
-- 2. guest_name --VARCHAR(100)
--ALTER TABLE -- 1. booking_id --VARCHAR(50)
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN booking_id TYPE VARCHAR(50)
USING CAST(booking_id AS VARCHAR(50));
 
-- 2. guest_name --VARCHAR(100)
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN guest_name TYPE VARCHAR(100)
USING CAST(guest_name AS VARCHAR(100));
 
-- 3. guest_phone --VARCHAR(20)
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN guest_phone TYPE VARCHAR(10)
USING CAST(guest_phone AS VARCHAR(10));
 
-- 4. guest_city ----VARCHAR(100)
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN guest_city TYPE VARCHAR(50)
USING CAST(guest_city AS VARCHAR(50));
 
-- 5. guest_nationality ---VARCHAR(50)
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN guest_nationality TYPE VARCHAR(50)
USING CAST(guest_nationality AS VARCHAR(50));
 
-- 6. room_no -----VARCHAR(10)
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN room_no TYPE INTEGER
USING CAST(room_no AS INTEGER );
 
-- 7. room_type -----VARCHAR(50)
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN room_type TYPE VARCHAR (50)
USING CAST(room_type AS VARCHAR (50));
 
-- 8. room_rate_per_night ----INTEGER
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN room_rate_per_night TYPE INTEGER
USING CAST(room_rate_per_night AS INTEGER);
 
-- 9. check_in_date ----- DATE
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN check_in_date TYPE DATE
USING CAST(check_in_date AS DATE);
 
-- 10. check_out_date ---- DATE
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN check_out_date TYPE DATE
USING CAST(check_out_date AS DATE);
 
-- 11. nights_stayed ----INTEGER
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN nights_stayed TYPE INTEGER
USING CAST(nights_stayed AS INTEGER);
 
-- 12. staff_name ----VARCHAR(100)
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN staff_name TYPE VARCHAR(100)
USING CAST(staff_name AS VARCHAR(100));
 
-- 13. staff_department --- VARCHAR(100)
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN staff_department TYPE VARCHAR(100)
USING CAST(staff_department AS VARCHAR(100));
 

 
 
ALTER TABLE cleaned_bookings
ALTER COLUMN staff_salary TYPE INTEGER
USING CAST(NULLIF(TRIM(staff_salary), '') AS INTEGER);
 
 
 
-- 15. payment_method ---VARCHAR(50)
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN payment_method TYPE VARCHAR(50)
USING CAST(payment_method AS VARCHAR(50));
 
-- 16. booking_status --- VARCHAR(50)
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN booking_status TYPE VARCHAR(50)
USING CAST(booking_status AS VARCHAR(50));
 
-- 17. total_amount ----INTEGER
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN total_amount type numeric
USING CAST(total_amount AS NUMERIC);

select distinct total_amount from cleaned_staging_bookings;
 
SELECT COUNT() AS* totalamount_
FROM cleaned_bookings;
 
-- 18. service_used -----VARCHAR(200)
ALTER TABLE cleaned_bookings
ALTER COLUMN service_used TYPE VARCHAR(200)
USING CAST(service_used AS VARCHAR(200));
 
-- 19. service_price -- INTEGER
ALTER TABLE cleaned_bookings
ALTER COLUMN service_price TYPE INTEGER
USING CAST(service_price AS INTEGER);
 
-- 20. guest_rating ----INTEGER
ALTER TABLE cleaned_bookings
ALTER COLUMN guest_rating TYPE INTEGER
USING CAST(guest_rating AS INTEGER);
ALTER COLUMN guest_name TYPE VARCHAR(100)
USING CAST(guest_name AS VARCHAR(100));
 
-- 3. guest_phone --VARCHAR(20)
ALTER TABLE cleaned_bookings
ALTER COLUMN guest_phone TYPE VARCHAR(10)
USING CAST(guest_phone AS VARCHAR(10));
 
-- 4. guest_city ----VARCHAR(100)
ALTER TABLE cleaned_bookings
ALTER COLUMN guest_city TYPE VARCHAR(50)
USING CAST(guest_city AS VARCHAR(50));
 
-- 5. guest_nationality ---VARCHAR(50)
ALTER TABLE cleaned_bookings
ALTER COLUMN guest_nationality TYPE VARCHAR(50)
USING CAST(guest_nationality AS VARCHAR(50));
 
-- 6. room_no -----VARCHAR(10)
ALTER TABLE cleaned_bookings
ALTER COLUMN room_no TYPE INTEGER
USING CAST(room_no AS INTEGER );
 
-- 7. room_type -----VARCHAR(50)
ALTER TABLE cleaned_bookings
ALTER COLUMN room_type TYPE VARCHAR (50)
USING CAST(room_type AS VARCHAR (50));
 
-- 8. room_rate_per_night ----INTEGER
ALTER TABLE cleaned_bookings
ALTER COLUMN room_rate_per_night TYPE INTEGER
USING CAST(room_rate_per_night AS INTEGER);
 
-- 9. check_in_date ----- DATE
ALTER TABLE cleaned_bookings
ALTER COLUMN check_in_date TYPE DATE
USING CAST(check_in_date AS DATE);
 
-- 10. check_out_date ---- DATE
ALTER TABLE cleaned_bookings
ALTER COLUMN check_out_date TYPE DATE
USING CAST(check_out_date AS DATE);
 
-- 11. nights_stayed ----INTEGER
ALTER TABLE cleaned_bookings
ALTER COLUMN nights_stayed TYPE INTEGER
USING CAST(nights_stayed AS INTEGER);
 
-- 12. staff_name ----VARCHAR(100)
ALTER TABLE cleaned_bookings
ALTER COLUMN staff_name TYPE VARCHAR(100)
USING CAST(staff_name AS VARCHAR(100));
 
-- 13. staff_department --- VARCHAR(100)
ALTER TABLE cleaned_bookings
ALTER COLUMN staff_department TYPE VARCHAR(100)
USING CAST(staff_department AS VARCHAR(100));
 
-- 14. staff_salary ---INTEGER
-- 1. Remove non‑numeric characters using TRANSLATE
-- (keep only digits 0-9, delete everything else)
UPDATE cleaned_bookings
SET staff_salary = TRANSLATE(
staff_salary,
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz$ ,.-/\\''"',
'0123456789'
)
WHERE staff_salary IS NOT NULL;
 
UPDATE cleaned_bookings
SET staff_salary = NULL
WHERE staff_salary ILIKE '%KES%';
 
UPDATE cleaned_bookings
SET staff_salary = NULL
WHERE TRIM(staff_salary) = '';
 
ALTER TABLE cleaned_bookings
ALTER COLUMN staff_salary TYPE INTEGER
USING CAST(NULLIF(TRIM(staff_salary), '') AS INTEGER);
 
 
 
-- 15. payment_method ---VARCHAR(50)
ALTER TABLE cleaned_bookings
ALTER COLUMN payment_method TYPE VARCHAR(50)
USING CAST(payment_method AS VARCHAR(50));
 
-- 16. booking_status --- VARCHAR(50)
ALTER TABLE cleaned_bookings
ALTER COLUMN booking_status TYPE VARCHAR(50)
USING CAST(booking_status AS VARCHAR(50));
 
-- 17. total_amount ----INTEGER
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN total_amount TYPE numeric
USING (NULLIF(TRIM(total_amount), '')::numeric);
 
select distinct(total_amount)
from cleaned_staging_bookings;

 
-- 18. service_used -----VARCHAR(200)
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN service_used TYPE VARCHAR(200)
USING CAST(service_used AS VARCHAR(200));
 
-- 19. service_price -- INTEGER
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN service_price TYPE numeric
USING (NULLIF(TRIM(service_price), '')::numeric);
 
-- 20. guest_rating ----INTEGER
ALTER TABLE cleaned_staging_bookings
ALTER COLUMN guest_rating TYPE numeric 
USING (NULLIF(TRIM(guest_rating), '')::numeric);



--> analysis.

select * from cleaned_staging_bookings
limit 50;

select distinct (booking_status) from cleaned_staging_bookings;

SELECT 
    TO_CHAR(check_in_date, 'YYYY-MM') AS month,
    SUM(total_amount) AS total_revenue
FROM tembo_hotel.cleaned_staging_bookings
WHERE booking_status = 'Checked Out'
  AND total_amount IS NOT NULL
GROUP BY TO_CHAR(check_in_date, 'YYYY-MM')
ORDER BY month;

---TOTAL REVENUE BY ROOM_TYPE

SELECT 
    room_type,
    SUM(total_amount) AS total_revenue,
    COUNT(*) AS number_of_bookings
FROM tembo_hotel.cleaned_staging_bookings
WHERE booking_status = 'Checked Out'
  AND total_amount IS NOT NULL
GROUP BY room_type
ORDER BY total_revenue DESC;

---TOTAL REVENUE BY PAYMENT METHOD
SELECT 
    payment_method,
    SUM(total_amount) AS total_revenue,
    COUNT(*) AS number_of_transactions
FROM tembo_hotel.cleaned_staging_bookings
WHERE booking_status = 'Checked Out'
  AND total_amount IS NOT NULL
GROUP BY payment_method
ORDER BY total_revenue DESC;

---ROOM TYPES BOOKED THE MOST

SELECT 
    room_type,
    COUNT(*) AS total_bookings
FROM tembo_hotel.cleaned_staging_bookings
GROUP BY room_type
ORDER BY total_bookings DESC;


-----AVERAGE NIGHTS STAYED PER ROOM
SELECT 
    room_type,
    ROUND(AVG(nights_stayed), 2) AS avg_nights_stayed,
    COUNT(*) AS number_of_stays
FROM tembo_hotel.cleaned_staging_bookings
WHERE booking_status = 'Checked Out'
  AND nights_stayed IS NOT NULL
  AND nights_stayed > 0
GROUP BY room_type
ORDER BY avg_nights_stayed DESC;

----GUEST INSIGHTS

select distinct (guest_city) from cleaned_staging_bookings;


UPDATE tembo_hotel.cleaned_staging_bookings
SET guest_city = 'Unknown'
WHERE guest_city IS NULL
   OR TRIM(guest_city) = '';
SELECT 
    guest_city,
    COUNT(*) AS number_of_bookings
FROM tembo_hotel.cleaned_staging_bookings
WHERE guest_city IS NOT NULL
GROUP BY guest_city
ORDER BY number_of_bookings DESC
LIMIT 10;

---AVERAGE RATINGS PER ROOM
SELECT 
    room_type,
    ROUND(AVG(guest_rating), 2) AS avg_rating,
    COUNT(guest_rating) AS number_of_ratings  
FROM tembo_hotel.cleaned_staging_bookings
WHERE booking_status = 'Checked Out'
  AND guest_rating IS NOT NULL
GROUP BY room_type
ORDER BY avg_rating DESC;

---STAFF WITH THE MOST BOOKINGS
SELECT 
    staff_name,
    staff_department,
    COUNT(*) AS number_of_bookings
FROM tembo_hotel.cleaned_staging_bookings
WHERE booking_status = 'Checked Out'
  AND staff_name IS NOT NULL
  AND TRIM(staff_name) != ''
GROUP BY staff_name, staff_department
ORDER BY number_of_bookings DESC
LIMIT 1;

-----DEPARTMENT WITH MOST REVENUE
SELECT 
    staff_department,
    ROUND(SUM(total_amount), 2) AS total_revenue,
    COUNT(*) AS total_bookings_handled
FROM tembo_hotel.cleaned_staging_bookings
WHERE booking_status = 'Checked Out'
  AND total_amount IS NOT NULL
  AND staff_department IS NOT NULL
  AND TRIM(staff_department) != ''
GROUP BY staff_department
ORDER BY total_revenue desc
limit 1;

----MONTHLY TRENDS
WITH monthly_revenue AS (
    SELECT 
        TO_CHAR(check_in_date, 'YYYY-MM') AS month,
        SUM(total_amount) AS revenue
    FROM tembo_hotel.cleaned_staging_bookings
    WHERE booking_status = 'Checked Out'
      AND total_amount IS NOT NULL
    GROUP BY TO_CHAR(check_in_date, 'YYYY-MM')
)
SELECT 
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS previous_month_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month)) / 
        NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2
    ) AS month_growth_percent
FROM monthly_revenue
ORDER BY month;


----busiest vs quietest months
WITH monthly_activity AS (
    SELECT 
        TO_CHAR(check_in_date, 'YYYY-MM') AS month,
        COUNT(*) AS total_bookings
    FROM tembo_hotel.cleaned_staging_bookings
    WHERE booking_status = 'Checked Out'
    GROUP BY TO_CHAR(check_in_date, 'YYYY-MM')
)
SELECT 
    month,
    total_bookings,
    CASE 
        WHEN total_bookings = (SELECT MAX(total_bookings) FROM monthly_activity) THEN 'Busiest'
        WHEN total_bookings = (SELECT MIN(total_bookings) FROM monthly_activity) THEN 'Quietest'
        ELSE 'Other'
    END AS month_type
FROM monthly_activity
ORDER BY total_bookings DESC;

------cancellation rates
select distinct (booking_status) from cleaned_staging_bookings;

SELECT 
    room_type,
    COUNT(*) AS total_bookings,
    COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No-show')) AS lost_bookings,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No-show')) / COUNT(*), 
        2
    ) AS cancellation_rate_percent
FROM tembo_hotel.cleaned_staging_bookings
GROUP BY room_type
ORDER BY cancellation_rate_percent DESC;

------revenue lost from cancellation
SELECT 
    room_type,
    COUNT(*) AS lost_bookings,
    SUM(total_amount) AS revenue_lost
FROM tembo_hotel.cleaned_staging_bookings
WHERE booking_status IN ('Cancelled', 'No-show')
  AND total_amount IS NOT NULL
GROUP BY room_type
ORDER BY revenue_lost DESC;

------views creation
----1.revenue view
CREATE  VIEW tembo_hotel.revenue_analysis AS
SELECT 
    TO_CHAR(check_in_date, 'YYYY-MM') AS month,
    room_type,
    payment_method,
    COUNT(*) AS total_bookings,
    ROUND(SUM(total_amount), 2) AS total_revenue,
    ROUND(AVG(total_amount), 2) AS avg_booking_value
FROM tembo_hotel.cleaned_staging_bookings
WHERE booking_status = 'Checked Out'
  AND total_amount IS NOT NULL
GROUP BY month, room_type, payment_method
ORDER BY month, total_revenue DESC;

SELECT * FROM tembo_hotel.revenue_analysis WHERE month = '2024-11';
SELECT month, SUM(total_revenue) FROM tembo_hotel.revenue_analysis GROUP BY month;

---2,rooms_occupancy
CREATE  VIEW tembo_hotel.occupancy_analysis AS
SELECT 
    room_type,
    COUNT(*) AS total_completed_stays,
    ROUND(AVG(nights_stayed), 2) AS avg_nights_stayed,
    MIN(nights_stayed) AS min_nights_stayed,
    MAX(nights_stayed) AS max_nights_stayed
FROM tembo_hotel.cleaned_staging_bookings
WHERE booking_status = 'Checked Out'
  AND nights_stayed IS NOT NULL
  AND nights_stayed > 0
GROUP BY room_type
ORDER BY total_completed_stays DESC;

SELECT * FROM tembo_hotel.occupancy_analysis;

----3. guest insights
CREATE  VIEW guest_views AS
SELECT 
    guest_city,
    COUNT(*) AS total_bookings,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_spend
FROM tembo_hotel.cleaned_staging_bookings
GROUP BY guest_city;

SELECT * FROM tembo_hotel.guest_views;

----staff_view
CREATE  VIEW tembo_hotel.staff_performance AS
SELECT 
    staff_name,
    staff_department,
    COUNT(*) AS bookings_handled,
    ROUND(SUM(total_amount), 2) AS revenue_generated,
    ROUND(AVG(total_amount), 2) AS avg_booking_value
FROM tembo_hotel.cleaned_staging_bookings
WHERE booking_status = 'Checked Out'
  AND total_amount IS NOT NULL
  AND staff_name IS NOT NULL
  AND TRIM(staff_name) != ''
GROUP BY staff_name, staff_department
ORDER BY revenue_generated DESC;

SELECT * FROM tembo_hotel.staff_performance LIMIT 10;


------indexes
-- Index on room_no 
CREATE INDEX  idx__room_no ON tembo_hotel.cleaned_staging_bookings (room_no);

-- Index on staff_name 
CREATE INDEX  idx_staff_name ON tembo_hotel.cleaned_staging_bookings (staff_name);

-- Index on guest_city 
CREATE INDEX  idx_guest_city ON tembo_hotel.cleaned_staging_bookings (guest_city);

-- Index on check_in_date 
CREATE INDEX  idx_check_in_date ON tembo_hotel.cleaned_staging_bookings (check_in_date);