Changes between Version 2 and Version 3 of AdvancedReports

Timestamp:

06/15/26 01:21:50 (2 days ago)

Author:

213257

Comment:

--

AdvancedReports

@@ -184 +184 @@
 ORDER BY engagement_rank;
 }}}
+
+=== Relational Algebra ===
+
+Selecting recent inquiries and joining to client and unit:
+
+{{{
+RecentInquiries ← σ(created_at ≥ CURRENT_DATE − 180)(Inquiry)
+
+InquiryClientUnit ← RecentInquiries
+    ⋈(inquiry.client_id = client.client_id) Client
+    ⋈(inquiry.unit_id = unit.unit_id) Unit
+}}}
+
+Aggregating per client — inquiry-side statistics:
+
+{{{
+ClientInquiryStats ← γ(
+    client_id, name, email, phone ;
+    COUNT(inquiry_id)  → total_inquiries,
+    MIN(created_at)    → first_inquiry_date,
+    MAX(created_at)    → last_inquiry_date,
+    AVG(price)         → avg_inquired_price
+)(InquiryClientUnit)
+}}}
+
+Selecting recent appointments and joining to timeslot:
+
+{{{
+RecentAppts ← σ(timeslot.date ≥ CURRENT_DATE − 180)(
+    Appointment ⋈(appointment.timeslot_id = timeslot.timeslot_id) Timeslot
+)
+}}}
+
+Aggregating per client — appointment-side statistics:
+
+{{{
+ClientApptStats ← γ(
+    client_id ;
+    COUNT(appointment_id) → total_appointments,
+    MIN(timeslot.date)    → first_appointment_date
+)(RecentAppts)
+}}}
+
+Left joining, deriving interest classification and ranking:
+
+{{{
+Combined ← ClientInquiryStats ⟕(client_id = client_id) ClientApptStats
+
+WithScore ← ρ(
+    interest        ← IF(total_appointments > 0, 'Warm Interest', 'Cold Interest'),
+    engagement_rank ← RANK() OVER (
+                          ORDER BY (total_inquiries + total_appointments · 3) DESC
+                      )
+)(Combined)
+
+Result ← τ(engagement_rank)(WithScore)
+
+π(engagement_rank, name, email, phone,
+  interest, total_inquiries, total_appointments,
+  days_to_first_appointment, avg_inquired_price,
+  first_inquiry_date, last_inquiry_date)(Result)
+}}}