/* testing recommend when user, charlie */

delete from likes;
delete from users;

drop table if exists movies cascade;
\i /usr0/stu415_s13/etaralova415/loading.sql

insert into users (login_name,email,passwd) values ('anca', 'anca@cmu.edu', 'ancapass');
insert into users (login_name,email,passwd) values ('bob', 'bob@cmu.edu', 'bobpass');
insert into users (login_name,email,passwd) values ('charlie', 'charlie@cmu.edu', 'charliepass');

insert into users (login_name,email,passwd) values ('david', 'david@cmu.edu', 'davidpass');
insert into users (login_name,email,passwd) values ('ekaterina', 'ekaterina@cmu.edu', 'epass');

insert into likes (login_name,mtitle) values ('anca', 'Armageddon');
insert into likes (login_name,mtitle) values ('anca', 'Batman');
insert into likes (login_name,mtitle) values ('bob', 'Armageddon');
insert into likes (login_name,mtitle) values ('bob', 'TRON');
insert into likes (login_name,mtitle) values ('bob', 'Dracula');
insert into likes (login_name,mtitle) values ('bob', 'Batman');
insert into likes (login_name,mtitle) values ('charlie', 'Armageddon');
insert into likes (login_name,mtitle) values ('charlie', 'TRON');
insert into likes (login_name,mtitle) values ('charlie', 'Bolt');
insert into likes (login_name,mtitle) values ('charlie', 'Casino');


insert into likes (login_name,mtitle) values ('david', 'Rambo');
insert into likes (login_name,mtitle) values ('david', 'Armageddon');
insert into likes (login_name,mtitle) values ('david', 'Dracula');
insert into likes (login_name,mtitle) values ('david', 'Batman'); 

insert into likes (login_name,mtitle) values ('ekaterina', 'Batman'); 
insert into likes (login_name,mtitle) values ('ekaterina', 'Dracula'); 
insert into likes (login_name,mtitle) values ('ekaterina', 'Rambo'); 


/* version 1:*/
/*
drop view if exists num_likes cascade;
create view num_likes as
       select login_name,count(login_name) as likes_N
       from  likes group by login_name;
select login_name, likes_N from num_likes where likes_n = 
       (select max(likes_n) from num_likes) order by login_name ASC limit 10;
*/
/*or version 2: */

/*
drop view if exists num_likes cascade;
create view num_likes as
       select login_name,count(login_name) as likes_N
       from  likes group by login_name order by likes_N desc;
select login_name,likes_N from num_likes order by likes_N desc, login_name ASC limit 10;
*/

