Changes between Version 8 and Version 9 of Complex DB Reports


Ignore:
Timestamp:
02/20/26 20:51:11 (8 days ago)
Author:
231035
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Complex DB Reports

    v8 v9  
    99* Ranks the users from most to least active
    1010=== SQL
    11 {{{
    12 WITH
    13 params AS (
    14   SELECT
    15     CAST(:start_ts AS timestamp) AS start_ts,
    16     CAST(:end_ts   AS timestamp) AS end_ts
    17 ),
    18 
    19 listings_by_user AS (
    20   SELECT
    21     l.owner_id AS user_id,
    22     COUNT(*)   AS listings_created
    23   FROM listings l
    24   JOIN params p ON l.created_at >= p.start_ts AND l.created_at < p.end_ts
    25   GROUP BY l.owner_id
    26 ),
    27 
    28 reviews_by_user AS (
    29   SELECT
    30     r.reviewer_id AS user_id,
    31     COUNT(*)      AS reviews_left,
    32     AVG(r.rating)::numeric(10,2) AS avg_rating_left
    33   FROM reviews r
    34   JOIN params p ON r.created_at >= p.start_ts AND r.created_at < p.end_ts
    35   GROUP BY r.reviewer_id
    36 ),
    37 
    38 appointments_by_user AS (
    39   SELECT
    40     a.responsible_owner_id AS user_id,
    41     COUNT(*) AS appointments_total,
    42     COUNT(*) FILTER (WHERE a.status = 'DONE')     AS appointments_done,
    43     COUNT(*) FILTER (WHERE a.status = 'NO_SHOW')  AS appointments_no_show,
    44     COUNT(*) FILTER (WHERE a.status = 'CANCELLED')AS appointments_cancelled
    45   FROM appointments a
    46   JOIN params p ON a.date_time >= p.start_ts AND a.date_time < p.end_ts
    47   GROUP BY a.responsible_owner_id
    48 ),
    49 
    50 favorites_by_user AS (
    51   SELECT
    52     f.client_id AS user_id,
    53     COUNT(*)    AS favorites_saved_all_time
    54   FROM favorite_listings f
    55   GROUP BY f.client_id
    56 )
    57 
     11{{{WITH params AS (
     12    SELECT
     13        CAST(:start_ts AS timestamp) AS start_ts,
     14        CAST(:end_ts   AS timestamp) AS end_ts
     15),
     16
     17    listings_by_user AS (
     18         SELECT l.owner_id AS user_id, COUNT(*) AS listings_created
     19         FROM listings l
     20                  JOIN params p ON l.created_at >= p.start_ts AND l.created_at < p.end_ts
     21         GROUP BY l.owner_id
     22     ),
     23     reviews_by_user AS (
     24         SELECT r.reviewer_id AS user_id,
     25                COUNT(*) AS reviews_left,
     26                AVG(r.rating)::numeric(10,2) AS avg_rating_left
     27         FROM reviews r
     28                  JOIN params p ON r.created_at >= p.start_ts AND r.created_at < p.end_ts
     29         GROUP BY r.reviewer_id
     30     ),
     31     appointments_by_user AS (
     32         SELECT a.responsible_owner_id AS user_id,
     33                COUNT(*) AS appointments_total,
     34                COUNT(*) FILTER (WHERE a.status = 'DONE')      AS appointments_done,
     35                COUNT(*) FILTER (WHERE a.status = 'NO_SHOW')   AS appointments_no_show,
     36                COUNT(*) FILTER (WHERE a.status = 'CANCELLED') AS appointments_cancelled
     37         FROM appointments a
     38                  JOIN params p ON a.date_time >= p.start_ts AND a.date_time < p.end_ts
     39         GROUP BY a.responsible_owner_id
     40     ),
     41     favorites_by_user AS (
     42         SELECT f.client_id AS user_id, COUNT(*) AS favorites_saved_all_time
     43         FROM favorite_listings f
     44         GROUP BY f.client_id
     45     )
    5846SELECT
    59   u.user_id,
    60   u.username,
    61   u.email,
    62   u.name,
    63   u.surname,
    64 
    65   COALESCE(l.listings_created, 0)          AS listings_created,
    66   COALESCE(rv.reviews_left, 0)             AS reviews_left,
    67   COALESCE(rv.avg_rating_left, 0)       AS avg_rating_left,
    68   COALESCE(ap.appointments_total, 0)       AS appointments_total,
    69   COALESCE(ap.appointments_done, 0)        AS appointments_done,
    70   COALESCE(ap.appointments_no_show, 0)     AS appointments_no_show,
    71   COALESCE(ap.appointments_cancelled, 0)   AS appointments_cancelled,
    72   COALESCE(fv.favorites_saved_all_time, 0) AS favorites_saved_all_time,
    73 
    74   (
    75     COALESCE(l.listings_created, 0) * 5
    76     + COALESCE(rv.reviews_left, 0) * 3
    77     + COALESCE(ap.appointments_done, 0) * 2
    78     + COALESCE(fv.favorites_saved_all_time, 0) * 1
    79     - COALESCE(ap.appointments_no_show, 0) * 2
    80   ) AS activity_score,
    81 
    82   DENSE_RANK() OVER (
    83     ORDER BY
    84       (
     47    u.user_id, u.username, u.email, u.name, u.surname,
     48    COALESCE(l.listings_created, 0) AS listings_created,
     49    COALESCE(rv.reviews_left, 0) AS reviews_left,
     50    COALESCE(rv.avg_rating_left, 0) AS avg_rating_left,
     51    COALESCE(ap.appointments_total, 0) AS appointments_total,
     52    COALESCE(ap.appointments_done, 0) AS appointments_done,
     53    COALESCE(ap.appointments_no_show, 0) AS appointments_no_show,
     54    COALESCE(ap.appointments_cancelled, 0) AS appointments_cancelled,
     55    COALESCE(fv.favorites_saved_all_time, 0) AS favorites_saved_all_time,
     56    (
    8557        COALESCE(l.listings_created, 0) * 5
    86         + COALESCE(rv.reviews_left, 0) * 3
    87         + COALESCE(ap.appointments_done, 0) * 2
    88         + COALESCE(fv.favorites_saved_all_time, 0) * 1
    89         - COALESCE(ap.appointments_no_show, 0) * 2
    90       ) DESC,
    91       COALESCE(l.listings_created, 0) DESC,
    92       COALESCE(rv.reviews_left, 0) DESC
    93   ) AS activity_rank
    94 
     58            + COALESCE(rv.reviews_left, 0) * 3
     59            + COALESCE(ap.appointments_done, 0) * 2
     60            + COALESCE(fv.favorites_saved_all_time, 0)
     61            - COALESCE(ap.appointments_no_show, 0) * 2
     62        ) AS activity_score,
     63    DENSE_RANK() OVER (
     64        ORDER BY
     65            (
     66                COALESCE(l.listings_created, 0) * 5
     67                    + COALESCE(rv.reviews_left, 0) * 3
     68                    + COALESCE(ap.appointments_done, 0) * 2
     69                    + COALESCE(fv.favorites_saved_all_time, 0)
     70                    - COALESCE(ap.appointments_no_show, 0) * 2
     71                ) DESC,
     72            COALESCE(l.listings_created, 0) DESC,
     73            COALESCE(rv.reviews_left, 0) DESC
     74        ) AS activity_rank
    9575FROM users u
    96 LEFT JOIN listings_by_user     l  ON l.user_id  = u.user_id
    97 LEFT JOIN reviews_by_user      rv ON rv.user_id = u.user_id
    98 LEFT JOIN appointments_by_user ap ON ap.user_id = u.user_id
    99 LEFT JOIN favorites_by_user    fv ON fv.user_id = u.user_id
    100 
    101 WHERE
    102   COALESCE(l.listings_created, 0)
    103   + COALESCE(rv.reviews_left, 0)
    104   + COALESCE(ap.appointments_total, 0)
    105   + COALESCE(fv.favorites_saved_all_time, 0) > 0
    106 
    107 ORDER BY activity_rank;
     76         LEFT JOIN listings_by_user l ON l.user_id = u.user_id
     77         LEFT JOIN reviews_by_user rv ON rv.user_id = u.user_id
     78         LEFT JOIN appointments_by_user ap ON ap.user_id = u.user_id
     79         LEFT JOIN favorites_by_user fv ON fv.user_id = u.user_id
     80WHERE COALESCE(l.listings_created, 0)
     81          + COALESCE(rv.reviews_left, 0)
     82          + COALESCE(ap.appointments_total, 0)
     83          + COALESCE(fv.favorites_saved_all_time, 0) > 0
     84ORDER BY activity_rank
    10885LIMIT 10;
    10986}}}