members teams - left join, MySQL

Language: Layout:

drop table if exists membership_stack;
drop table if exists members;
CREATE TABLE members (
  id int unsigned auto_increment,
  first_name varchar(255) not null,
  PRIMARY KEY(id));
INSERT INTO members (id, first_name)
     VALUES (1, 'Tyler'),
            (2, 'Marissa'),
            (3, 'Alex'),
            (4, 'Parker');
CREATE TABLE membership_stack (
  id int unsigned auto_increment,
  member_id int unsigned not null,
  sequence int unsigned not null,
  team varchar(255) not null,
  `status` varchar(255) not null,
  PRIMARY KEY(id),
  FOREIGN KEY(member_id) REFERENCES members(id));
-- Algorithm to determine correct team:
-- 1. Only consider rows with the highest sequence number
-- 2. Order statuses and pick the first one found:
--    (active, completed, cancelled, abandoned)
INSERT INTO membership_stack (member_id, sequence, team, status)
     VALUES (1, 1, 'instinct', 'active'),
            (1, 1, 'valor', 'abandoned'),
            (2, 1, 'valor', 'active'),
            (2, 2, 'mystic', 'abandoned'),
            (2, 2, 'valor', 'completed'),
            (3, 1, 'instinct', 'completed'),
            (3, 2, 'valor', 'active');

SELECT m.id, m.first_name, ms.sequence, ms.team, ms.status
FROM membership_stack AS ms
JOIN (
    SELECT member_id, MAX(sequence) AS sequence
    FROM membership_stack
    GROUP BY member_id
) AS t1 
    ON  ms.member_id = t1.member_id
    AND ms.sequence = t1.sequence
JOIN members AS m
    ON ms.member_id = m.id
ORDER BY m.id, FIELD(ms.status, 'active', 'completed', 'cancelled', 'abandoned');

/*
| id | first_name | sequence |     team |    status |
|----|------------|----------|----------|-----------|
|  1 |      Tyler |        1 | instinct |    active |
|  2 |    Marissa |        2 |    valor | completed |
|  3 |       Alex |        2 |    valor |    active |
|  4 |     Parker |     NULL |     NULL |      NULL |
*/

SELECT m.id, m.first_name, ms.sequence, ms.team, ms.status
FROM members AS m
LEFT JOIN membership_stack AS ms 
    ON  ms.member_id = m.id
    AND ms.id = (
        SELECT ms1.id
        FROM membership_stack AS ms1
        WHERE ms1.member_id = ms.member_id
        ORDER BY ms1.sequence desc, 
                 FIELD(ms1.status, 'active', 'completed', 'cancelled', 'abandoned'),
                 ms1.id asc
        LIMIT 1
    )
ORDER BY m.id;


absolute service time: 0,39 sec