Changes between Initial Version and Version 1 of ProcedureV1

Timestamp:

08/15/25 20:14:44 (4 days ago)

Author:

221071

Comment:

--

ProcedureV1

@@ +1 @@
+= 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;
+}}}