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');


 id | contest_id | code
----+------------+------
  2 |          2 | A
  6 |          4 | B
(2 rows)



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');


 id | contest_id | code
----+------------+------
  1 |          3 | A
  2 |          3 | B
  3 |          1 | A
(3 rows)