Changes between Version 2 and Version 3 of Complex DB Reports

Timestamp:

02/06/26 18:49:34 (43 hours ago)

Author:

231035

Comment:

--

Complex DB Reports

@@ -106 +106 @@
 ORDER BY activity_rank;
 }}}
-=== Relation
-{{{
-Relation
-}}}
+=== Relation Algebra
+{{{
+Period <-
+{ (start_ts, end_ts) }
+
+ListingsByUser <-
+γ
+  user_id := l.owner_id;
+  listings_created := COUNT(*)
+(
+  listings l ⨝
+  (l.created_at ≥ p.start_ts ∧ l.created_at < p.end_ts)
+  Period p
+)
+ReviewsByUser <-
+γ
+  user_id := r.reviewer_id;
+  reviews_left := COUNT(*);
+  avg_rating_left := AVG(r.rating)
+(
+  reviews r ⨝
+  (r.created_at ≥ p.start_ts ∧ r.created_at < p.end_ts)
+  Period p
+)
+AppointmentsByUser <-
+γ
+  user_id := a.responsible_owner_id;
+  appointments_total := COUNT(*);
+  appointments_done := COUNT(a.status = 'DONE');
+  appointments_no_show := COUNT(a.status = 'NO_SHOW');
+  appointments_cancelled := COUNT(a.status = 'CANCELLED')
+(
+  appointments a ⨝
+  (a.date_time ≥ p.start_ts ∧ a.date_time < p.end_ts)
+  Period p
+)
+
+FavoritesByUser <-
+γ
+  user_id := f.client_id;
+  favorites_saved_all_time := COUNT(*)
+(
+  favorite_listings f
+)
+
+UserActivity <-
+((((
+     users u
+     ⟕ (u.user_id = l.user_id) ListingsByUser l
+    )
+   ⟕ (u.user_id = r.user_id) ReviewsByUser r
+   )
+  ⟕ (u.user_id = a.user_id) AppointmentsByUser a
+ )
+ ⟕ (u.user_id = f.user_id) FavoritesByUser f
+)
+ActivityWithDefaults <-
+π
+  u.user_id,
+  u.username,
+  u.email,
+  u.name,
+  u.surname,
+  listings_created := COALESCE(l.listings_created, 0),
+  reviews_left := COALESCE(r.reviews_left, 0),
+  avg_rating_left := COALESCE(r.avg_rating_left, 0),
+  appointments_total := COALESCE(a.appointments_total, 0),
+  appointments_done := COALESCE(a.appointments_done, 0),
+  appointments_no_show := COALESCE(a.appointments_no_show, 0),
+  appointments_cancelled := COALESCE(a.appointments_cancelled, 0),
+  favorites_saved_all_time := COALESCE(f.favorites_saved_all_time, 0)
+(
+  UserActivity
+)
+
+ActiveUsers <-
+σ
+  listings_created +
+  reviews_left +
+  appointments_total +
+  favorites_saved_all_time > 0
+(
+  ActivityWithDefaults
+)
+ScoredUsers <-
+π
+  *,
+  activity_score :=
+    listings_created * 5 +
+    reviews_left * 3 +
+    appointments_done * 2 +
+    favorites_saved_all_time * 1 -
+    appointments_no_show * 2
+(
+  ActiveUsers
+)
+
+RankedUsers <-
+rank_dense
+  activity_rank :=
+    ORDER BY
+      activity_score DESC,
+      listings_created DESC,
+      reviews_left DESC
+(
+  ScoredUsers
+)
+Result <-
+τ activity_rank ASC
+(
+  π
+    user_id,
+    username,
+    email,
+    name,
+    surname,
+    listings_created,
+    reviews_left,
+    avg_rating_left,
+    appointments_total,
+    appointments_done,
+    appointments_no_show,
+    appointments_cancelled,
+    favorites_saved_all_time,
+    activity_score,
+    activity_rank
+  (
+    RankedUsers
+  )
+)
+
+}}}
+
+== 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.
+This query:
+* 
+=== SQL
+{{{
+}}}
+=== Relation Algebra
+{{{
+
+}}}