= Advanced Reports =

== Unit Demand — 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)
}}}

----

== Client Engagement & Conversion — Semi-Annual ==

What steps do clients go through over a six month period?
This report segments the clients into two types - Cold interest meaning client only made an inquiry,
and Warm interest meaning client made an appointment. Ranking them by an engagement score based on the inquiry volume and the appointment count.
Showing the average price range for each client and the time taken from first inquiry to an appointment.
With the goal for giving insight into the behaviour of clients.

=== SQL

{{{
WITH client_inquiries AS (
    SELECT
        c.client_id,
        c.name AS client_name,
        c.email AS client_email,
        c.phone AS client_phone,
        COUNT(DISTINCT i.inquiry_id) AS total_inquiries,
        MIN(i.created_at) AS first_inquiry_date,
        MAX(i.created_at) AS last_inquiry_date,
        ROUND(AVG(u.price), 2) AS avg_inquired_price
    FROM project.client c
    JOIN project.inquiry i  ON c.client_id = i.client_id
        AND i.created_at >= CURRENT_DATE - INTERVAL '6 months'
    JOIN project.unit u     ON i.unit_id   = u.unit_id
    GROUP BY c.client_id, c.name, c.email, c.phone
),
client_appointments AS (
    SELECT
        c.client_id,
        COUNT(DISTINCT a.appointment_id) AS total_appointments,
        MIN(ts.date) AS first_appointment_date
    FROM project.client c
    JOIN project.appointment a ON c.client_id = a.client_id
    JOIN project.timeslot ts ON a.timeslot_id = ts.timeslot_id
    WHERE ts.date >= CURRENT_DATE - INTERVAL '6 months'
    GROUP BY c.client_id
)

SELECT
    RANK() OVER (
        ORDER BY (
            ci.total_inquiries +
            COALESCE(ca.total_appointments, 0) * 3
        ) DESC
    ) AS engagement_rank,
    ci.client_name,
    ci.client_email,
    ci.client_phone,
    CASE
        WHEN COALESCE(ca.total_appointments, 0) > 0 THEN 'Warm Interest'
        ELSE 'Cold Interest'
    end AS interest,
    ci.total_inquiries,
    COALESCE(ca.total_appointments, 0) AS total_appointments,
    CASE
        WHEN ca.first_appointment_date IS NOT NULL
        THEN (ca.first_appointment_date - ci.first_inquiry_date::date)
        ELSE NULL
    end AS days_to_first_appointment,
    ci.avg_inquired_price,
    ci.first_inquiry_date,
    ci.last_inquiry_date
FROM client_inquiries ci
LEFT JOIN client_appointments ca ON ci.client_id = ca.client_id
ORDER BY engagement_rank;
}}}