book suggestion, PostgreSQL

book suggestion

--PostgreSQL 9.6
--'\\' is a delimiter

create table user_bk_his(
    user_id text,
    book_id text);

insert into user_bk_his values ('U1','B1');
insert into user_bk_his values ('U1','B2');

insert into user_bk_his values ('U2','B1');
insert into user_bk_his values ('U2','B2');
insert into user_bk_his values ('U2','B3');

insert into user_bk_his values ('U3','B2');
insert into user_bk_his values ('U3','B3');

insert into user_bk_his values ('U4','B7');

insert into user_bk_his values ('U5','B1');

insert into user_bk_his values ('U6','B1');
insert into user_bk_his values ('U6','B2');
insert into user_bk_his values ('U6','B3');

insert into user_bk_his values ('U7','B1');
insert into user_bk_his values ('U7','B2');
insert into user_bk_his values ('U7','B3');

insert into user_bk_his values ('U8','B1');
insert into user_bk_his values ('U8','B2');

select book_id, count(user_id) from user_bk_his
    where  book_id <> 'B1'
    and user_id in (select distinct user_id from user_bk_his where book_id = 'B1')
    group by book_id having count(user_id) > 1 order by count(user_id) desc limit 1


run \| edit \| history \| help	0