SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
create table users ( id bigint primary key, name varchar not null ); create table contests ( id bigint primary key, name varchar not null ); create table problems ( id bigint primary key, contest_id bigint, code varchar not null, constraint fk_problems_contest_id foreign key (contest_id) references contests (id) ); create unique index on problems (contest_id, code); create table submissions ( id bigint primary key, user_id bigint, problem_id bigint, success boolean not null, submitted_at timestamp not null, constraint fk_submissions_user_id foreign key (user_id) references users (id), constraint fk_submissions_problem_id foreign key (problem_id) references problems (id) ); insert into users values (1, 'Olivia'), (2, 'Henry'), (3, 'Lucas'), (4, 'John'), (5, 'Charlotte'), (6, 'Henry'); insert into contests values (3, 'Main'), (1, 'Practice'); insert into problems values (1, 3, 'A'), (2, 3, 'B'), (3, 1, 'A'); insert into submissions values (10, 3, 2, false, '2023-02-05 11:05:12'), (20, 3, 2, true, '2023-02-05 11:07:49'), (30, 3, 2, true, '2023-02-05 11:07:49'), (40, 3, 1, false, '2023-02-05 11:01:32'), (50, 3, 1, false, '2023-02-05 11:11:46'), (60, 3, 1, false, '2023-02-05 11:27:05'), (70, 6, 2, false, '2023-02-05 11:04:00'), (80, 6, 2, true, '2023-02-05 11:05:00'), (90, 6, 2, false, '2023-02-05 11:06:00'), (100, 6, 2, true, '2023-02-05 11:07:00'), (110, 6, 1, false, '2023-02-05 11:08:00'), (120, 6, 1, true, '2023-02-05 11:09:00'), (130, 2, 2, false, '2023-02-05 11:00:01'), (150, 5, 1, false, '2023-02-05 11:07:48'), (160, 5, 1, true, '2023-02-05 11:07:49'), (170, 5, 1, true, '2023-02-05 11:07:50'), (180, 1, 3, false, '2023-02-04 15:00:01'), (190, 1, 3, true, '2023-02-04 15:00:01'), (200, 5, 3, true, '2023-02-04 15:00:01'), (210, 5, 3, false, '2023-02-04 15:00:01'), (220, 2, 3, false, '2023-02-04 15:00:01'), (230, 6, 3, false, '2023-02-04 15:00:01'), (240, 6, 3, false, '2023-02-04 15:00:01'); with t1 as (select distinct s.user_id, c.id from contests c join problems p on c.id = p.contest_id join submissions s on p.id = s.problem_id where s.success = 't' group by 1, 2 order by 1), t2 as (select distinct count(*), user_id from t1 group by user_id order by count(*) desc) with t3 as (select distinct s.user_id, c.id from contests c join problems p on c.id = p.contest_id join submissions s on p.id = s.problem_id group by 1, 2 order by 1) with t4 as (select distinct count(*), user_id from t3 group by 2 order by count(*) desc) select distinct u.id, u.name, case when (select t2.count from t2 where u.id = t2.user_id) is not null then (select t2.count from t2 where u.id = t2.user_id) else 0 end as solved_at_least_one_contest_count, case when (select t4.count from t4 where u.id = t4.user_id) is not null then (select t4.count from t4 where u.id = t4.user_id) else 0 end as take_part_contest_count from users u group by 1, 2 order by 3 desc, 4 desc
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear