= Advanced Reports =

== Unit Demand Forecast — Quarterly Interest Ranking ==

Which currently available units show the strongest demand signals in the last quarter,
and which buildings are generating the most interest for units.
Each unit is scored by the inquiry count, appointment count. The units are ranked within their building,
with the goal to give insight into potential buyer interest in what types of flats potential buyers are mostly looking for.

=== SQL ===

{{{
WITH unit_stats AS (
    SELECT
        u.unit_id,
        u.unit_number,
        u.price,
        u.room_number,
        u.floor_area,
        f.floor_number,
        b.building_id,
        b.name                            AS building_name,
        COUNT(DISTINCT i.inquiry_id)      AS total_inquiries,
        COUNT(DISTINCT a.appointment_id)  AS total_appointments,
        ROUND(
            COUNT(DISTINCT a.appointment_id)::NUMERIC /
            NULLIF(COUNT(DISTINCT i.inquiry_id), 0) * 100, 2
        )                                 AS conversion_rate_pct
    FROM project.unit u
    JOIN project.floor f ON u.floor_id = f.floor_id
    JOIN project.building b ON f.building_id = b.building_id
    LEFT JOIN project.inquiry i ON u.unit_id = i.unit_id
        AND i.created_at >= CURRENT_DATE - INTERVAL '3 months'
    LEFT JOIN project.appointment a  ON u.unit_id = a.unit_id
        AND a.status = 'Scheduled'
    LEFT JOIN project.timeslot ts ON a.timeslot_id = ts.timeslot_id
        AND ts.date >= CURRENT_DATE - INTERVAL '3 months'
    WHERE u.status = 'Available'
    GROUP BY
        u.unit_id, u.unit_number, u.price, u.room_number, u.floor_area,
        f.floor_number, b.building_id, b.name
)
SELECT
    building_name,
    unit_number,
    floor_number,
    room_number,
    floor_area,
    price,
    total_inquiries,
    total_appointments,
    conversion_rate_pct,
    (total_inquiries + total_appointments * 2)  AS interest_score,
    RANK() OVER (
        PARTITION BY building_id
        ORDER BY (total_inquiries + total_appointments * 2) DESC
    )                                           AS rank_in_building
FROM unit_stats
ORDER BY interest_score DESC, building_name, rank_in_building;
}}}

=== Relational Algebra ===

Selection of available units and joining to floor and building

{{{
AvailableUnits ← σ(status = 'Available')(Unit)

UnitFloorBuilding ← AvailableUnits
    ⋈(unit.floor_id = floor.floor_id) Floor
    ⋈(floor.building_id = building.building_id) Building
}}}

Selecting recent inquires and scheduled appointments

{{{
RecentInquiries ← σ(created_at ≥ CURRENT_DATE − 90)(Inquiry)

RecentScheduledAppts ← σ(appointment.status = 'Scheduled' ∧ timeslot.date ≥ CURRENT_DATE − 90)(
    Appointment ⋈(appointment.timeslot_id = timeslot.timeslot_id) Timeslot
)
}}}

Left joining units to the inquires and appointments and per unit aggregation

{{{
UnitWithActivity ← UnitFloorBuilding
    ⟕(unit.unit_id = inquiry.unit_id) RecentInquiries
    ⟕(unit.unit_id = appointment.unit_id) RecentScheduledAppts

UnitStats ← γ(
    unit_id, unit_number, price, room_number, floor_area,
    floor_number, building_id, name ;
    COUNT(inquiry_id)     → total_inquiries,
    COUNT(appointment_id) → total_appointments,
    COUNT(appointment_id) / NULLIF(COUNT(inquiry_id), 0) * 100
                          → conversion_rate_pct
)(UnitWithActivity)
}}}

Interest score, ranking within building, and final columns

{{{
Scored ← ρ(
    interest_score   ← total_inquiries + total_appointments · 2,
    rank_in_building ← RANK() OVER (PARTITION BY building_id
                                    ORDER BY interest_score DESC)
)(UnitStats)

Result ← τ(interest_score DESC, name, rank_in_building)(Scored)

π(name, unit_number, floor_number, room_number, floor_area,
  price, total_inquiries, total_appointments,
  conversion_rate_pct, interest_score, rank_in_building)(Result)
}}}

----