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, 'Marie Curie'), (2, 'Stephen Hawking'), (3, 'Ada Lovelace'), (4, 'Albert Einstein'), (5, 'Archimedes'); insert into contests values (1, 'Sandbox-Juniors'), (2, 'Sandbox-Seniors'), (3, 'Contest-Juniors'), (4, 'Contest-Seniors'); insert into problems values (1, 1, 'A'), (2, 2, 'A'), (3, 3, 'A'), (4, 3, 'B'), (5, 4, 'A'), (6, 4, 'B'); insert into submissions values (1, 2, 2, false, '2023-02-05 11:01:00'), (2, 2, 2, true, '2023-02-05 11:02:00'), (3, 2, 6, true, '2023-02-05 11:03:01'), (4, 2, 1, true, '2023-02-05 11:04:00'), (5, 2, 1, true, '2023-02-05 11:05:00'), (6, 3, 6, true, '2023-02-05 11:06:00'), (17, 1, 6, true, '2023-02-05 11:03:00'), (8, 1, 2, true, '2023-02-05 11:08:00'), (9, 1, 1, false, '2023-02-05 11:09:00'), (10, 3, 1, false, '2023-02-05 11:10:00'), (11, 5, 5, false, '2023-02-05 11:11:00'), (13, 2, 6, true, '2023-02-05 11:03:00'), (14, 3, 6, false, '2023-02-05 11:05:59'), (15, 1, 6, true, '2023-02-05 11:04:00'); select users.id as id, users.name as name, ifnull(max(tmp.solved_at_least_one_contest_count), 0) as solved_at_least_one_contest_count, count(distinct contests.id) as take_part_contest_count from users left join submissions on users.id = submissions.user_id left join problems on problems.id = submissions.problem_id left join contests on contests.id = problems.contest_id left join (select users.id as id, users.name as name, count(distinct contests.id) as solved_at_least_one_contest_count from users left join submissions on users.id = submissions.user_id left join problems on problems.id = submissions.problem_id left join contests on contests.id = problems.contest_id where submissions.success group by users.id, users.name) as tmp on users.id = tmp.id group by users.id, users.name order by solved_at_least_one_contest_count DESC, take_part_contest_count DESC /* select users.id as id, users.name as name, SUM ( CASE submissions.success WHEN true THEN 1 ELSE 0 END) as solved_at_least_one_contest_count, count(submissions.id) as take_part_contest_count, problems.id as problem_id, contests.id as contest_id, problems.code as code, submissions.success as success from users inner join submissions on users.id = submissions.user_id inner join problems on problems.id = submissions.problem_id inner join contests on contests.id = problems.contest_id group by users.id, users.name, problems.id, contests.id, problems.code, submissions.success */
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear