rating.sql

create table users(
    id serial primary key,
    name text not null
);

insert into users
select n, format('User %s', n)
from generate_series(1, 100000) as seq(n);

create table user_points(
    user_id integer not null,
    points integer not null default 0,
    reason text
);

insert into user_points(user_id, points, reason)
select
    n / 10 + 1,
    (random() * n)::int % 100,
    format('iteration %s', n)
from
    generate_series(1, 1000000) as seq(n);


create materialized view mv_user_rating as
select
    user_id, sum(points) as total_points
from user_points
group by user_id;


refresh materialized view mv_user_rating;

create index idx_mv_total_points on mv_user_rating
    using btree (total_points desc);

analyze mv_user_rating;

select
    u.id as user_id,
    u.name as user_name,
    mv.total_points as total_points
from
    mv_user_rating mv,
    users u
where
    mv.user_id = u.id
order by
    mv.total_points desc
limit
    100;


┌─────────┬────────────┬──────────────┐
│ user_id │ user_name  │ total_points │
├─────────┼────────────┼──────────────┤
│      96 │ User 96    │         1225 │
│      45 │ User 45    │         1185 │
│      85 │ User 85    │         1169 │
│      10 │ User 10    │         1140 │
│      33 │ User 33    │         1138 │
│      80 │ User 80    │         1135 │
│      48 │ User 48    │         1133 │
│      53 │ User 53    │         1121 │
│      11 │ User 11    │         1119 │
│      91 │ User 91    │         1099 │
│      79 │ User 79    │         1096 │
│      56 │ User 56    │         1090 │
│      72 │ User 72    │         1089 │
│      31 │ User 31    │         1088 │
│      40 │ User 40    │         1072 │
│      97 │ User 97    │         1070 │
│      65 │ User 65    │         1068 │
│      42 │ User 42    │         1058 │
│      43 │ User 43    │         1057 │
│      78 │ User 78    │         1054 │
│      63 │ User 63    │         1053 │
│      54 │ User 54    │         1049 │
│      93 │ User 93    │         1031 │
│      29 │ User 29    │         1029 │
│      51 │ User 51    │         1028 │
│     100 │ User 100   │         1027 │
│      98 │ User 98    │         1021 │
│      69 │ User 69    │         1014 │
│      28 │ User 28    │         1003 │
│      67 │ User 67    │          994 │
│      60 │ User 60    │          990 │
│      21 │ User 21    │          987 │
│      58 │ User 58    │          986 │
│      26 │ User 26    │          984 │


explain analyze
select
    u.id as user_id,
    u.name as user_name,
    mv.total_points as total_points
from
    mv_user_rating mv,
    users u
where
    mv.user_id = u.id
order by
    mv.total_points desc
limit
    100;


┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                          QUERY PLAN                                                                          │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit  (cost=0.58..38.88 rows=100 width=22) (actual time=0.029..0.335 rows=100 loops=1)                                                                      │
│   ->  Nested Loop  (cost=0.58..38292.36 rows=100000 width=22) (actual time=0.027..0.326 rows=100 loops=1)                                                    │
│         ->  Index Scan using idx_mv_total_points on mv_user_rating mv  (cost=0.29..4024.10 rows=100001 width=12) (actual time=0.016..0.071 rows=100 loops=1) │
│         ->  Index Scan using users_pkey on users u  (cost=0.29..0.34 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=100)                            │
│               Index Cond: (id = mv.user_id)                                                                                                                  │
│ Planning Time: 0.286 ms                                                                                                                                      │
│ Execution Time: 0.368 ms                                                                                                                                     │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

refresh materialized view concurrently mv_user_rating;

ERROR:  cannot refresh materialized view "public.mv_user_rating" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

create unique index idx_uq_mv_user_rating_user_id on mv_user_rating(user_id);

refresh materialized view concurrently mv_user_rating;

SELECT cron.schedule(
    'cron_job_refresh_user_rating',
    'refresh materialized view concurrently mv_user_rating;'
);

----------

create table user_total_points(
    user_id integer primary key,
    total_points integer not null default 0
);

truncate user_points;

begin;
insert into user_points(user_id, points) values (999, 100);
insert into user_total_points(user_id, total_points) values (999, 100);
commit;

ERROR:  duplicate key value violates unique constraint "user_total_points_pkey"
DETAIL:  Key (user_id)=(999) already exists.

begin;
insert into user_points(user_id, points) values (999, 100);
insert into user_total_points(user_id, total_points) values (999, 100)
on conflict(user_id) do update
    set total_points = user_total_points.total_points + excluded.total_points;
commit;


test=# select * from user_points;
┌─────────┬────────┬────────┐
│ user_id │ points │ reason │
├─────────┼────────┼────────┤
│     999 │    100 │ <null> │
│     999 │    100 │ <null> │
└─────────┴────────┴────────┘

test=# select * from user_total_points;
┌─────────┬──────────────┐
│ user_id │ total_points │
├─────────┼──────────────┤
│     999 │          200 │
└─────────┴──────────────┘

with
step_1 as (
    insert into user_points(user_id, points)
    values (999, 100)
)
insert into user_total_points(user_id, total_points)
    values (999, 100)
on conflict(user_id) do update
    set total_points = user_total_points.total_points + excluded.total_points;


┌─────────┬────────┬────────┐
│ user_id │ points │ reason │
├─────────┼────────┼────────┤
│     999 │    100 │ <null> │
│     999 │    100 │ <null> │
│     999 │    100 │ <null> │
│     999 │    100 │ <null> │
│     999 │    100 │ <null> │
└─────────┴────────┴────────┘

select * from user_total_points;

┌─────────┬──────────────┐
│ user_id │ total_points │
├─────────┼──────────────┤
│     999 │          500 │
└─────────┴──────────────┘

create or replace function func_add_points(user_id integer, points integer)
returns integer
as $$
with step_1 as (
    insert into user_points(user_id, points)
    values (user_id, points)
)
insert into user_total_points(user_id, total_points)
    values (user_id, points)
on conflict(user_id) do update
    set total_points = user_total_points.total_points + excluded.total_points
returning
    total_points
$$
language sql strict parallel safe;

select
    n as user_id,
    func_add_points(n, n * 5)
    as total_points
from
    generate_series(25, 50) seq(n);

┌─────────┬──────────────┐
│ user_id │ total_points │
├─────────┼──────────────┤
│      25 │          125 │
│      26 │          130 │
│      27 │          135 │
│      28 │          140 │
│      29 │          145 │
│      30 │          150 │
│      31 │          155 │
│      32 │          160 │
│      33 │          165 │
│      34 │          170 │
│      35 │          175 │
│      36 │          180 │
│      37 │          185 │
│      38 │          190 │
│      39 │          195 │
│      40 │          200 │
│      41 │          205 │
│      42 │          210 │
│      43 │          215 │
│      44 │          220 │
│      45 │          225 │
│      46 │          230 │
│      47 │          235 │
│      48 │          240 │
│      49 │          245 │
│      50 │          250 │
└─────────┴──────────────┘


select
    user_id,
    points,
    func_add_points(user_id, points) as total
from (values
    (1003, 3),
    (1003, 2),
    (1003, 1),
    (1003, 5),
    (1003, 7)) as vals(user_id, points);

┌─────────┬────────┬────────┐
│ user_id │ points │ total  │
├─────────┼────────┼────────┤
│    1003 │      3 │      3 │
│    1003 │      2 │      5 │
│    1003 │      1 │      6 │
│    1003 │      5 │     11 │
│    1003 │      7 │     18 │
└─────────┴────────┴────────┘

create index idx_total_points on user_total_points
    using btree (total_points desc);

analyze user_total_points;

select
    u.id as user_id,
    u.name as user_name,
    total.total_points as total_points
from
    user_total_points total,
    users u
where
    total.user_id = u.id
order by
    total.total_points desc
limit
    100;

┌─────────┬───────────┬──────────────┐
│ user_id │ user_name │ total_points │
├─────────┼───────────┼──────────────┤
│     999 │ User 999  │         1500 │
│      50 │ User 50   │          250 │
│      49 │ User 49   │          245 │
│      48 │ User 48   │          240 │
│      47 │ User 47   │          235 │
│      46 │ User 46   │          230 │
│      45 │ User 45   │          225 │
│      44 │ User 44   │          220 │
│      43 │ User 43   │          215 │
│      42 │ User 42   │          210 │
│      41 │ User 41   │          205 │
│      40 │ User 40   │          200 │
│      39 │ User 39   │          195 │
│      38 │ User 38   │          190 │
│      37 │ User 37   │          185 │
│      36 │ User 36   │          180 │
│      35 │ User 35   │          175 │
│      34 │ User 34   │          170 │
│      33 │ User 33   │          165 │
│      32 │ User 32   │          160 │
│      31 │ User 31   │          155 │
│      30 │ User 30   │          150 │
│      29 │ User 29   │          145 │
│      28 │ User 28   │          140 │
│      27 │ User 27   │          135 │
│      26 │ User 26   │          130 │
│      25 │ User 25   │          125 │
└─────────┴───────────┴──────────────┘

alter table user_total_points
add column rating integer not null default 0;

alter table user_total_points
drop column rating;


select
    *,
    dense_rank() over w as rank
from
    user_total_points
window
    w as (order by total_points desc)
order by
    total_points desc
;


┌─────────┬──────────────┬──────┐
│ user_id │ total_points │ rank │
├─────────┼──────────────┼──────┤
│     999 │         1500 │    1 │
│      50 │          250 │    2 │
│      49 │          245 │    3 │
│      48 │          240 │    4 │
│      47 │          235 │    5 │
│      46 │          230 │    6 │



select func_add_points(49, 5);

┌─────────────────┐
│ func_add_points │
├─────────────────┤
│             250 │
└─────────────────┘


┌─────────┬──────────────┬──────┐
│ user_id │ total_points │ rank │
├─────────┼──────────────┼──────┤
│     999 │         1500 │    1 │
│      49 │          250 │    2 │
│      50 │          250 │    2 │
│      48 │          240 │    3 │
│      47 │          235 │    4 │
│      46 │          230 │    5 │


select
    u.id as user_id,
    u.name as user_name,
    total.total_points as total_points,
    dense_rank() over w as rank
from
    user_total_points total,
    users u
where
    total.user_id = u.id
window
    w as (order by total_points desc)
order by
    total_points desc
limit
    100
;


┌─────────┬───────────┬──────────────┬──────┐
│ user_id │ user_name │ total_points │ rank │
├─────────┼───────────┼──────────────┼──────┤
│     999 │ User 999  │         1500 │    1 │
│      50 │ User 50   │          250 │    2 │
│      49 │ User 49   │          250 │    2 │
│      48 │ User 48   │          240 │    3 │
│      47 │ User 47   │          235 │    4 │
│      46 │ User 46   │          230 │    5 │
│      45 │ User 45   │          225 │    6 │
│      44 │ User 44   │          220 │    7 │
│      43 │ User 43   │          215 │    8 │
│      42 │ User 42   │          210 │    9 │
│      41 │ User 41   │          205 │   10 │
│      40 │ User 40   │          200 │   11 │
│      39 │ User 39   │          195 │   12 │
│      38 │ User 38   │          190 │   13 │
│      37 │ User 37   │          185 │   14 │
│      36 │ User 36   │          180 │   15 │
│      35 │ User 35   │          175 │   16 │
│      34 │ User 34   │          170 │   17 │