Domify

Напредни извештаи од базата (SQL и складирани процедури)

1. Извештај за вкупни приходи на изнајмувачите на недвижности за нивните изнајмени единици како и просечен приход остварен по изнајмена единица во тековната година.

SELECT 
    u.first_name,
    u.last_name,
    u.email,
    u.rating,
    lp.is_agent,
    COUNT(DISTINCT l.id) AS lease_count,
    COUNT(DISTINCT l.tenant_id) AS tenant_count,
    COALESCE(SUM(p.amount), 0) AS total_revenue,
    ROUND(
        CASE WHEN COUNT(DISTINCT l.id) > 0 
             THEN COALESCE(SUM(p.amount), 0) / COUNT(DISTINCT l.id)
             ELSE 0
        END, 2
    ) AS avg_revenue_per_lease
FROM LandlordProfile lp
JOIN UserD u ON u.id = lp.id
JOIN Lease l ON lp.id = l.landlord_id
LEFT JOIN Payment p 
    ON p.lease_id = l.id 
    AND p.payment_date >= date_trunc('year', CURRENT_DATE)
GROUP BY u.id, u.first_name, u.last_name, u.email, u.rating, lp.is_agent
ORDER BY total_revenue DESC;

2. Извештај за перформансите на недвижностите по градови: број на имоти, број на издавачки единици, активни единици, остварени приходи за тековната година по град како и просечна кирија по изнајмувачка единица како и остварен приход по објект.

SELECT 
    a.city,
    COUNT(DISTINCT p.id) as total_properties,
    COUNT(DISTINCT u.id) as total_units,
    COUNT(DISTINCT l.id) as active_leases,
    SUM(pay.amount) as total_revenue,
    ROUND(AVG(l.rent_amount),2) as avg_rent_per_lease,
    ROUND (SUM(pay.amount) / COUNT(DISTINCT p.id),2) as revenue_per_property
FROM Address a
JOIN Property p ON a.id = p.address_id
JOIN Unit u ON p.id = u.property_id
JOIN Listing lst ON u.id = lst.unit_id
JOIN Lease l ON lst.id = l.listing_id
JOIN Payment pay ON l.id = pay.lease_id
WHERE EXTRACT(YEAR FROM pay.payment_date) = EXTRACT(YEAR FROM CURRENT_DATE)
    AND pay.status = 'завршено'
GROUP BY a.city
ORDER BY total_revenue DESC;