Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

07/07/26 23:17:58 (2 days ago)

Author:

231035

Comment:

--

AdvancedReports

@@ +1 @@
+= Advanced Reports
+== 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
+* Combines everything per user
+* Calculates the final score
+* Ranks the users from most to least active
+=== SQL
+{{{
+WITH params AS (
+    SELECT
+        CAST(:start_ts AS timestamp) AS start_ts,
+        CAST(:end_ts   AS timestamp) AS end_ts
+),
+
+    listings_by_user AS (
+         SELECT l.owner_id AS user_id, COUNT(*) AS listings_created
+         FROM listings l
+                  JOIN params p ON l.created_at >= p.start_ts AND l.created_at < p.end_ts
+         GROUP BY l.owner_id
+     ),
+     reviews_by_user AS (
+         SELECT r.reviewer_id AS user_id,
+                COUNT(*) AS reviews_left,
+                AVG(r.rating)::numeric(10,2) AS avg_rating_left
+         FROM reviews r
+                  JOIN params p ON r.created_at >= p.start_ts AND r.created_at < p.end_ts
+         GROUP BY r.reviewer_id
+     ),
+     appointments_by_user AS (
+         SELECT a.responsible_owner_id AS user_id,
+                COUNT(*) AS appointments_total,
+                COUNT(*) FILTER (WHERE a.status = 'DONE')      AS appointments_done,
+                COUNT(*) FILTER (WHERE a.status = 'NO_SHOW')   AS appointments_no_show,
+                COUNT(*) FILTER (WHERE a.status = 'CANCELLED') AS appointments_cancelled
+         FROM appointments a
+                  JOIN params p ON a.date_time >= p.start_ts AND a.date_time < p.end_ts
+         GROUP BY a.responsible_owner_id
+     ),
+     favorites_by_user AS (
+         SELECT f.client_id AS user_id, COUNT(*) AS favorites_saved_all_time
+         FROM favorite_listings f
+         GROUP BY f.client_id
+     )
+SELECT
+    u.user_id, u.username, u.email, u.name, u.surname,
+    COALESCE(l.listings_created, 0) AS listings_created,
+    COALESCE(rv.reviews_left, 0) AS reviews_left,
+    COALESCE(rv.avg_rating_left, 0) AS avg_rating_left,
+    COALESCE(ap.appointments_total, 0) AS appointments_total,
+    COALESCE(ap.appointments_done, 0) AS appointments_done,
+    COALESCE(ap.appointments_no_show, 0) AS appointments_no_show,
+    COALESCE(ap.appointments_cancelled, 0) AS appointments_cancelled,
+    COALESCE(fv.favorites_saved_all_time, 0) AS favorites_saved_all_time,
+    (
+        COALESCE(l.listings_created, 0) * 5
+            + COALESCE(rv.reviews_left, 0) * 3
+            + COALESCE(ap.appointments_done, 0) * 2
+            + COALESCE(fv.favorites_saved_all_time, 0)
+            - COALESCE(ap.appointments_no_show, 0) * 2
+        ) AS activity_score,
+    DENSE_RANK() OVER (
+        ORDER BY
+            (
+                COALESCE(l.listings_created, 0) * 5
+                    + COALESCE(rv.reviews_left, 0) * 3
+                    + COALESCE(ap.appointments_done, 0) * 2
+                    + COALESCE(fv.favorites_saved_all_time, 0)
+                    - COALESCE(ap.appointments_no_show, 0) * 2
+                ) DESC,
+            COALESCE(l.listings_created, 0) DESC,
+            COALESCE(rv.reviews_left, 0) DESC
+        ) AS activity_rank
+FROM users u
+         LEFT JOIN listings_by_user l ON l.user_id = u.user_id
+         LEFT JOIN reviews_by_user rv ON rv.user_id = u.user_id
+         LEFT JOIN appointments_by_user ap ON ap.user_id = u.user_id
+         LEFT JOIN favorites_by_user fv ON fv.user_id = u.user_id
+WHERE COALESCE(l.listings_created, 0)
+          + COALESCE(rv.reviews_left, 0)
+          + COALESCE(ap.appointments_total, 0)
+          + COALESCE(fv.favorites_saved_all_time, 0) > 0
+ORDER BY activity_rank
+LIMIT 10;
+}}}
+=== 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 <-
+π
+  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 :=
+    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 <-
+topK_{K := 10}
+(
+  τ 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.
+[[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 animals a1  ON a1.animal_id = l1.animal_id
+
+                 JOIN listings l2 ON l2.listing_id <> l1.listing_id
+                 JOIN animals a2  ON a2.animal_id = l2.animal_id
+
+                 LEFT JOIN my_likes ml ON ml.listing_id = l2.listing_id
+        WHERE ml.listing_id IS NULL
+          AND (
+            a2.species = a1.species
+                OR a2.breed = a1.breed
+                OR a2.located_name = a1.located_name
+            )
+        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,
+    a.name AS title,
+    a.species,
+    a.breed,
+    a.located_name AS 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
+         JOIN animals  a ON a.animal_id = l.animal_id
+WHERE l.status = 'ACTIVE'
+  AND l.owner_id <> :user_id
+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' ∧ l.owner_id ≠ p.user_id)
+  (
+    (Merged m ⨝ (m.listing_id = l.listing_id) listings l)
+    × Params p
+  )
+)
+
+Result <-
+topK_{N := p.top_n}
+(
+  τ final_score DESC, created_at DESC
+  (FinalWithListings × Params p)
+)
+
+}}}