Cleaning and Analyzing Tembo Hotel's Bookings

One guest at Tembo Hotel & Suites checked out three days before checking in. The spreadsheet says so. It also spells Nairobi as Nairobi and as NAIROBI, files a town called Thikax under guest cities, and writes January dates as 10/01/2024, 01-12-2024 and 28-01-24, three formats in one column.

That spreadsheet is the whole booking history of a mid-range business hotel in Nairobi, open since 2023. The Hotel Director wants to know which rooms earn the most, which months are busiest, how the staff are doing and whether guests are happy. Nobody can answer those questions from a file like this. Any total you add up is wrong by an amount you can't measure.

This article follows the project from the raw CSV to the finished answers. I load the file into PostgreSQL, fix the damage one defect at a time, and then run the six analyses the Director asked for. I ran every query below, in order, against the real file on PostgreSQL 16. The output under each query is what came back, laid out the way DBeaver shows it: a result grid for SELECT, a statistics line for everything else.

The brief in plain words

The Director's message boils down to four worries: money, busy periods, staff and guest happiness. The brief turns them into six groups of questions:

1. Revenue by month, by room type and by payment method.
2. Which room types are booked most, and how many nights a guest stays on average.
3. The top 10 cities guests come from, and the average rating per room type.
4. Which staff member handled the most bookings, and which department earns the most.
5. Month-over-month revenue growth (with a window function), and the busiest and quietest months.
6. The cancellation rate per room type, and the revenue lost to cancellations and no-shows.

The file is tembo_hotel_dirty.csv: 286 rows and 20 columns, one row per booking. Each row carries the guest, the room, the dates, the staff member who handled it, how the guest paid, any extra service (spa, laundry, airport pickup) and the guest's rating.

Note: To gain access to the files used in this project, check out this Github repository.

The plan: register first, get a room second

A guest doesn't walk straight to a room. They stop at the front desk, where staff check the details, and only then get a key. The database work follows the same order:

tembo_hotel_dirty.csv
        |
        v
staging_bookings      every column is TEXT, so nothing is rejected on arrival
        |   fix each defect with UPDATE / DELETE
        v
clean_bookings        real types and CHECK constraints, so bad data can't get in
        |
        v
v_clean_bookings      a view that adds a month column for the analysis
        |
        v
six business questions

The staging table exists because a strict table would refuse the file at the first KES 34000 in a numeric column. With everything as text, the load always succeeds, and the cleaning happens where I can watch it, one column at a time.

Step 1: Ingest the CSV without losing a row

I keep the project in its own schema so it never collides with anything else in the database.

CREATE SCHEMA IF NOT EXISTS tembo;
SET search_path TO tembo;

DROP TABLE IF EXISTS tembo.staging_bookings;
CREATE TABLE tembo.staging_bookings (
    booking_id          TEXT,
    guest_name          TEXT,
    guest_phone         TEXT,
    guest_city          TEXT,
    guest_nationality   TEXT,
    room_no             TEXT,
    room_type           TEXT,
    room_rate_per_night TEXT,
    check_in_date       TEXT,
    check_out_date      TEXT,
    nights_stayed       TEXT,
    staff_name          TEXT,
    staff_department    TEXT,
    staff_salary        TEXT,
    payment_method      TEXT,
    booking_status      TEXT,
    total_amount        TEXT,
    service_used        TEXT,
    service_price       TEXT,
    guest_rating        TEXT
);

Statistics tab: Updated Rows = 0

In DBeaver you can right-click the table and choose Import Data, then point it at the CSV. If you prefer a script, PostgreSQL's COPY does the same job in one line:

COPY tembo.staging_bookings
FROM '/path/to/tembo_hotel_dirty.csv'
WITH (FORMAT csv, HEADER true);

Statistics tab: Updated Rows = 286

COPY reads the file on the database server, so the path must be visible to the server and your role needs the right privileges. From a client machine, use \copy in psql or the DBeaver wizard.

Next, count the rows and look at the first few. This is the sanity check I run after every load.

SELECT COUNT(*) AS rows_loaded
FROM tembo.staging_bookings;

Result grid: 1 row fetched

rows_loaded
286

SELECT booking_id, guest_name, guest_phone, guest_city, room_type,
       check_in_date, total_amount
FROM tembo.staging_bookings
LIMIT 8;

Result grid: 8 rows fetched

booking_id	guest_name	guest_phone	guest_city	room_type	check_in_date	total_amount
BK0001	ALICE MWANGI	0712345678	Nairobi	Standard	2024-01-05	6000
BK0002	brian otieno	0723456789	Mombasa	Standard	2024-01-06	5500
BK0003	Carol Wanjiku	0734567890	Kisumu	Deluxe	2024-01-07	25500
BK0004	David Kamau	0745-678-901	Nairobi	Deluxe	2024-01-08	18200
BK0005	Esther Njoroge	[phone]	Nakuru	Suite	2024-01-09	60000
BK0006	Felix Hassan	0767890123	Nairobi	Standard	10/01/2024	27500
BK0007	Grace Achieng	0778901234	Eldoret	Deluxe	01-12-2024	43000
BK0008	Henry Korir	0789012345	[NULL]	Suite	2024-01-13	30000

286 rows, as promised. Look at the grid, though. Row 3 has stray spaces around a name, row 4 has dashes in a phone number, row 5 has a +254 prefix instead of a leading zero, row 7 has a date in month-day-year order, and row 8 has an empty city. We are just getting started.

Step 2: Audit the damage before touching anything

I don't fix by feel. One query counts every kind of damage I can test for, so I know the size of the job and can prove afterwards that it's done. Each line is a rule the clean data must obey, tested against the raw text.

SELECT 'Duplicate booking_id rows' AS problem,
       COUNT(*) - COUNT(DISTINCT booking_id) AS rows_affected
FROM tembo.staging_bookings
UNION ALL SELECT 'Names with stray spaces or odd casing', COUNT(*)
  FROM tembo.staging_bookings WHERE guest_name <> INITCAP(TRIM(guest_name))
UNION ALL SELECT 'Phones with dashes, +254 or padding', COUNT(*)
  FROM tembo.staging_bookings WHERE guest_phone !~ '^0[0-9]{9}$'
UNION ALL SELECT 'Cities blank, misspelt or oddly cased', COUNT(*)
  FROM tembo.staging_bookings
  WHERE guest_city IS NULL OR guest_city !~ '^[A-Z][a-z]+$' OR guest_city = 'Thikax'
UNION ALL SELECT 'Nationality casing', COUNT(*)
  FROM tembo.staging_bookings WHERE guest_nationality <> 'Kenyan'
UNION ALL SELECT 'Room type aliases (Std, DLX, lower case)', COUNT(*)
  FROM tembo.staging_bookings WHERE room_type NOT IN ('Standard','Deluxe','Suite','Penthouse')
UNION ALL SELECT 'Payment method aliases', COUNT(*)
  FROM tembo.staging_bookings WHERE payment_method NOT IN ('Cash','Card','Bank Transfer','M-Pesa')
UNION ALL SELECT 'Booking status casing', COUNT(*)
  FROM tembo.staging_bookings WHERE booking_status NOT IN ('Checked Out','Cancelled','No Show')
UNION ALL SELECT 'Dates not in YYYY-MM-DD', COUNT(*)
  FROM tembo.staging_bookings
  WHERE check_in_date !~ '^\d{4}-\d{2}-\d{2}$' OR check_out_date !~ '^\d{4}-\d{2}-\d{2}$'
UNION ALL SELECT 'Check-out before check-in', COUNT(*)
  FROM tembo.staging_bookings
  WHERE check_in_date ~ '^\d{4}-\d{2}-\d{2}$' AND check_out_date ~ '^\d{4}-\d{2}-\d{2}$'
    AND check_out_date < check_in_date
UNION ALL SELECT 'Nights stayed below 1', COUNT(*)
  FROM tembo.staging_bookings WHERE nights_stayed::INT < 1
UNION ALL SELECT 'Staff salary unreadable', COUNT(*)
  FROM tembo.staging_bookings WHERE staff_salary !~ '^\d+$'
UNION ALL SELECT 'Total amount blank or not a plain number', COUNT(*)
  FROM tembo.staging_bookings WHERE total_amount IS NULL OR total_amount !~ '^\d+$'
UNION ALL SELECT 'Rating padded or outside 1 to 5', COUNT(*)
  FROM tembo.staging_bookings WHERE guest_rating !~ '^[1-5]$';

Result grid: 14 rows fetched

problem	rows_affected
Duplicate booking_id rows	1
Names with stray spaces or odd casing	45
Phones with dashes, +254 or padding	29
Cities blank, misspelt or oddly cased	45
Nationality casing	1
Room type aliases (Std, DLX, lower case)	29
Payment method aliases	15
Booking status casing	15
Dates not in YYYY-MM-DD	44
Check-out before check-in	2
Nights stayed below 1	1
Staff salary unreadable	14
Total amount blank or not a plain number	30
Rating padded or outside 1 to 5	17

Fourteen kinds of damage. (Missing phone numbers and ratings are not on the list, because a value that was never recorded is a gap, not a defect.) Some are cosmetic (casing), some silently change an answer (a duplicate booking counts twice), and some make an answer impossible (a total that is just a comma). The !~ operator means "does not match this regular expression", which is the fastest way to find everything that breaks a rule I can describe.

Step 3: Fix the data, one defect at a time

Every fix follows the same rhythm. Look at the problem, write the UPDATE, then run a check that proves it worked. I keep the WHERE clause on each update so the "Updated Rows" count tells me how many rows I really touched. An update with no WHERE clause always reports 285, which teaches you nothing.

3.1 Duplicate bookings

SELECT booking_id, guest_name, check_in_date, total_amount, COUNT(*) AS copies
FROM tembo.staging_bookings
GROUP BY booking_id, guest_name, check_in_date, total_amount
HAVING COUNT(*) > 1;

Result grid: 1 row fetched

booking_id	guest_name	check_in_date	total_amount	copies
BK0006	Felix Hassan	10/01/2024	27500	2

Booking BK0006 appears twice, identical in every column. Revenue counted twice is the classic quiet error. In PostgreSQL every row has a hidden physical address called ctid. I keep the lowest address for each booking_id and delete the rest.

DELETE FROM tembo.staging_bookings
WHERE ctid NOT IN (
    SELECT MIN(ctid)
    FROM tembo.staging_bookings
    GROUP BY booking_id
);

Statistics tab: Updated Rows = 1

SELECT COUNT(*) AS rows_left, COUNT(DISTINCT booking_id) AS unique_ids
FROM tembo.staging_bookings;

Result grid: 1 row fetched

rows_left	unique_ids
285	285

285 rows and 285 unique IDs. (MIN(ctid) needs PostgreSQL 14 or newer. On older versions, use ROW_NUMBER() in a CTE.)

3.2 Guest names

The LENGTH column exposes what the eye can't see: trailing spaces.

SELECT guest_name, LENGTH(guest_name) AS chars, COUNT(*) AS rows_affected
FROM tembo.staging_bookings
WHERE guest_name <> INITCAP(TRIM(guest_name))
GROUP BY guest_name
ORDER BY rows_affected DESC;

Result grid: 5 rows fetched

guest_name	chars	rows_affected
ALICE MWANGI	12	15
brian otieno	12	14
Carol Wanjiku	17	14
grace wanjiru	13	1
PETER MWANGI	12	1

INITCAP capitalises each word and lower-cases the rest. TRIM strips the spaces.

UPDATE tembo.staging_bookings
SET guest_name = INITCAP(TRIM(guest_name))
WHERE guest_name <> INITCAP(TRIM(guest_name));

Statistics tab: Updated Rows = 45

The check is the same query as before. It should return nothing.

SELECT guest_name, LENGTH(guest_name) AS chars, COUNT(*) AS rows_affected
FROM tembo.staging_bookings
WHERE guest_name <> INITCAP(TRIM(guest_name))
GROUP BY guest_name;

Result grid: 0 rows fetched

guest_name	chars	rows_affected

3.3 Phone numbers

QUOTE_LITERAL wraps each value in quotes, so blanks and padding become visible.

SELECT QUOTE_LITERAL(guest_phone) AS phone_as_typed, COUNT(*) AS rows_affected
FROM tembo.staging_bookings
WHERE guest_phone !~ '^0[0-9]{9}$'
GROUP BY guest_phone
ORDER BY rows_affected DESC;

Result grid: 3 rows fetched

phone_as_typed	rows_affected
'[phone]'	14
'0745-678-901'	14
' 0700000004 '	1

Three shapes: dashes, a +254 prefix and padding. The 14 blank phones don't appear, because COPY loads an empty CSV field as NULL. Some import tools load an empty string instead, so the CASE below turns those into NULL as well. The target is the local ten-digit form, 07xxxxxxxx: strip everything that isn't a digit, and turn a leading 254 into 0.

UPDATE tembo.staging_bookings
SET guest_phone = CASE
    WHEN NULLIF(TRIM(guest_phone), '') IS NULL THEN NULL
    WHEN TRIM(guest_phone) LIKE '+254%'
        THEN '0' || SUBSTRING(REGEXP_REPLACE(guest_phone, '[^0-9]', '', 'g') FROM 4)
    ELSE REGEXP_REPLACE(guest_phone, '[^0-9]', '', 'g')
END
WHERE guest_phone !~ '^0[0-9]{9}$';

Statistics tab: Updated Rows = 29

SELECT COUNT(*) FILTER (WHERE guest_phone IS NULL) AS missing_phones,
       COUNT(*) FILTER (WHERE guest_phone !~ '^0[0-9]{9}$') AS malformed_phones
FROM tembo.staging_bookings;

Result grid: 1 row fetched

missing_phones	malformed_phones
14	0

3.4 Cities

SELECT QUOTE_LITERAL(guest_city) AS city_as_typed, COUNT(*) AS bookings
FROM tembo.staging_bookings
GROUP BY guest_city
ORDER BY bookings DESC;

Result grid: 12 rows fetched

city_as_typed	bookings
'Nairobi'	114
'Eldoret'	28
'Kisumu'	27
'Mombasa'	15
'NAIROBI'	15
'Nakuru'	14
'Machakos'	14
'Nyeri'	14
'Meru'	14
[NULL]	14
'Thikax'	14
'kisumu'	2

Nairobi has three spellings. Thikax is a typo for Thika, which is the only reading that fits: it appears exactly as often as the other small towns and no such place exists. That is an assumption, and I say so here so a reader can overrule it. Blank cities become Unknown rather than staying NULL, so the label is visible in every report and the Director can see how much is missing.

UPDATE tembo.staging_bookings
SET guest_city = CASE
    WHEN NULLIF(TRIM(guest_city), '') IS NULL THEN 'Unknown'
    WHEN LOWER(TRIM(guest_city)) = 'thikax' THEN 'Thika'
    ELSE INITCAP(TRIM(guest_city))
END
WHERE guest_city IS NULL OR guest_city !~ '^[A-Z][a-z]+$' OR guest_city = 'Thikax';

Statistics tab: Updated Rows = 45

SELECT guest_city, COUNT(*) AS bookings
FROM tembo.staging_bookings
GROUP BY guest_city
ORDER BY bookings DESC;

Result grid: 10 rows fetched

guest_city	bookings
Nairobi	129
Kisumu	29
Eldoret	28
Mombasa	15
Meru	14
Unknown	14
Nakuru	14
Thika	14
Machakos	14
Nyeri	14

3.5 Short vocabularies: room type, payment, status, nationality

These four columns hold a handful of allowed values each, so one pattern fits all: map every known variant to one spelling. The room types hide the most, since Std and DLX are abbreviations that no casing function can repair.

UPDATE tembo.staging_bookings
SET room_type = CASE
    WHEN UPPER(TRIM(room_type)) IN ('STANDARD', 'STD') THEN 'Standard'
    WHEN UPPER(TRIM(room_type)) IN ('DELUXE', 'DLX')   THEN 'Deluxe'
    WHEN UPPER(TRIM(room_type)) = 'SUITE'              THEN 'Suite'
    WHEN UPPER(TRIM(room_type)) = 'PENTHOUSE'          THEN 'Penthouse'
    ELSE room_type
END
WHERE room_type NOT IN ('Standard', 'Deluxe', 'Suite', 'Penthouse');

Statistics tab: Updated Rows = 29

UPDATE tembo.staging_bookings
SET payment_method = 'M-Pesa'
WHERE UPPER(TRIM(payment_method)) IN ('MPESA', 'M PESA')
  AND payment_method <> 'M-Pesa';

Statistics tab: Updated Rows = 15

UPDATE tembo.staging_bookings
SET booking_status = INITCAP(TRIM(booking_status))
WHERE booking_status <> INITCAP(TRIM(booking_status));

Statistics tab: Updated Rows = 15

UPDATE tembo.staging_bookings
SET guest_nationality = INITCAP(TRIM(guest_nationality))
WHERE guest_nationality <> INITCAP(TRIM(guest_nationality));

Statistics tab: Updated Rows = 1

One query verifies all four columns at once:

SELECT 'room_type' AS column_name, room_type AS value, COUNT(*) AS bookings
FROM tembo.staging_bookings GROUP BY room_type
UNION ALL
SELECT 'payment_method', payment_method, COUNT(*)
FROM tembo.staging_bookings GROUP BY payment_method
UNION ALL
SELECT 'booking_status', booking_status, COUNT(*)
FROM tembo.staging_bookings GROUP BY booking_status
UNION ALL
SELECT 'guest_nationality', guest_nationality, COUNT(*)
FROM tembo.staging_bookings GROUP BY guest_nationality
ORDER BY column_name, bookings DESC;

Result grid: 12 rows fetched

column_name	value	bookings
booking_status	Checked Out	253
booking_status	Cancelled	23
booking_status	No Show	9
guest_nationality	Kenyan	285
payment_method	M-Pesa	74
payment_method	Cash	72
payment_method	Card	70
payment_method	Bank Transfer	69
room_type	Standard	114
room_type	Deluxe	90
room_type	Suite	56
room_type	Penthouse	25

3.6 Dates, the hard one

Dates need the most care, because a wrong guess is invisible. 05/03/2024 is either 5 March or 3 May, and nothing in the text says which. First, sort the check-in dates by shape:

SELECT CASE
           WHEN check_in_date ~ '^\d{4}-\d{2}-\d{2}$' THEN 'YYYY-MM-DD'
           WHEN check_in_date ~ '^\d{2}/\d{2}/\d{4}$' THEN 'NN/NN/YYYY'
           WHEN check_in_date ~ '^\d{2}-\d{2}-\d{2}$' THEN 'NN-NN-YY'
           WHEN check_in_date ~ '^\d{2}-\d{2}-\d{4}$' THEN 'NN-NN-YYYY'
       END AS date_shape,
       COUNT(*) AS bookings,
       MIN(check_in_date) AS example
FROM tembo.staging_bookings
GROUP BY 1
ORDER BY bookings DESC;

Result grid: 4 rows fetched

date_shape	bookings	example
YYYY-MM-DD	242	2023-06-11
NN-NN-YYYY	15	01-12-2024
NN/NN/YYYY	15	05/03/2024
NN-NN-YY	13	05-10-24

Four shapes. My rules for the three odd ones:

• NN/NN/YYYY is day first, as is usual in Kenya.
• NN-NN-YY is day first with a two-digit year.
• NN-NN-YYYY is month first (01-12-2024 for 12 January), unless the first number is above 12, in which case it must be day first.

Each of those rules is a guess. So I use the nights_stayed column as a witness: after parsing, the gap between check-in and check-out must equal the nights on record. If a rule is wrong, the check will show it. A small function keeps the rules in one place:

CREATE OR REPLACE FUNCTION tembo.fix_date(d TEXT) RETURNS DATE AS $$
    SELECT CASE
        WHEN d ~ '^\d{4}-\d{2}-\d{2}$' THEN d::DATE
        WHEN d ~ '^\d{2}/\d{2}/\d{4}$' THEN TO_DATE(d, 'DD/MM/YYYY')
        WHEN d ~ '^\d{2}-\d{2}-\d{2}$' THEN TO_DATE(d, 'DD-MM-YY')
        WHEN d ~ '^\d{2}-\d{2}-\d{4}$' AND SPLIT_PART(d, '-', 1)::INT > 12
            THEN TO_DATE(d, 'DD-MM-YYYY')
        WHEN d ~ '^\d{2}-\d{2}-\d{4}$' THEN TO_DATE(d, 'MM-DD-YYYY')
    END
$$ LANGUAGE sql IMMUTABLE;

Statistics tab: Updated Rows = 0

A trial run on one booking of each shape, before anything changes:

SELECT booking_id,
       check_in_date  AS raw_in,
       check_out_date AS raw_out,
       tembo.fix_date(check_in_date)  AS parsed_in,
       tembo.fix_date(check_out_date) AS parsed_out,
       nights_stayed
FROM tembo.staging_bookings
WHERE booking_id IN ('BK0003', 'BK0006', 'BK0007', 'BK0020', 'BK9005')
ORDER BY booking_id;

Result grid: 5 rows fetched

booking_id	raw_in	raw_out	parsed_in	parsed_out	nights_stayed
BK0003	2024-01-07	2024-01-10	2024-01-07	2024-01-10	3
BK0006	10/01/2024	15/01/2024	2024-01-10	2024-01-15	5
BK0007	01-12-2024	01-17-2024	2024-01-12	2024-01-17	5
BK0020	28-01-24	29-01-24	2024-01-28	2024-01-29	1
BK9005	15-11-2024	17-11-2024	2024-11-15	2024-11-17	2

Each parsed gap matches its nights_stayed. Now apply the function to both date columns:

UPDATE tembo.staging_bookings
SET check_in_date  = tembo.fix_date(check_in_date)::TEXT,
    check_out_date = tembo.fix_date(check_out_date)::TEXT
WHERE check_in_date  !~ '^\d{4}-\d{2}-\d{2}$'
   OR check_out_date !~ '^\d{4}-\d{2}-\d{2}$';

Statistics tab: Updated Rows = 43

Now the time traveller. Two bookings check out before they check in. In both, the gap between the two dates equals the recorded nights (or its absolute value), so the two dates were entered in the wrong boxes. Swap them. In a single UPDATE, PostgreSQL evaluates every right-hand side against the old row, so the swap needs no temporary column.

UPDATE tembo.staging_bookings
SET check_in_date  = check_out_date,
    check_out_date = check_in_date
WHERE check_out_date::DATE < check_in_date::DATE;

Statistics tab: Updated Rows = 2

One booking still carries -3 nights. The dates now say three nights, so the dates win, and the rule is written into SQL: recompute nights wherever they disagree with the calendar.

UPDATE tembo.staging_bookings
SET nights_stayed = (check_out_date::DATE - check_in_date::DATE)::TEXT
WHERE nights_stayed::INT <> (check_out_date::DATE - check_in_date::DATE);

Statistics tab: Updated Rows = 1

The proof: every one of the 285 bookings must agree with the calendar.

SELECT COUNT(*) AS bookings,
       COUNT(*) FILTER (
           WHERE check_out_date::DATE - check_in_date::DATE = nights_stayed::INT
       ) AS dates_agree_with_nights,
       MIN(check_in_date) AS first_check_in,
       MAX(check_in_date) AS last_check_in
FROM tembo.staging_bookings;

Result grid: 1 row fetched

bookings	dates_agree_with_nights	first_check_in	last_check_in
285	285	2023-06-10	2024-12-31

All 285 agree, so both guessed rules survived the test. Without that test, I would be trusting my own reading of an ambiguous string.

3.7 Staff salary

SELECT staff_name, staff_salary, COUNT(*) AS rows_affected
FROM tembo.staging_bookings
GROUP BY staff_name, staff_salary
ORDER BY staff_name, staff_salary;

Result grid: 11 rows fetched

staff_name	staff_salary	rows_affected
Amina Juma	65000	35
Brenda Achieng	42000	36
Fatuma Hassan	48000	36
Joy Otieno	120000	34
Kelvin Omondi	42000	38
Moses Kipchoge	35000	35
Moses Kipchoge	KES ,	1
Peter Ngugi	30000	28
Peter Ngugi	KES ,	6
Tony Karanja	32000	29
Tony Karanja	KES ,	7

Three staff members have some rows with KES , in place of a salary. Each person has one true salary elsewhere in the file, so the value can be recovered. First blank the junk, then copy the known salary from the same person's other rows.

UPDATE tembo.staging_bookings
SET staff_salary = NULL
WHERE staff_salary !~ '^\d+$';

Statistics tab: Updated Rows = 14

UPDATE tembo.staging_bookings s
SET staff_salary = k.salary
FROM (
    SELECT staff_name, MAX(staff_salary) AS salary
    FROM tembo.staging_bookings
    WHERE staff_salary IS NOT NULL
    GROUP BY staff_name
) k
WHERE s.staff_name = k.staff_name
  AND s.staff_salary IS NULL;

Statistics tab: Updated Rows = 14

SELECT staff_name, staff_department, staff_salary, COUNT(*) AS bookings
FROM tembo.staging_bookings
GROUP BY staff_name, staff_department, staff_salary
ORDER BY staff_name;

Result grid: 8 rows fetched

staff_name	staff_department	staff_salary	bookings
Amina Juma	Restaurant	65000	35
Brenda Achieng	Front Desk	42000	36
Fatuma Hassan	Housekeeping	48000	36
Joy Otieno	Management	120000	34
Kelvin Omondi	Front Desk	42000	38
Moses Kipchoge	Restaurant	35000	36
Peter Ngugi	Security	30000	34
Tony Karanja	Housekeeping	32000	36

Eight staff, eight salaries, one row each.

3.8 Money: totals and service prices

SELECT booking_id, room_rate_per_night AS rate, nights_stayed AS nights,
       service_price, QUOTE_LITERAL(total_amount) AS total_as_typed
FROM tembo.staging_bookings
WHERE total_amount !~ '^\d+$'
ORDER BY booking_id
LIMIT 8;

Result grid: 8 rows fetched

booking_id	rate	nights	service_price	total_as_typed
BK0014	8500	4	[NULL]	'KES 34000'
BK0015	15000	3	[NULL]	','
BK0034	8500	4	15000	'KES 49000'
BK0035	15000	2	[NULL]	','
BK0054	8500	4	[NULL]	'KES 34000'
BK0055	15000	4	15000	','
BK0074	5500	2	[NULL]	'KES 11000'
BK0075	8500	4	[NULL]	','

Some totals carry a KES prefix, some use a thousands comma (42,500), and some are a lone comma or empty. The first two only need stripping. The last group has lost the figure, but the figure can be rebuilt, because the file follows a rule: total = rate per night x nights + service price. Check BK0001 in the raw data: 5,500 x 1 + 500 = 6,000, and that is what the row says.

Blank service fields first, so a missing service is NULL and never an empty string. This reports 0 rows here, because COPY had already loaded the blanks as NULL. With an import tool that loads empty strings, it would catch them. Then strip the symbols, then rebuild what is still missing.

UPDATE tembo.staging_bookings
SET service_used  = NULLIF(TRIM(service_used), ''),
    service_price = NULLIF(TRIM(service_price), '')
WHERE service_used = '' OR service_price = '';

Statistics tab: Updated Rows = 0

UPDATE tembo.staging_bookings
SET total_amount = NULLIF(REGEXP_REPLACE(total_amount, '[^0-9]', '', 'g'), '')
WHERE total_amount !~ '^\d+$';

Statistics tab: Updated Rows = 29

UPDATE tembo.staging_bookings
SET total_amount = (room_rate_per_night::INT * nights_stayed::INT
                    + COALESCE(service_price::INT, 0))::TEXT
WHERE total_amount IS NULL;

Statistics tab: Updated Rows = 11

Now the audit. How many of the 285 totals obey the rule?

SELECT COUNT(*) AS bookings,
       COUNT(*) FILTER (
           WHERE total_amount::INT = room_rate_per_night::INT * nights_stayed::INT
                                     + COALESCE(service_price::INT, 0)
       ) AS totals_that_add_up
FROM tembo.staging_bookings;

Result grid: 1 row fetched

bookings	totals_that_add_up
285	283

283 of 285. The two that fail deserve a look, not a silent fix.

SELECT booking_id, service_used, service_price, total_amount,
       room_rate_per_night::INT * nights_stayed::INT
           + COALESCE(service_price::INT, 0) AS rule_says
FROM tembo.staging_bookings
WHERE total_amount::INT <> room_rate_per_night::INT * nights_stayed::INT
                           + COALESCE(service_price::INT, 0);

Result grid: 2 rows fetched

booking_id	service_used	service_price	total_amount	rule_says
BK9007	Breakfast Buffet	1200	25500	26700
BK9004	Laundry	800	25500	26300

In both rows the stored total equals the room charge alone, with the service left out. The service may have been billed separately, or someone forgot to add it. I can't tell from the file, so I leave the stored figures alone and list the two bookings as a question for the hotel. Together they come to 2,000 KES.

3.9 Ratings

SELECT QUOTE_LITERAL(guest_rating) AS rating_as_typed, COUNT(*) AS bookings
FROM tembo.staging_bookings
GROUP BY guest_rating
ORDER BY guest_rating;

Result grid: 10 rows fetched

rating_as_typed	bookings
' 3'	1
' 4'	2
'0'	6
'1'	60
'2'	45
'3'	45
'4'	65
'5'	52
'6'	8
[NULL]	1

The scale is 1 to 5. The file also holds 0, 6, padded values like ' 4', and blanks. A 0 or a 6 is impossible, so it becomes NULL: an honest gap is better than an invented score. First strip the padding and turn blanks into NULL.

UPDATE tembo.staging_bookings
SET guest_rating = NULLIF(TRIM(guest_rating), '')
WHERE guest_rating <> TRIM(guest_rating) OR guest_rating = '';

Statistics tab: Updated Rows = 3

UPDATE tembo.staging_bookings
SET guest_rating = NULL
WHERE guest_rating::INT NOT BETWEEN 1 AND 5;

Statistics tab: Updated Rows = 14

SELECT guest_rating, COUNT(*) AS bookings
FROM tembo.staging_bookings
GROUP BY guest_rating
ORDER BY guest_rating;

Result grid: 6 rows fetched

guest_rating	bookings
1	60
2	45
3	46
4	67
5	52
[NULL]	15

Step 4: Move the clean rows into a table that says no

The staging table is tidy now, but it's still all text, and text will happily accept banana as a date. The real table uses proper types and CHECK constraints. If a bad row ever reaches it, the database itself refuses the row.

DROP TABLE IF EXISTS tembo.clean_bookings CASCADE;

CREATE TABLE tembo.clean_bookings (
    booking_id          VARCHAR(10) PRIMARY KEY,
    guest_name          VARCHAR(100),
    guest_phone         VARCHAR(10),
    guest_city          VARCHAR(60),
    guest_nationality   VARCHAR(30),
    room_no             VARCHAR(5),
    room_type           VARCHAR(20),
    room_rate_per_night NUMERIC(10,2),
    check_in_date       DATE,
    check_out_date      DATE,
    nights_stayed       INTEGER CHECK (nights_stayed > 0),
    staff_name          VARCHAR(100),
    staff_department    VARCHAR(30),
    staff_salary        NUMERIC(10,2),
    payment_method      VARCHAR(20),
    booking_status      VARCHAR(20),
    total_amount        NUMERIC(10,2),
    service_used        VARCHAR(50),
    service_price       NUMERIC(10,2),
    guest_rating        INTEGER CHECK (guest_rating BETWEEN 1 AND 5),
    CHECK (check_out_date > check_in_date)
);

Statistics tab: Updated Rows = 0

INSERT INTO tembo.clean_bookings
SELECT booking_id, guest_name, guest_phone, guest_city, guest_nationality,
       room_no, room_type, room_rate_per_night::NUMERIC,
       check_in_date::DATE, check_out_date::DATE, nights_stayed::INT,
       staff_name, staff_department, staff_salary::NUMERIC,
       payment_method, booking_status, total_amount::NUMERIC,
       service_used, service_price::NUMERIC, guest_rating::INT
FROM tembo.staging_bookings;

Statistics tab: Updated Rows = 285

All 285 rows went through without a single complaint, which is the real test of the cleaning. To see what the constraints protect against, try to insert a rating of 6:

INSERT INTO tembo.clean_bookings
    (booking_id, guest_name, room_type, check_in_date, check_out_date, nights_stayed, guest_rating)
VALUES
    ('BK9999', 'Test Guest', 'Suite', '2025-01-10', '2025-01-12', 2, 6);

Error: SQL Error [23514]: ERROR: new row for relation "clean_bookings" violates check constraint "clean_bookings_guest_rating_check"

Last, a view. Every analysis below groups by month, so the view adds a stay_month column once instead of repeating DATE_TRUNC in every query. I assign each stay to the month of check-in.

CREATE OR REPLACE VIEW tembo.v_clean_bookings AS
SELECT b.*,
       DATE_TRUNC('month', check_in_date)::DATE AS stay_month
FROM tembo.clean_bookings b;

Statistics tab: Updated Rows = 0

SELECT COUNT(*) AS bookings,
       MIN(check_in_date) AS first_check_in,
       MAX(check_in_date) AS last_check_in,
       COUNT(DISTINCT room_no) AS rooms,
       COUNT(*) FILTER (WHERE guest_rating IS NULL) AS unrated
FROM tembo.v_clean_bookings;

Result grid: 1 row fetched

bookings	first_check_in	last_check_in	rooms	unrated
285	2023-06-10	2024-12-31	10	15

The file covers June 2023 to December 2024, ten rooms, and 285 bookings. Before the first business question, one definition matters. Only bookings with status Checked Out earned money. Cancelled and No Show rows still carry a total_amount, but it's a price the hotel never collected. So revenue queries filter on Checked Out, and the cancellation question (6) is the one place the other two statuses take centre stage.

Step 5: Answer the Director's questions

Q1. Where does the money come from?

By month:

SELECT TO_CHAR(stay_month, 'YYYY-MM') AS month,
       COUNT(*)          AS stays,
       SUM(total_amount) AS revenue
FROM tembo.v_clean_bookings
WHERE booking_status = 'Checked Out'
GROUP BY stay_month
ORDER BY stay_month;

Result grid: 19 rows fetched

month	stays	revenue
2023-06	3	78000.00
2023-07	6	182300.00
2023-08	3	110000.00
2023-09	5	152200.00
2023-10	3	92000.00
2023-11	5	142300.00
2023-12	9	335200.00
2024-01	18	630500.00
2024-02	19	608000.00
2024-03	19	614000.00
2024-04	18	704500.00
2024-05	15	437700.00
2024-06	19	476000.00
2024-07	17	528200.00
2024-08	17	402500.00
2024-09	18	509000.00
2024-10	21	583000.00
2024-11	19	576500.00
2024-12	19	590500.00

By room type, with each type's share of the total. SUM(SUM(...)) OVER () is a window function wrapped around an aggregate. It divides each group's revenue by the grand total without a second query.

SELECT room_type,
       COUNT(*)          AS stays,
       SUM(total_amount) AS revenue,
       ROUND(100.0 * SUM(total_amount) / SUM(SUM(total_amount)) OVER (), 1) AS revenue_share_pct
FROM tembo.v_clean_bookings
WHERE booking_status = 'Checked Out'
GROUP BY room_type
ORDER BY revenue DESC;

Result grid: 4 rows fetched

room_type	stays	revenue	revenue_share_pct
Suite	54	2659600.00	34.3
Deluxe	83	2236700.00	28.9
Standard	97	1628500.00	21.0
Penthouse	19	1227600.00	15.8

By payment method, with the same share calculation:

SELECT payment_method,
       COUNT(*)          AS stays,
       SUM(total_amount) AS revenue,
       ROUND(100.0 * SUM(total_amount) / SUM(SUM(total_amount)) OVER (), 1) AS revenue_share_pct
FROM tembo.v_clean_bookings
WHERE booking_status = 'Checked Out'
GROUP BY payment_method
ORDER BY revenue DESC;

Result grid: 4 rows fetched

payment_method	stays	revenue	revenue_share_pct
Cash	72	2309500.00	29.8
M-Pesa	74	2108700.00	27.2
Card	70	2043000.00	26.4
Bank Transfer	37	1291200.00	16.7

Suites earn the most, 34.3% of revenue from only 54 stays, while Standard rooms take the most bookings and bring in 21%. The Penthouse has the fewest stays (19) and still takes a 15.8% share. On payment, cash leads at 29.8%, with M-Pesa and cards close behind. Bank transfers are the rarest method (37 stays), but the guests who use them spend more per stay than the rest.

Q2. Which rooms are booked most, and for how long?

SELECT room_type,
       COUNT(*)                    AS stays,
       SUM(nights_stayed)          AS room_nights,
       ROUND(AVG(nights_stayed), 2) AS avg_nights
FROM tembo.v_clean_bookings
WHERE booking_status = 'Checked Out'
GROUP BY room_type
ORDER BY stays DESC;

Result grid: 4 rows fetched

room_type	stays	room_nights	avg_nights
Standard	97	279	2.88
Deluxe	83	252	3.04
Suite	54	172	3.19
Penthouse	19	48	2.53

Standard is the most booked, with 97 stays. Longer stays go the other way: Suite guests stay 3.19 nights on average against 2.88 for Standard. The Penthouse has the shortest stays (2.53 nights), which fits a room people book for an occasion.

Q3. Who are our guests, and are they happy?

The top 10 cities:

SELECT guest_city,
       COUNT(*)          AS stays,
       SUM(total_amount) AS revenue
FROM tembo.v_clean_bookings
WHERE booking_status = 'Checked Out'
GROUP BY guest_city
ORDER BY stays DESC, revenue DESC
LIMIT 10;

Result grid: 10 rows fetched

guest_city	stays	revenue
Nairobi	111	3248100.00
Kisumu	29	737100.00
Eldoret	28	983700.00
Mombasa	15	303800.00
Meru	14	574300.00
Nakuru	14	555000.00
Thika	14	373200.00
Unknown	10	474000.00
Nyeri	9	336500.00
Machakos	9	166700.00

Nairobi supplies 111 of the 253 stays, about 44%, and Eldoret earns more per stay than Kisumu. The file only names nine real cities, so the tenth row is Unknown, which is the honest name for the blank cells. It is worth telling the front desk to stop leaving that field empty.

Average rating per room type:

SELECT room_type,
       ROUND(AVG(guest_rating), 2) AS avg_rating,
       COUNT(guest_rating)         AS ratings_counted
FROM tembo.v_clean_bookings
WHERE booking_status = 'Checked Out'
GROUP BY room_type
ORDER BY avg_rating DESC;

Result grid: 4 rows fetched

room_type	avg_rating	ratings_counted
Penthouse	3.17	18
Standard	3.07	91
Suite	2.96	47
Deluxe	2.94	82

I count ratings only from guests who actually stayed. Nobody can rate a night they never spent, yet the raw file holds ratings on cancelled bookings. COUNT(guest_rating) skips NULLs, so the cleaned blanks don't drag the average down.

Every room type sits between 2.94 and 3.17 out of 5. That is a mediocre score, and an average can hide the shape behind it. One more query shows the spread:

SELECT guest_rating,
       COUNT(*) AS stays,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share_pct
FROM tembo.v_clean_bookings
WHERE booking_status = 'Checked Out'
  AND guest_rating IS NOT NULL
GROUP BY guest_rating
ORDER BY guest_rating;

Result grid: 5 rows fetched

guest_rating	stays	share_pct
1	54	22.7
2	40	16.8
3	40	16.8
4	58	24.4
5	46	19.3

Guests split. Of the 238 ratings, 39.5% are a 1 or a 2 and 43.7% are a 4 or a 5, while only 16.8% picked the middle. One guest in five gave the lowest score. A hotel of quietly satisfied guests would score 3 out of 5 as well, so the average alone would never have told us this.

Q4. How is the staff performing?

A CTE gathers each person's numbers first, then RANK() orders them. COUNT(*) FILTER (WHERE ...) counts only the rows that meet a condition, which replaces the older SUM(CASE WHEN ...) trick.

WITH staff_stats AS (
    SELECT staff_name,
           staff_department,
           COUNT(*) AS bookings_handled,
           COUNT(*) FILTER (WHERE booking_status = 'Checked Out') AS completed_stays,
           COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS lost_bookings,
           SUM(total_amount) FILTER (WHERE booking_status = 'Checked Out') AS revenue
    FROM tembo.v_clean_bookings
    GROUP BY staff_name, staff_department
)
SELECT RANK() OVER (ORDER BY bookings_handled DESC) AS rank_by_bookings,
       staff_name,
       staff_department,
       bookings_handled,
       completed_stays,
       lost_bookings,
       revenue
FROM staff_stats
ORDER BY rank_by_bookings, revenue DESC;

Result grid: 8 rows fetched

rank_by_bookings	staff_name	staff_department	bookings_handled	completed_stays	lost_bookings	revenue
1	Kelvin Omondi	Front Desk	38	38	0	1073700.00
2	Brenda Achieng	Front Desk	36	36	0	1283800.00
2	Moses Kipchoge	Restaurant	36	36	0	1035000.00
2	Tony Karanja	Housekeeping	36	36	0	972700.00
2	Fatuma Hassan	Housekeeping	36	19	17	601200.00
6	Amina Juma	Restaurant	35	35	0	1000200.00
7	Peter Ngugi	Security	34	34	0	1070300.00
7	Joy Otieno	Management	34	19	15	715500.00

Kelvin Omondi handled the most bookings (38), but Brenda Achieng brought in the most revenue (1,283,800 KES) from fewer bookings. Volume and value are different rankings, and a bonus scheme should know which one it rewards.

The last two columns hold the surprise. Every one of the 32 cancellations and no-shows belongs to two people, Fatuma Hassan and Joy Otieno. The other six staff members have none. That's a pattern in the data, not a verdict on anyone, and Q6 shows that the staff name is only half of it. It's the first thing I would raise with the Director.

Which department earns the most?

SELECT staff_department,
       COUNT(*)          AS stays,
       SUM(total_amount) AS revenue,
       ROUND(100.0 * SUM(total_amount) / SUM(SUM(total_amount)) OVER (), 1) AS revenue_share_pct
FROM tembo.v_clean_bookings
WHERE booking_status = 'Checked Out'
GROUP BY staff_department
ORDER BY revenue DESC;

Result grid: 5 rows fetched

staff_department	stays	revenue	revenue_share_pct
Front Desk	74	2357500.00	30.4
Restaurant	71	2035200.00	26.3
Housekeeping	55	1573900.00	20.3
Security	34	1070300.00	13.8
Management	19	715500.00	9.2

Front Desk leads with 30.4%, which surprises nobody. Restaurant is second, and Security (a department with no obvious booking role) still records 13.8% of revenue. That's another oddity worth a question: the data suggests bookings are entered by people outside reception.

Q5. What is the trend?

The growth query compares each month with the one before. LAG() reaches back one row in the window, so no self-join is needed. A second window function, RANK(), orders the months from best to worst.

WITH monthly_revenue AS (
    SELECT stay_month,
           COUNT(*)          AS stays,
           SUM(total_amount) AS revenue
    FROM tembo.v_clean_bookings
    WHERE booking_status = 'Checked Out'
    GROUP BY stay_month
),
monthly_growth AS (
    SELECT stay_month, stays, revenue,
           LAG(revenue) OVER (ORDER BY stay_month) AS prev_revenue
    FROM monthly_revenue
)
SELECT TO_CHAR(stay_month, 'YYYY-MM') AS month,
       stays,
       revenue,
       prev_revenue,
       ROUND((revenue - prev_revenue) * 100.0 / prev_revenue, 2) AS growth_pct,
       RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM monthly_growth
ORDER BY stay_month;

Result grid: 19 rows fetched

month	stays	revenue	prev_revenue	growth_pct	revenue_rank
2023-06	3	78000.00	[NULL]	[NULL]	19
2023-07	6	182300.00	78000.00	133.72	14
2023-08	3	110000.00	182300.00	-39.66	17
2023-09	5	152200.00	110000.00	38.36	15
2023-10	3	92000.00	152200.00	-39.55	18
2023-11	5	142300.00	92000.00	54.67	16
2023-12	9	335200.00	142300.00	135.56	13
2024-01	18	630500.00	335200.00	88.10	2
2024-02	19	608000.00	630500.00	-3.57	4
2024-03	19	614000.00	608000.00	0.99	3
2024-04	18	704500.00	614000.00	14.74	1
2024-05	15	437700.00	704500.00	-37.87	11
2024-06	19	476000.00	437700.00	8.75	10
2024-07	17	528200.00	476000.00	10.97	8
2024-08	17	402500.00	528200.00	-23.80	12
2024-09	18	509000.00	402500.00	26.46	9
2024-10	21	583000.00	509000.00	14.54	6
2024-11	19	576500.00	583000.00	-1.11	7
2024-12	19	590500.00	576500.00	2.43	5

The first row has no previous month, so its prev_revenue and growth_pct are NULL. That's correct, and it's why the dividing expression never fails.

The 2023 months look wild (an 88% jump into January 2024, two months down by almost 40%), but the cause is thin data. Only 34 stays fall in the seven months of 2023 against roughly 18 in a typical month of 2024. Growth between such small numbers mostly measures noise. For busiest and quietest, I compare 2024 only, the one complete year:

(SELECT 'Busiest' AS label,
        TO_CHAR(stay_month, 'YYYY-MM') AS month,
        COUNT(*) AS stays,
        SUM(total_amount) AS revenue
 FROM tembo.v_clean_bookings
 WHERE booking_status = 'Checked Out' AND stay_month >= '2024-01-01'
 GROUP BY stay_month
 ORDER BY revenue DESC
 LIMIT 3)
UNION ALL
(SELECT 'Quietest',
        TO_CHAR(stay_month, 'YYYY-MM'),
        COUNT(*),
        SUM(total_amount)
 FROM tembo.v_clean_bookings
 WHERE booking_status = 'Checked Out' AND stay_month >= '2024-01-01'
 GROUP BY stay_month
 ORDER BY SUM(total_amount)
 LIMIT 3);

Result grid: 6 rows fetched

label	month	stays	revenue
Busiest	2024-04	18	704500.00
Busiest	2024-01	18	630500.00
Busiest	2024-03	19	614000.00
Quietest	2024-08	17	402500.00
Quietest	2024-05	15	437700.00
Quietest	2024-06	19	476000.00

April 2024 was the best month at 704,500 KES. August was the weakest by revenue (402,500 KES) even though it had 17 stays, and May had the fewest stays (15). Stays and revenue don't move together: June had 19 stays and earned 476,000 KES, while January earned 630,500 KES from 18. The mix of rooms matters more than the headcount, so a hotel needs both rankings, one for rostering staff and one for pricing.

Q6. What do cancellations cost?

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_pct,
       SUM(total_amount) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS lost_revenue
FROM tembo.v_clean_bookings
GROUP BY room_type
ORDER BY cancellation_rate_pct DESC;

Result grid: 4 rows fetched

room_type	total_bookings	lost_bookings	cancellation_rate_pct	lost_revenue
Penthouse	25	6	24.00	565000.00
Standard	114	17	14.91	320300.00
Deluxe	90	7	7.78	170000.00
Suite	56	2	3.57	120000.00

The Penthouse loses almost one booking in four (24%), and that one room type accounts for 565,000 KES of lost revenue. Suites lose the fewest, 3.57%. Standard rooms are cancelled at 14.91%, which costs less per booking but happens far more often.

The overall bill, split by type of loss:

SELECT booking_status,
       COUNT(*)          AS bookings,
       SUM(total_amount) AS value,
       ROUND(100.0 * SUM(total_amount) / SUM(SUM(total_amount)) OVER (), 1) AS share_of_booked_value_pct
FROM tembo.v_clean_bookings
GROUP BY booking_status
ORDER BY value DESC;

Result grid: 3 rows fetched

booking_status	bookings	value	share_of_booked_value_pct
Checked Out	253	7752400.00	86.8
Cancelled	23	910500.00	10.2
No Show	9	264800.00	3.0

Cancellations and no-shows together removed 1,175,300 KES, about 13.2% of everything booked. Cancellations cost far more than no-shows (910,500 KES against 264,800 KES).

One more cut, because the staff table in Q4 hinted at a pattern. Does the payment method say anything about who cancels?

SELECT payment_method,
       COUNT(*) AS 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(*), 1) AS lost_rate_pct,
       COUNT(DISTINCT staff_name) AS staff_handling
FROM tembo.v_clean_bookings
GROUP BY payment_method
ORDER BY lost_rate_pct DESC;

Result grid: 4 rows fetched

payment_method	bookings	lost_bookings	lost_rate_pct	staff_handling
Bank Transfer	69	32	46.4	2
Card	70	0	0.0	2
Cash	72	0	0.0	3
M-Pesa	74	0	0.0	3

This is the most useful result in the project. Every one of the 32 lost bookings was paid by bank transfer, so 46% of bank-transfer bookings never became revenue, and cash, card and M-Pesa lost none. The last column ties it to Q4: only two staff members, Fatuma Hassan and Joy Otieno, ever handle a bank-transfer booking. The data can't separate the two causes. It may be that transfers are reservations held without payment and left to lapse, or that these two colleagues log lapsed bookings and others don't. Either way it points at the fix: ask for a deposit before a bank-transfer booking holds a room, and check how cancellations get recorded.

What the Director gets

Put together, the answers fit in a short paragraph. Suites earn the most in total, the Penthouse earns the most per stay (about 64,600 KES), Standard rooms fill most often, and the Penthouse also loses the most bookings to cancellation. Revenue held steady at roughly 400,000 to 700,000 KES a month through 2024, with April the peak. Front Desk earns the biggest share and Brenda Achieng leads on revenue. Guests are polarised, with four in ten giving a 1 or a 2 and four in ten giving a 4 or a 5. About 13% of booked value never arrived, and all of it sits in bank-transfer bookings.

And there are four questions for the hotel: why only two colleagues handle bank transfers, why Security and Housekeeping staff enter bookings at all, why blank cities and phone numbers slip through, and what happened to the service charge on two bookings.

Pitfalls I hit, so you can skip them

• A two-digit year and a four-digit format don't mix. TO_DATE('28-01-24', 'DD-MM-YYYY') returns a date in the year 24 AD, and gives no error. Match the format to the text: DD-MM-YY.
• Ambiguous dates need a witness. Guessing MM-DD against DD-MM is only safe when another column, here nights_stayed, can confirm it.
• An update with no WHERE clause hides its own effect. A guarded update reports the true number of rows changed, which becomes a free audit trail.
• Clean in staging, constrain in the final table. The CHECK constraints turned "I think the data is clean" into "the database refused anything that wasn't".
• Decide what "revenue" means before writing the first SUM. It changed every answer above.

Try it yourself

Load the file, run the audit query, and see if your count of damaged rows matches mine. Then pick a question the Director didn't ask, such as which room number is busiest or whether guests who pay with M-Pesa rate higher, and answer it with the view. The brief also asks for Power BI visuals. The views above are ready to connect, so that is the natural next step.

If you spot a cleaning rule I got wrong, or a better way to write any query here, tell me in the comments. I'd like to know.