Topic 2: A typical day in the life of a data analyst

Please watch the below optional video to look at a more advanced sample SQL syntax and Power BI Dashboard

Sample advanced Power BI Dashboard

Sample advanced SQL query

// Below is an example of a more complex SQL code.
//Upon completing the SQL for Data Analysis Course, students will be able to create similar code.

WITH booked_rooms AS (
  SELECT
    bookings.room_id,
    COUNT(*) AS num_bookings,
    MAX(bookings.check_out_date) AS latest_check_out
  FROM
    bookings
  WHERE
    bookings.check_out_date >= CURRENT_DATE()
  GROUP BY
    bookings.room_id
)
SELECT
  bookings.booking_id,
  bookings.booking_date,
  customers.customer_name,
  rooms.room_number,
  rooms.room_type,
  bookings.check_in_date,
  bookings.check_out_date,
  CASE
    WHEN bookings.check_out_date < CURRENT_DATE() THEN 'Completed'
    WHEN bookings.check_in_date > CURRENT_DATE() THEN 'Upcoming'
    ELSE 'Ongoing'
  END AS booking_status,
  CASE
    WHEN booked_rooms.num_bookings > 1 THEN 'Fully Booked'
    ELSE 'Available'
  END AS room_availability
FROM
  bookings
JOIN
  customers ON bookings.customer_id = customers.customer_id
JOIN
  rooms ON bookings.room_id = rooms.room_id
LEFT JOIN
  booked_rooms ON bookings.room_id = booked_rooms.room_id
WHERE
  bookings.booking_date >= '2023-01-01'
  AND bookings.booking_date <= '2023-12-31'
ORDER BY
  bookings.booking_date ASC;