create database demo;
use demo;

drop table if exists MembershipEvent;
drop table if exists MembershipType;
drop table if exists Membership;

create table Membership
(
    id   int primary key,
    name varchar(50)
);

create table MembershipType
(
    id   int primary key,
    name varchar(50) unique not null
);

create table MembershipEvent
(
    id               int primary key,
    membershipId     int      not null,
    membershipTypeId int      not null,
    eventTime        datetime not null,
    foreign key (membershipTypeId) references MembershipType (id),
    foreign key (membershipId) references Membership (id)
);

insert into Membership (id, name)
values (1, 'Awesome Mike'),
       (2, 'Lazy Mary'),
       (3, 'Some Due'),
       (4, 'Old Gold');
insert into MembershipType (id, name)
values (1, 'basic'),
       (2, 'semi'),
       (3, 'super'),
       (4, 'terminated');
insert into MembershipEvent (id, membershipId, membershipTypeId, eventTime)
values (1, 1, 1, '2023-01-01'),
       (2, 1, 2, '2023-03-01'),
       (3, 2, 1, '2023-01-10'),
       (4, 2, 2, '2023-02-20'),
       (5, 2, 3, '2023-03-04'),
       (6, 3, 1, '2023-03-10'),
       (7, 3, 4, '2023-05-01');


select M.id as MembershipId, M.name as memberName, MT.name as currentMembershipTypeName, ME.eventTime as currentMembershipStartTime
from Membership M
         left join MembershipEvent ME on M.id = ME.membershipId
         left join MembershipType MT on ME.membershipTypeId = MT.id

where exists(select *
             from (select InnerME.id, InnerME.membershipTypeId
                   from MembershipEvent InnerME
                   where InnerME.membershipId = ME.membershipId
                     and InnerME.eventTime <= '2023-05-30'
                   order by InnerME.eventTime desc
                   limit 1) TEMP
             where TEMP.membershipTypeId not in (5)
               and TEMP.id = ME.id);
