Changes between Version 4 and Version 5 of AdvancedReports

Timestamp:

06/27/25 02:28:17 (10 days ago)

Author:

221012

Comment:

--

AdvancedReports

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