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;

5. Извештај за цени за изнајмување на недвижности (просечна, минимална, максимална и цена по метар квадратен) согласно категорија на објект, град, број на соби, големина на објект.

SELECT 
    pt.name AS property_type,
    a.city,
    u.bedrooms,
    CASE 
        WHEN u.area_sq_m < 50 THEN 'Мала (<50м²)'
        WHEN u.area_sq_m < 80 THEN 'Средна (50-80м²)'
        ELSE 'Голема (>80м²)'
    END AS size_category,
    COUNT(l.id) AS lease_count,
    ROUND(AVG(l.rent_amount), 2) AS avg_rent,
    ROUND(MIN(l.rent_amount), 2) AS min_rent,
    ROUND(MAX(l.rent_amount), 2) AS max_rent,
    ROUND(AVG(l.rent_amount / u.area_sq_m), 2) AS price_per_sqm
FROM Lease l
JOIN Listing li ON l.listing_id = li.id
JOIN Unit u ON li.unit_id = u.id
JOIN Property p ON u.property_id = p.id
JOIN PropertyType pt ON p.property_type_id = pt.id
JOIN Address a ON p.address_id = a.id
WHERE l.start_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY pt.name, a.city, u.bedrooms, size_category
HAVING COUNT(l.id) >= 1
ORDER BY a.city, pt.name, u.bedrooms;

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

SELECT 
    EXTRACT(YEAR FROM li.available_from) AS year, 
    EXTRACT(MONTH FROM li.available_from) AS month,
    COUNT(*) AS total_listings,
    AVG(l.start_date - li.available_from) AS avg_days_on_market
FROM Listing li 
JOIN Lease l ON li.id = l.listing_id 
WHERE li.available_from >= CURRENT_DATE - INTERVAL '365 days' AND l.start_date BETWEEN li.available_from AND li.available_to
GROUP BY year, month 
ORDER BY year, month;

7. Извештај за број на барања за поправка/сервисирање на недвижност по град

SELECT
  a.city,
  COUNT(sr.id) AS request_count
FROM ServiceRequest sr
JOIN Lease l ON sr.lease_id = l.id
JOIN Listing li ON l.listing_id = li.id
JOIN Unit u ON li.unit_id = u.id
JOIN Property p ON u.property_id = p.id
JOIN Address a ON p.address_id = a.id
WHERE EXTRACT(YEAR FROM sr.request_date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY a.city
ORDER BY request_count DESC

8. Извештај за заинтересираност на изнајмувачи, издавачи со најмногу заинтересирани огласи сортирани по популарност

SELECT 
    u.first_name, 
    u.last_name,
    u.email,
    a.city,
    COUNT(DISTINCT li.id) AS total_listings,
    COUNT(i.tenant_profile_id) AS total_interested_tenants,
	ROUND(COUNT(i.tenant_profile_id) / COUNT(DISTINCT li.id), 2) AS avg_interest_per_listing
FROM LandlordProfile lp
JOIN UserD u ON lp.id = u.id
JOIN Property p ON p.owner_id = u.id
JOIN Unit un ON un.property_id = p.id
JOIN Listing li ON li.unit_id = un.id
JOIN Address a ON p.address_id = a.id
LEFT JOIN Interested i ON li.id = i.listing_id
WHERE li.available_from >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY u.id, u.first_name, u.last_name, u.email, a.city
HAVING COUNT(i.tenant_profile_id) > 0
ORDER BY total_interested_tenants DESC;