NN Group test case

mysql.sql

-- Table 1
create table ip_events
(
    ip_address varchar(45)  not null,
    login_time timestamp    not null,
    event_type varchar(16)  not null,
    username   varchar(255) not null
)
    collate = utf8mb4_unicode_ci;

create index ip_address_idx
    on ip_events (ip_address);

create index login_time_idx
    on ip_events (login_time);

create index username_idx
    on ip_events (username);

-- Table 2
create table ip_event_username_counts
(
    ip_address      varchar(45) not null,
    usernames_count int         not null,
    constraint ip_address
        unique (ip_address)
);

create index ip_address_idx
    on ip_event_username_counts (ip_address);

create index username_counts_idx
    on ip_event_username_counts (usernames_count);


-- SQL examples:
select distinct ip_address from ip_events
where login_time between str_to_date('2012-01-23', '%Y-%m-%d %h:%i%p') and str_to_date('2012-01-24','%Y-%m-%d %h:%i%p');

select distinct ip_address from ip_events
where username = 'hermiston.bud';

select ip_address from ip_event_username_counts ieuc where usernames_count > 3;

-- Insert is gonna look like this:
start transaction;

    insert into ip_events (ip_address, login_time, event_type, username) values (?, ?, ?, ?);

    insert into ip_event_username_counts (ip_address, usernames_count)
    select ip_address, count(distinct username) from ip_events ie
        where ie.ip_address = ?
    on duplicate key update usernames_count = (select count(distinct username) from ip_events i where i.ip_address = ?);

commit;