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;

3. Извештај за процент на изнајмени недвижности во однос на објавени огласи за секоја година.

WITH total_listings AS (
    SELECT 
        EXTRACT(YEAR FROM available_from) AS year,
        COUNT(*) AS listing_count
    FROM Listing
    GROUP BY year
),
converted_listings AS (
    SELECT 
        EXTRACT(YEAR FROM l.start_date) AS year,
        COUNT(DISTINCT li.id) AS converted
    FROM Lease l
    JOIN Listing li ON l.listing_id = li.id
    GROUP BY year
)
SELECT 
    tl.year,
    tl.listing_count,
    COALESCE(cl.converted, 0) AS converted,
    ROUND(100.0 * COALESCE(cl.converted, 0) / tl.listing_count, 2) AS conversion_rate
FROM total_listings tl
LEFT JOIN converted_listings cl ON tl.year = cl.year
ORDER BY tl.year;

4. Извештај за раст на приходи (извршени успешни плаќања за изнајмувања) за претходна и тековна година за секој град изразена во проценти.

WITH yearly_income AS (
    SELECT 
        a.city,
        EXTRACT(YEAR FROM p.payment_date) AS year,
        SUM(p.amount) AS total_revenue
    FROM Payment p
    JOIN Lease l ON l.id = p.lease_id
    JOIN Listing li ON li.id = l.listing_id
    JOIN Unit u ON li.unit_id = u.id
    JOIN Property pr ON u.property_id = pr.id
    JOIN Address a ON pr.address_id = a.id
    WHERE p.status = 'завршено'
    GROUP BY a.city, year
),
diffs AS (
    SELECT 
        curr.city,
        curr.total_revenue AS this_year,
        prev.total_revenue AS last_year,
        ROUND(((curr.total_revenue - prev.total_revenue) / NULLIF(prev.total_revenue, 0)) * 100, 2) AS growth_percent
    FROM yearly_income curr
    JOIN yearly_income prev ON prev.city = curr.city AND curr.year = prev.year + 1
    WHERE curr.year = EXTRACT(YEAR FROM CURRENT_DATE)
)
SELECT * 
FROM diffs
ORDER BY growth_percent DESC;