UC0007: View Resource Usage Analytics

Initiating actor: Faculty Administrator

Other actors: None

A faculty administrator wants to analyze how resources are being utilized across the faculty. The system provides analytical reports including reservation counts per resource, most active users, reservation status distribution, busiest days of the week, and resource type utilization rates. These reports support data-driven decisions about resource allocation and scheduling policies.

Scenario

1. The administrator (Marko Dimitrovski, user_id = 2) opens the usage analytics dashboard. The system displays a summary overview with total counts.

   SELECT
       COUNT(*) AS total_reservations,
       COUNT(*) FILTER (WHERE status = 'approved') AS approved,
       COUNT(*) FILTER (WHERE status = 'pending') AS pending,
       COUNT(*) FILTER (WHERE status = 'rejected') AS rejected,
       COUNT(*) FILTER (WHERE status = 'completed') AS completed,
       COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
       COUNT(DISTINCT user_id) AS unique_users,
       COUNT(DISTINCT resource_id) AS unique_resources
   FROM project.reservations;
   

2. The administrator views the Reservations per Resource report. The system shows resources ranked by total number of reservations.

   SELECT r.name AS resource_name,
          rt.type_name AS resource_type,
          COUNT(res.reservation_id) AS total_reservations,
          COUNT(res.reservation_id) FILTER (WHERE res.status = 'approved') AS approved,
          COUNT(res.reservation_id) FILTER (WHERE res.status = 'completed') AS completed,
          COUNT(res.reservation_id) FILTER (WHERE res.status = 'rejected') AS rejected
   FROM project.resources r
   JOIN project.resource_types rt ON r.type_id = rt.type_id
   LEFT JOIN project.reservations res ON r.resource_id = res.resource_id
   GROUP BY r.resource_id, r.name, rt.type_name
   ORDER BY total_reservations DESC;
   

3. The administrator views the Most Active Users report. The system shows users ranked by number of reservations made.

   SELECT u.first_name || ' ' || u.last_name AS user_name,
          ut.type_name AS user_role,
          COUNT(res.reservation_id) AS total_reservations,
          COUNT(res.reservation_id) FILTER (WHERE res.status IN ('approved', 'completed')) AS successful,
          COUNT(res.reservation_id) FILTER (WHERE res.status = 'rejected') AS rejected
   FROM project.users u
   JOIN project.user_types ut ON u.type_id = ut.type_id
   LEFT JOIN project.reservations res ON u.user_id = res.user_id
   GROUP BY u.user_id, u.first_name, u.last_name, ut.type_name
   HAVING COUNT(res.reservation_id) > 0
   ORDER BY total_reservations DESC;
   

4. The administrator views the Reservation Status Distribution report. The system shows the breakdown of all reservations by status with percentages.

   SELECT status,
          COUNT(*) AS count,
          ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS percentage
   FROM project.reservations
   GROUP BY status
   ORDER BY count DESC;
   

5. The administrator views the Busiest Days of the Week report. The system shows how many approved or completed reservations fall on each weekday for physical resources.

   SELECT
       TO_CHAR(res.start_time, 'Day') AS day_of_week,
       EXTRACT(ISODOW FROM res.start_time) AS day_number,
       COUNT(*) AS reservation_count
   FROM project.reservations res
   JOIN project.resources r ON res.resource_id = r.resource_id
   JOIN project.resource_types rt ON r.type_id = rt.type_id
   WHERE rt.is_physical = TRUE
     AND res.status IN ('approved', 'completed')
   GROUP BY TO_CHAR(res.start_time, 'Day'), EXTRACT(ISODOW FROM res.start_time)
   ORDER BY day_number;
   

6. The administrator views the Resource Type Utilization report. The system shows usage per resource category with averages.

   SELECT rt.type_name,
          CASE WHEN rt.is_physical THEN 'Physical' ELSE 'Digital' END AS category,
          COUNT(DISTINCT r.resource_id) AS total_resources,
          COUNT(res.reservation_id) AS total_reservations,
          ROUND(COUNT(res.reservation_id)::NUMERIC /
                NULLIF(COUNT(DISTINCT r.resource_id), 0), 1) AS avg_reservations_per_resource
   FROM project.resource_types rt
   LEFT JOIN project.resources r ON rt.type_id = r.type_id
   LEFT JOIN project.reservations res ON r.resource_id = res.resource_id
   GROUP BY rt.type_id, rt.type_name, rt.is_physical
   ORDER BY total_reservations DESC;
   

7. The administrator views the Monthly Reservation Trends report. The system shows how many reservations were created each month and their outcomes.

   SELECT
       TO_CHAR(DATE_TRUNC('month', created_at), 'YYYY-MM') AS month,
       COUNT(*) AS reservations_created,
       COUNT(*) FILTER (WHERE status IN ('approved', 'completed')) AS successful,
       COUNT(*) FILTER (WHERE status = 'rejected') AS rejected,
       COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
   FROM project.reservations
   GROUP BY DATE_TRUNC('month', created_at)
   ORDER BY month;