= 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)
      )
  )
}}}