Changes between Version 3 and Version 4 of Complex DB Reports

Timestamp:

02/07/26 16:36:10 (21 hours ago)

Author:

231035

Comment:

--

Complex DB Reports

@@ -2 +2 @@
 == Finding the most active users on the app during a given time period
 This SQL query wants to find the most active users. Active are users who create listings, write reviews, handle appointments and save favorites.
+[[BR]]
 This query:
 * Counts each type of activity
@@ -109 +110 @@
 {{{
 Period <-
-{ (start_ts, end_ts) }
+{(start_ts, end_ts)}
 
 ListingsByUser <-
@@ -147 +148 @@
   user_id := f.client_id;
   favorites_saved_all_time := COUNT(*)
-(
-  favorite_listings f
-)
+(favorite_listings f)
 
 UserActivity <-
@@ -177 +176 @@
   appointments_cancelled := COALESCE(a.appointments_cancelled, 0),
   favorites_saved_all_time := COALESCE(f.favorites_saved_all_time, 0)
-(
-  UserActivity
-)
+(UserActivity)
 
 ActiveUsers <-
@@ -187 +184 @@
   appointments_total +
   favorites_saved_all_time > 0
-(
-  ActivityWithDefaults
-)
+(ActivityWithDefaults)
 ScoredUsers <-
 π
@@ -199 +194 @@
     favorites_saved_all_time * 1 -
     appointments_no_show * 2
-(
-  ActiveUsers
-)
+(ActiveUsers)
 
 RankedUsers <-
@@ -210 +203 @@
       listings_created DESC,
       reviews_left DESC
-(
-  ScoredUsers
-)
+(ScoredUsers)
 Result <-
 τ activity_rank ASC
@@ -232 +223 @@
     activity_score,
     activity_rank
-  (
-    RankedUsers
-  )
+  (RankedUsers)
 )
 
@@ -241 +230 @@
 == Recommending listings to a user by similar users and liked listings
 This SQL query wants to find the recommended listings for a user based on similar users and his liked listings.
+[[BR]]
 This query:
-* 
+* Gets the recent likes of the user
+* Gets the similar users based on same liked listings
+* Gets listings liked by the similar users but NOT by me
+* Gets listings similar to my liked listings 
+* Combines them both
 === SQL
 {{{
+WITH
+my_likes AS (
+  SELECT fl.listing_id
+  FROM favorite_listings fl
+  WHERE fl.client_id = :user_id
+),
+
+my_recent_likes AS (
+  SELECT fl.listing_id
+  FROM favorite_listings fl
+  JOIN listings l ON l.listing_id = fl.listing_id
+  WHERE fl.client_id = :user_id
+  ORDER BY l.created_at DESC
+  LIMIT :10
+),
+
+similar_users AS (
+  SELECT
+    fl2.client_id AS other_user_id,
+    COUNT(*)      AS overlap_likes
+  FROM favorite_listings fl2
+  JOIN my_likes ml ON ml.listing_id = fl2.listing_id
+  WHERE fl2.client_id <> :user_id
+  GROUP BY fl2.client_id
+  HAVING COUNT(*) > 0
+),
+
+cf_candidates AS (
+  SELECT
+    fl.listing_id,
+    SUM(su.overlap_likes) AS cf_score,
+    COUNT(DISTINCT su.other_user_id) AS liked_by_similar_users
+  FROM similar_users su
+  JOIN favorite_listings fl
+    ON fl.client_id = su.other_user_id
+  LEFT JOIN my_likes ml
+    ON ml.listing_id = fl.listing_id
+  WHERE ml.listing_id IS NULL  
+  GROUP BY fl.listing_id
+),
+
+content_candidates AS (
+  SELECT
+    l2.listing_id,
+    COUNT(*) AS content_score
+  FROM my_recent_likes r
+  JOIN listings l1 ON l1.listing_id = r.listing_id
+  JOIN listings l2 ON
+      l2.listing_id <> l1.listing_id
+      AND (
+           (l2.species = l1.species)
+        OR (l2.breed   = l1.breed)
+        OR (l2.location = l1.location)
+      )
+  LEFT JOIN my_likes ml ON ml.listing_id = l2.listing_id
+  WHERE ml.listing_id IS NULL
+  GROUP BY l2.listing_id
+),
+
+
+merged AS (
+  SELECT
+    COALESCE(cf.listing_id, cc.listing_id) AS listing_id,
+    COALESCE(cf.cf_score, 0)              AS cf_score,
+    COALESCE(cf.liked_by_similar_users, 0) AS liked_by_similar_users,
+    COALESCE(cc.content_score, 0)         AS content_score
+  FROM cf_candidates cf
+  FULL OUTER JOIN content_candidates cc
+    ON cc.listing_id = cf.listing_id
+)
+
+SELECT
+  l.listing_id,
+  l.title,
+  l.species,
+  l.breed,
+  l.location,
+  l.created_at,
+
+  m.cf_score,
+  m.liked_by_similar_users,
+  m.content_score,
+
+  (m.cf_score * 3 + m.content_score * 2) AS final_score
+
+FROM merged m
+JOIN listings l ON l.listing_id = m.listing_id
+WHERE l.status = 'ACTIVE'
+ORDER BY final_score DESC, l.created_at DESC
+LIMIT 20;
 }}}
 === Relation Algebra
 {{{
+Params <-
+{(user_id := U, k_recent := 10, top_n := 20)}
+MyLikes <-
+π listing_id
+(
+  σ fl.client_id = p.user_id
+  (
+    favorite_listings fl × Params p
+  )
+)
+MyRecentLikes <-
+topK_{K := p.k_recent}
+(
+  τ l.created_at DESC
+  (
+    π fl.listing_id, l.created_at
+    (
+      σ fl.client_id = p.user_id
+      (
+        (favorite_listings fl ⨝ (fl.listing_id = l.listing_id) listings l)
+        × Params p
+      )
+    )
+  )
+)
+SimilarUsers <-
+σ other_user_id ≠ p.user_id ∧ overlap_likes > 0
+(
+  γ
+    other_user_id := fl2.client_id;
+    overlap_likes := COUNT(*)
+  (
+    (
+      favorite_listings fl2 ⨝ (fl2.listing_id = ml.listing_id) MyLikes ml
+    )
+    × Params p
+  )
+)
+CFCandidates <-
+γ
+  listing_id := fl.listing_id;
+  cf_score := SUM(su.overlap_likes);
+  liked_by_similar_users := COUNT_DISTINCT(su.other_user_id)
+(
+  σ ml.listing_id IS NULL
+  (
+    (
+      (SimilarUsers su ⨝ (su.other_user_id = fl.client_id) favorite_listings fl)
+      ⟕ (fl.listing_id = ml.listing_id) MyLikes ml
+    )
+  )
+)
+
+ContentCandidates <-
+γ
+  listing_id := l2.listing_id;
+  content_score := COUNT(*)
+(
+  σ ml.listing_id IS NULL
+  (
+    (
+      (
+        (MyRecentLikes r ⨝ (r.listing_id = l1.listing_id) listings l1)
+        ⨝
+        (
+          l2.listing_id ≠ l1.listing_id ∧
+          (l2.species = l1.species ∨ l2.breed = l1.breed ∨ l2.location = l1.location)
+        )
+        listings l2
+      )
+      ⟕ (l2.listing_id = ml.listing_id) MyLikes ml
+    )
+  )
+)
+Merged <-
+π
+  listing_id := COALESCE(cf.listing_id, cc.listing_id),
+  cf_score := COALESCE(cf.cf_score, 0),
+  liked_by_similar_users := COALESCE(cf.liked_by_similar_users, 0),
+  content_score := COALESCE(cc.content_score, 0)
+(CFCandidates cf ⟗ (cf.listing_id = cc.listing_id) ContentCandidates cc)
+FinalWithListings <-
+π
+  l.listing_id,
+  l.title,
+  l.species,
+  l.breed,
+  l.location,
+  l.created_at,
+  m.cf_score,
+  m.liked_by_similar_users,
+  m.content_score,
+  final_score := (m.cf_score * 3 + m.content_score * 2)
+(
+  σ l.status = 'ACTIVE'
+  ((Merged m ⨝ (m.listing_id = l.listing_id) listings l))
+)
+Result <-
+topK_{N := p.top_n}
+(
+  τ final_score DESC, created_at DESC
+  (FinalWithListings × Params p)
+)
 
 }}}