Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

06/14/26 21:20:58 (2 days ago)

Author:

213257

Comment:

--

AdvancedReports

@@ +1 @@
+= 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)
+}}}
+
+----