MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table events (
    id   int auto_increment primary key,
    name varchar(100) null
);

create table users (
    name varchar(100) null,
    id   int auto_increment primary key
);

create table availability (
    answer  text null,
    event_id int  null,
    user_id int  not null,
    constraint availability_events_id_fk
        foreign key (event_id) references events (id),
    constraint availability_users_id_fk
        foreign key (user_id) references users (id)
);

INSERT INTO events (id, name) VALUES (1, 'Event 1');
INSERT INTO events (id, name) VALUES (2, 'Event 2');

INSERT INTO users (name, id) VALUES ('John', 1);
INSERT INTO users (name, id) VALUES ('Francis', 2);
INSERT INTO users (name, id) VALUES ('Peter', 3);
INSERT INTO users (name, id) VALUES ('Mike', 4);

INSERT INTO availability (answer, event_id, user_id) VALUES ('yes', 1, 1);
INSERT INTO availability (answer, event_id, user_id) VALUES ('yes', 2, 1);
INSERT INTO availability (answer, event_id, user_id) VALUES ('no', 1, 2);
INSERT INTO availability (answer, event_id, user_id) VALUES ('maybe', 2, 4);

user_id	cnt
1	0
2	1
3	2
4	1