Changes between Initial Version and Version 1 of Complex DB Reports

Timestamp:

02/06/26 17:26:14 (2 days ago)

Author:

231035

Comment:

--

Complex DB Reports

@@ +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.
+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) * 1
+    - 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) * 1
+        - 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 :top_n;
+
+}}}
+=== Relation
+{{{
+Relation
+}}}