Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

06/26/25 23:42:13 (10 days ago)

Author:

221012

Comment:

--

AdvancedReports

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