Changes between Initial Version and Version 1 of ComplexReports

Timestamp:

04/05/26 19:32:06 (6 days ago)

Author:

221511

Comment:

--

ComplexReports

@@ +1 @@
+= Advanced Reports =
+
+== 1. Resource Utilization and Demand Analysis ==
+
+Quarterly report showing each resource's utilization rate, peak usage hours, busiest day, approval rate, and demand ranking. Helps administrators identify underutilized resources that could be repurposed and overdemanded resources that may need capacity expansion or alternatives.
+
+=== Solution SQL ===
+
+{{{
+SET search_path TO project;
+
+WITH quarter_bounds AS (
+    SELECT DATE '2026-01-01' AS q_start, DATE '2026-03-31' AS q_end
+),
+quarter_days AS (
+    SELECT d::DATE AS day, EXTRACT(ISODOW FROM d)::INT AS dow
+    FROM quarter_bounds qb, generate_series(qb.q_start, qb.q_end, '1 day'::INTERVAL) AS d
+),
+resource_availability AS (
+    SELECT
+        r.resource_id,
+        SUM(CASE
+            WHEN qd.dow <= 5 OR r.available_weekends
+            THEN EXTRACT(EPOCH FROM (r.available_to - r.available_from)) / 3600.0
+            ELSE 0
+        END) AS total_available_hours
+    FROM resources r
+    CROSS JOIN quarter_days qd
+    GROUP BY r.resource_id
+),
+reservation_stats AS (
+    SELECT
+        rv.resource_id,
+        COUNT(*) AS total_reservations,
+        COUNT(*) FILTER (WHERE rv.status IN ('approved', 'completed')) AS approved_count,
+        COUNT(*) FILTER (WHERE rv.status = 'rejected') AS rejected_count,
+        COUNT(*) FILTER (WHERE rv.status = 'cancelled') AS cancelled_count,
+        ROUND(SUM(EXTRACT(EPOCH FROM (rv.end_time - rv.start_time)) / 3600.0)
+              FILTER (WHERE rv.status IN ('approved', 'completed')), 1) AS reserved_hours
+    FROM reservations rv, quarter_bounds qb
+    WHERE rv.start_time >= qb.q_start
+      AND rv.start_time < qb.q_end + INTERVAL '1 day'
+    GROUP BY rv.resource_id
+),
+popular_day AS (
+    SELECT DISTINCT ON (rv.resource_id)
+        rv.resource_id,
+        TRIM(TO_CHAR(rv.start_time, 'Day')) AS busiest_day
+    FROM reservations rv, quarter_bounds qb
+    WHERE rv.start_time >= qb.q_start
+      AND rv.start_time < qb.q_end + INTERVAL '1 day'
+      AND rv.status IN ('approved', 'completed')
+    GROUP BY rv.resource_id, TRIM(TO_CHAR(rv.start_time, 'Day'))
+    ORDER BY rv.resource_id, COUNT(*) DESC
+),
+peak_hour AS (
+    SELECT DISTINCT ON (rv.resource_id)
+        rv.resource_id,
+        EXTRACT(HOUR FROM rv.start_time)::INT AS peak_hour
+    FROM reservations rv, quarter_bounds qb
+    WHERE rv.start_time >= qb.q_start
+      AND rv.start_time < qb.q_end + INTERVAL '1 day'
+      AND rv.status IN ('approved', 'completed')
+    GROUP BY rv.resource_id, EXTRACT(HOUR FROM rv.start_time)
+    ORDER BY rv.resource_id, COUNT(*) DESC
+)
+SELECT
+    r.name AS resource_name,
+    rt.type_name AS resource_type,
+    COALESCE(l.building || ' ' || l.room, 'Digital') AS location,
+    COALESCE(rs.total_reservations, 0) AS total_reservations,
+    COALESCE(rs.approved_count, 0) AS approved,
+    COALESCE(rs.rejected_count, 0) AS rejected,
+    COALESCE(rs.cancelled_count, 0) AS cancelled,
+    COALESCE(ROUND(rs.approved_count::NUMERIC / NULLIF(rs.total_reservations, 0) * 100, 1), 0)
+        AS approval_rate_pct,
+    COALESCE(rs.reserved_hours, 0) AS reserved_hours,
+    ROUND(ra.total_available_hours, 0) AS available_hours,
+    COALESCE(ROUND(rs.reserved_hours / NULLIF(ra.total_available_hours, 0) * 100, 2), 0)
+        AS utilization_pct,
+    COALESCE(pd.busiest_day, '-') AS busiest_day,
+    COALESCE(ph.peak_hour || ':00', '-') AS peak_hour,
+    RANK() OVER (
+        ORDER BY COALESCE(rs.reserved_hours, 0)
+                 / NULLIF(ra.total_available_hours, 0) DESC NULLS LAST
+    ) AS demand_rank
+FROM resources r
+JOIN resource_types rt ON r.type_id = rt.type_id
+LEFT JOIN locations l ON r.location_id = l.location_id
+JOIN resource_availability ra ON r.resource_id = ra.resource_id
+LEFT JOIN reservation_stats rs ON r.resource_id = rs.resource_id
+LEFT JOIN popular_day pd ON r.resource_id = pd.resource_id
+LEFT JOIN peak_hour ph ON r.resource_id = ph.resource_id
+ORDER BY utilization_pct DESC;
+}}}
+
+=== Solution Relational Algebra ===
+
+{{{
+QuarterDays <- π day, dow (
+  σ day >= '2026-01-01' ∧ day <= '2026-03-31' (CalendarDays)
+)
+
+ResourceAvailability <-
+  γ resource_id; total_available_hours := SUM(daily_hours) (
+    π resource_id, (CASE dow ≤ 5 ∨ available_weekends THEN hours ELSE 0) → daily_hours (
+      resources × QuarterDays
+    )
+  )
+
+ReservationStats <-
+  γ resource_id;
+    total_reservations := COUNT(*),
+    approved_count := COUNT(σ status ∈ {'approved','completed'}),
+    rejected_count := COUNT(σ status = 'rejected'),
+    cancelled_count := COUNT(σ status = 'cancelled'),
+    reserved_hours := SUM(duration_hours WHERE status ∈ {'approved','completed'}) (
+      σ start_time >= '2026-01-01' ∧ start_time < '2026-04-01' (
+        π resource_id, status, (end_time − start_time)/3600 → duration_hours (reservations)
+      )
+  )
+
+PopularDay <-
+  γ resource_id; busiest_day := DAY_NAME(start_time) HAVING MAX(COUNT(*)) (
+    σ status ∈ {'approved','completed'} ∧ start_time ∈ Quarter (reservations)
+  )
+
+PeakHour <-
+  γ resource_id; peak_hour := HOUR(start_time) HAVING MAX(COUNT(*)) (
+    σ status ∈ {'approved','completed'} ∧ start_time ∈ Quarter (reservations)
+  )
+
+Result <-
+  π resource_name, resource_type, location, total_reservations, approved_count,
+    rejected_count, cancelled_count, approval_rate_pct, reserved_hours,
+    available_hours, utilization_pct, busiest_day, peak_hour, demand_rank (
+      (
+        (
+          (
+            (resources ⨝ resource_types)
+            ⟕ locations
+          )
+          ⨝ ResourceAvailability
+        )
+        ⟕ ReservationStats
+      )
+      ⟕ PopularDay
+      ⟕ PeakHour
+  )
+}}}
+
+== 2. Monthly Reservation Trends with Cumulative Statistics ==
+
+Monthly time-series report showing reservation counts, approval rates, month-over-month growth, cumulative totals, and the top 3 most demanded resources per month. Useful for identifying seasonal patterns and forecasting future demand at the semester or academic year level.
+
+=== Solution SQL ===
+
+{{{
+SET search_path TO project;
+
+WITH monthly_overview AS (
+    SELECT
+        DATE_TRUNC('month', rv.start_time) AS month,
+        COUNT(*) AS total_reservations,
+        COUNT(*) FILTER (WHERE rv.status IN ('approved', 'completed')) AS approved,
+        COUNT(*) FILTER (WHERE rv.status = 'rejected') AS rejected,
+        COUNT(*) FILTER (WHERE rv.status = 'cancelled') AS cancelled,
+        COUNT(*) FILTER (WHERE rv.status = 'pending') AS pending,
+        COUNT(DISTINCT rv.user_id) AS unique_users,
+        COUNT(DISTINCT rv.resource_id) AS unique_resources,
+        ROUND(AVG(EXTRACT(EPOCH FROM (rv.end_time - rv.start_time)) / 3600.0), 1)
+            AS avg_duration_hours,
+        ROUND(SUM(EXTRACT(EPOCH FROM (rv.end_time - rv.start_time)) / 3600.0)
+              FILTER (WHERE rv.status IN ('approved', 'completed')), 1)
+            AS total_approved_hours
+    FROM reservations rv
+    GROUP BY DATE_TRUNC('month', rv.start_time)
+),
+monthly_trends AS (
+    SELECT
+        TO_CHAR(month, 'YYYY-MM') AS month_label,
+        month,
+        total_reservations,
+        approved,
+        rejected,
+        cancelled,
+        pending,
+        unique_users,
+        unique_resources,
+        avg_duration_hours,
+        total_approved_hours,
+        SUM(total_reservations) OVER (ORDER BY month) AS cumulative_total,
+        ROUND(approved::NUMERIC / NULLIF(total_reservations, 0) * 100, 1) AS approval_rate,
+        CASE
+            WHEN LAG(total_reservations) OVER (ORDER BY month) IS NULL THEN NULL
+            ELSE ROUND(
+                (total_reservations - LAG(total_reservations) OVER (ORDER BY month))::NUMERIC
+                / LAG(total_reservations) OVER (ORDER BY month) * 100, 1
+            )
+        END AS mom_change_pct
+    FROM monthly_overview
+),
+resource_demand_ranked AS (
+    SELECT
+        TO_CHAR(DATE_TRUNC('month', rv.start_time), 'YYYY-MM') AS month_label,
+        r.name AS resource_name,
+        COUNT(*) AS demand_count,
+        ROW_NUMBER() OVER (
+            PARTITION BY DATE_TRUNC('month', rv.start_time)
+            ORDER BY COUNT(*) DESC
+        ) AS rank
+    FROM reservations rv
+    JOIN resources r ON rv.resource_id = r.resource_id
+    GROUP BY DATE_TRUNC('month', rv.start_time), r.name
+),
+top_resources AS (
+    SELECT
+        month_label,
+        STRING_AGG(resource_name || ' (' || demand_count || ')',
+                   ', ' ORDER BY rank) AS top_3_resources
+    FROM resource_demand_ranked
+    WHERE rank <= 3
+    GROUP BY month_label
+)
+SELECT
+    mt.month_label,
+    mt.total_reservations,
+    mt.approved,
+    mt.rejected,
+    mt.cancelled,
+    mt.pending,
+    mt.approval_rate AS approval_rate_pct,
+    COALESCE(mt.mom_change_pct || '%', 'N/A') AS month_over_month,
+    mt.cumulative_total,
+    mt.unique_users,
+    mt.unique_resources,
+    mt.avg_duration_hours,
+    mt.total_approved_hours,
+    COALESCE(tr.top_3_resources, '-') AS top_demanded_resources
+FROM monthly_trends mt
+LEFT JOIN top_resources tr ON mt.month_label = tr.month_label
+ORDER BY mt.month_label;
+}}}
+
+=== Solution Relational Algebra ===
+
+{{{
+MonthlyOverview <-
+  γ month := TRUNC_MONTH(start_time);
+    total_reservations := COUNT(*),
+    approved := COUNT(σ status ∈ {'approved','completed'}),
+    rejected := COUNT(σ status = 'rejected'),
+    cancelled := COUNT(σ status = 'cancelled'),
+    pending := COUNT(σ status = 'pending'),
+    unique_users := COUNT_DISTINCT(user_id),
+    unique_resources := COUNT_DISTINCT(resource_id),
+    avg_duration_hours := AVG(duration),
+    total_approved_hours := SUM(duration WHERE status ∈ {'approved','completed'}) (
+      π start_time, status, user_id, resource_id,
+        (end_time − start_time)/3600 → duration (reservations)
+  )
+
+MonthlyTrends <-
+  π month_label, total_reservations, approved, rejected, cancelled, pending,
+    approval_rate, cumulative_total, mom_change_pct (
+      ω ORDER BY month;
+        cumulative_total := SUM(total_reservations),
+        mom_change_pct := (total_reservations − LAG(total_reservations))
+                          / LAG(total_reservations) * 100 (
+          MonthlyOverview
+      )
+  )
+
+ResourceDemandRanked <-
+  ω PARTITION BY month ORDER BY demand_count DESC;
+    rank := ROW_NUMBER() (
+      γ month := TRUNC_MONTH(start_time), resource_name := r.name;
+        demand_count := COUNT(*) (
+          reservations rv ⨝ (rv.resource_id = r.resource_id) resources r
+      )
+  )
+
+TopResources <-
+  γ month_label; top_3 := CONCAT(resource_name, demand_count) (
+    σ rank ≤ 3 (ResourceDemandRanked)
+  )
+
+Result <-
+  π month_label, total_reservations, approved, rejected, cancelled, pending,
+    approval_rate, mom_change_pct, cumulative_total, top_3 (
+      MonthlyTrends ⟕ (month_label) TopResources
+  )
+}}}
+
+== 3. User Activity and Behavior Analysis ==
+
+Per-user report showing total reservations, approval and cancellation rates, most-used resource, total hours consumed, and activity ranking. Helps identify the most active users, detect problematic patterns (high cancellation rates), and understand how different user types (students vs. teaching staff) use the system.
+
+=== Solution SQL ===
+
+{{{
+SET search_path TO project;
+
+WITH user_stats AS (
+    SELECT
+        rv.user_id,
+        COUNT(*) AS total_reservations,
+        COUNT(*) FILTER (WHERE rv.status IN ('approved', 'completed')) AS approved,
+        COUNT(*) FILTER (WHERE rv.status = 'rejected') AS rejected,
+        COUNT(*) FILTER (WHERE rv.status = 'cancelled') AS cancelled,
+        ROUND(COUNT(*) FILTER (WHERE rv.status = 'cancelled')::NUMERIC
+              / NULLIF(COUNT(*), 0) * 100, 1) AS cancellation_rate,
+        ROUND(AVG(EXTRACT(EPOCH FROM (rv.end_time - rv.start_time)) / 3600.0), 1)
+            AS avg_duration_hours,
+        ROUND(SUM(EXTRACT(EPOCH FROM (rv.end_time - rv.start_time)) / 3600.0)
+              FILTER (WHERE rv.status IN ('approved', 'completed')), 1) AS total_hours_used,
+        COUNT(DISTINCT rv.resource_id) AS distinct_resources_used
+    FROM reservations rv
+    GROUP BY rv.user_id
+),
+favorite_resource AS (
+    SELECT DISTINCT ON (rv.user_id)
+        rv.user_id,
+        r.name AS favorite_resource,
+        COUNT(*) AS use_count
+    FROM reservations rv
+    JOIN resources r ON rv.resource_id = r.resource_id
+    WHERE rv.status IN ('approved', 'completed')
+    GROUP BY rv.user_id, r.name
+    ORDER BY rv.user_id, COUNT(*) DESC
+)
+SELECT
+    u.first_name || ' ' || u.last_name AS user_name,
+    ut.type_name AS user_type,
+    COALESCE(us.total_reservations, 0) AS total_reservations,
+    COALESCE(us.approved, 0) AS approved,
+    COALESCE(us.rejected, 0) AS rejected,
+    COALESCE(us.cancelled, 0) AS cancelled,
+    COALESCE(us.cancellation_rate, 0) AS cancellation_rate_pct,
+    COALESCE(us.avg_duration_hours, 0) AS avg_duration_hours,
+    COALESCE(us.total_hours_used, 0) AS total_hours_used,
+    COALESCE(us.distinct_resources_used, 0) AS distinct_resources,
+    COALESCE(fr.favorite_resource, '-') AS most_used_resource,
+    COALESCE(fr.use_count, 0) AS most_used_count,
+    RANK() OVER (ORDER BY COALESCE(us.total_reservations, 0) DESC) AS activity_rank
+FROM users u
+JOIN user_types ut ON u.type_id = ut.type_id
+LEFT JOIN user_stats us ON u.user_id = us.user_id
+LEFT JOIN favorite_resource fr ON u.user_id = fr.user_id
+ORDER BY activity_rank;
+}}}
+
+=== Solution Relational Algebra ===
+
+{{{
+UserStats <-
+  γ user_id;
+    total_reservations := COUNT(*),
+    approved := COUNT(σ status ∈ {'approved','completed'}),
+    rejected := COUNT(σ status = 'rejected'),
+    cancelled := COUNT(σ status = 'cancelled'),
+    cancellation_rate := COUNT(σ status='cancelled') / COUNT(*) * 100,
+    avg_duration_hours := AVG(duration),
+    total_hours_used := SUM(duration WHERE status ∈ {'approved','completed'}),
+    distinct_resources := COUNT_DISTINCT(resource_id) (
+      π user_id, status, resource_id,
+        (end_time − start_time)/3600 → duration (reservations)
+  )
+
+FavoriteResource <-
+  γ user_id; favorite_resource := r.name HAVING MAX(use_count) (
+    γ user_id, r.name; use_count := COUNT(*) (
+      σ status ∈ {'approved','completed'} (
+        reservations rv ⨝ (rv.resource_id = r.resource_id) resources r
+      )
+    )
+  )
+
+Result <-
+  π user_name, user_type, total_reservations, approved, rejected, cancelled,
+    cancellation_rate, avg_duration_hours, total_hours_used,
+    distinct_resources, favorite_resource, activity_rank (
+      (
+        (users u ⨝ (u.type_id = ut.type_id) user_types ut)
+        ⟕ (u.user_id = us.user_id) UserStats us
+      )
+      ⟕ (u.user_id = fr.user_id) FavoriteResource fr
+  )
+}}}
+
+== 4. Administrator Approval Workload and Bottleneck Analysis ==
+
+Shows each administrator's approval workload, approval/rejection rates, workload share percentage, and system-wide pending reservation wait times. Helps management identify if approval responsibilities are evenly distributed and whether pending reservations are being handled in a timely manner.
+
+=== Solution SQL ===
+
+{{{
+SET search_path TO project;
+
+WITH admin_stats AS (
+    SELECT
+        rv.approved_by AS admin_id,
+        COUNT(*) AS total_reviewed,
+        COUNT(*) FILTER (WHERE rv.status IN ('approved', 'completed')) AS approved_count,
+        COUNT(*) FILTER (WHERE rv.status = 'rejected') AS rejected_count,
+        ROUND(COUNT(*) FILTER (WHERE rv.status IN ('approved', 'completed'))::NUMERIC
+              / NULLIF(COUNT(*), 0) * 100, 1) AS approval_rate,
+        COUNT(DISTINCT rv.resource_id) AS distinct_resources_handled,
+        COUNT(DISTINCT rv.user_id) AS distinct_users_served
+    FROM reservations rv
+    WHERE rv.approved_by IS NOT NULL
+    GROUP BY rv.approved_by
+),
+pending_stats AS (
+    SELECT
+        COUNT(*) AS total_pending,
+        ROUND(AVG(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - rv.created_at))
+              / 86400.0), 1) AS avg_wait_days,
+        MAX(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - rv.created_at))
+            / 86400.0)::INT AS max_wait_days
+    FROM reservations rv
+    WHERE rv.status = 'pending'
+),
+workload_share AS (
+    SELECT
+        admin_id,
+        total_reviewed,
+        ROUND(total_reviewed::NUMERIC / SUM(total_reviewed) OVER () * 100, 1)
+            AS workload_share_pct
+    FROM admin_stats
+)
+SELECT
+    u.first_name || ' ' || u.last_name AS administrator,
+    COALESCE(ast.total_reviewed, 0) AS total_reviewed,
+    COALESCE(ast.approved_count, 0) AS approved,
+    COALESCE(ast.rejected_count, 0) AS rejected,
+    COALESCE(ast.approval_rate, 0) AS approval_rate_pct,
+    COALESCE(ws.workload_share_pct, 0) AS workload_share_pct,
+    COALESCE(ast.distinct_resources_handled, 0) AS resources_handled,
+    COALESCE(ast.distinct_users_served, 0) AS users_served,
+    ps.total_pending AS system_pending_count,
+    ps.avg_wait_days AS pending_avg_wait_days,
+    ps.max_wait_days AS pending_max_wait_days
+FROM users u
+JOIN user_types ut ON u.type_id = ut.type_id
+LEFT JOIN admin_stats ast ON u.user_id = ast.admin_id
+LEFT JOIN workload_share ws ON u.user_id = ws.admin_id
+CROSS JOIN pending_stats ps
+WHERE ut.type_name = 'Administrator'
+ORDER BY total_reviewed DESC;
+}}}
+
+=== Solution Relational Algebra ===
+
+{{{
+AdminStats <-
+  γ admin_id := approved_by;
+    total_reviewed := COUNT(*),
+    approved_count := COUNT(σ status ∈ {'approved','completed'}),
+    rejected_count := COUNT(σ status = 'rejected'),
+    approval_rate := COUNT(σ status ∈ {'approved','completed'}) / COUNT(*) * 100,
+    distinct_resources := COUNT_DISTINCT(resource_id),
+    distinct_users := COUNT_DISTINCT(user_id) (
+      σ approved_by IS NOT NULL (reservations)
+  )
+
+PendingStats <-
+  γ total_pending := COUNT(*),
+    avg_wait_days := AVG((NOW() − created_at) / 86400),
+    max_wait_days := MAX((NOW() − created_at) / 86400) (
+      σ status = 'pending' (reservations)
+  )
+
+WorkloadShare <-
+  π admin_id, total_reviewed,
+    total_reviewed / SUM(total_reviewed) * 100 → workload_share_pct (
+      AdminStats
+  )
+
+Result <-
+  π administrator, total_reviewed, approved, rejected, approval_rate,
+    workload_share_pct, resources_handled, users_served,
+    total_pending, avg_wait_days, max_wait_days (
+      (
+        (
+          σ type_name = 'Administrator' (users u ⨝ user_types ut)
+          ⟕ (u.user_id = ast.admin_id) AdminStats ast
+        )
+        ⟕ (u.user_id = ws.admin_id) WorkloadShare ws
+      )
+      × PendingStats ps
+  )
+}}}
+
+== 5. Resource Recommendation Based on Similar Users ==
+
+Collaborative filtering report: given a target user, finds other users with similar reservation patterns (shared resources), then recommends resources those similar users have used that the target user has not tried yet. Useful for suggesting relevant resources to users and improving resource discovery.
+
+=== Solution SQL ===
+
+{{{
+SET search_path TO project;
+
+WITH target_user AS (
+    SELECT 5 AS user_id  -- parameterize per user
+),
+target_resources AS (
+    SELECT DISTINCT rv.resource_id
+    FROM reservations rv, target_user tu
+    WHERE rv.user_id = tu.user_id
+      AND rv.status IN ('approved', 'completed')
+),
+similar_users AS (
+    SELECT
+        rv.user_id AS similar_user_id,
+        COUNT(DISTINCT rv.resource_id) AS shared_resources,
+        RANK() OVER (ORDER BY COUNT(DISTINCT rv.resource_id) DESC) AS similarity_rank
+    FROM reservations rv
+    JOIN target_resources tr ON rv.resource_id = tr.resource_id
+    CROSS JOIN target_user tu
+    WHERE rv.user_id != tu.user_id
+      AND rv.status IN ('approved', 'completed')
+    GROUP BY rv.user_id
+),
+recommended_resources AS (
+    SELECT
+        rv.resource_id,
+        r.name AS resource_name,
+        rt.type_name AS resource_type,
+        COALESCE(l.building || ' ' || l.room, 'Digital') AS location,
+        COUNT(DISTINCT rv.user_id) AS recommended_by_count,
+        ROUND(AVG(EXTRACT(EPOCH FROM (rv.end_time - rv.start_time)) / 3600.0), 1)
+            AS avg_usage_hours
+    FROM reservations rv
+    JOIN similar_users su ON rv.user_id = su.similar_user_id
+    JOIN resources r ON rv.resource_id = r.resource_id
+    JOIN resource_types rt ON r.type_id = rt.type_id
+    LEFT JOIN locations l ON r.location_id = l.location_id
+    WHERE rv.status IN ('approved', 'completed')
+      AND su.similarity_rank <= 5
+      AND rv.resource_id NOT IN (SELECT resource_id FROM target_resources)
+    GROUP BY rv.resource_id, r.name, rt.type_name, l.building, l.room
+)
+SELECT
+    u.first_name || ' ' || u.last_name AS target_user,
+    rr.resource_name,
+    rr.resource_type,
+    rr.location,
+    rr.recommended_by_count AS similar_users_use_it,
+    rr.avg_usage_hours,
+    RANK() OVER (ORDER BY rr.recommended_by_count DESC,
+                          rr.avg_usage_hours DESC) AS recommendation_rank
+FROM recommended_resources rr
+CROSS JOIN target_user tu
+JOIN users u ON tu.user_id = u.user_id
+ORDER BY recommendation_rank;
+}}}
+
+=== Solution Relational Algebra ===
+
+{{{
+TargetUser <- {(user_id: 5)}
+
+TargetResources <-
+  π resource_id (
+    σ status ∈ {'approved','completed'} (
+      reservations ⨝ (user_id) TargetUser
+    )
+  )
+
+SimilarUsers <-
+  ω ORDER BY shared_resources DESC; similarity_rank := RANK() (
+    γ similar_user_id := rv.user_id;
+      shared_resources := COUNT_DISTINCT(rv.resource_id) (
+        σ rv.user_id ≠ tu.user_id ∧ rv.status ∈ {'approved','completed'} (
+          (reservations rv ⨝ (rv.resource_id = tr.resource_id) TargetResources tr)
+          × TargetUser tu
+        )
+    )
+  )
+
+RecommendedResources <-
+  γ resource_id, resource_name, resource_type, location;
+    recommended_by := COUNT_DISTINCT(user_id),
+    avg_usage_hours := AVG(duration) (
+      π rv.resource_id, r.name → resource_name, rt.type_name → resource_type,
+        l.building || l.room → location, rv.user_id,
+        (rv.end_time − rv.start_time)/3600 → duration (
+          σ rv.status ∈ {'approved','completed'} ∧ su.similarity_rank ≤ 5 (
+            (
+              (
+                reservations rv
+                ⨝ (rv.user_id = su.similar_user_id) σ similarity_rank ≤ 5 (SimilarUsers su)
+              )
+              ⨝ (rv.resource_id = r.resource_id) resources r
+              ⨝ (r.type_id = rt.type_id) resource_types rt
+              ⟕ (r.location_id = l.location_id) locations l
+            )
+            − (π * (reservations rv ⨝ TargetResources))
+          )
+      )
+  )
+
+Result <-
+  π target_user, resource_name, resource_type, location,
+    recommended_by, avg_usage_hours, recommendation_rank (
+      ω ORDER BY recommended_by DESC, avg_usage_hours DESC;
+        recommendation_rank := RANK() (
+          RecommendedResources × (TargetUser ⨝ users)
+      )
+  )
+}}}