CREATE TABLE Contacts (
    ContactID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE
);

CREATE TABLE Addresses (
    AddressID INT AUTO_INCREMENT PRIMARY KEY,
    ContactID INT,
    AddressLine1 VARCHAR(100),
    City VARCHAR(50),
    State VARCHAR(50),
    ZipCode VARCHAR(10),
    Country VARCHAR(50),
    FOREIGN KEY (ContactID) REFERENCES Contacts(ContactID)
);

CREATE TABLE PhoneNumbers (
    PhoneID INT AUTO_INCREMENT PRIMARY KEY,
    ContactID INT,
    PhoneNumber VARCHAR(15),
    Type VARCHAR(20),
    FOREIGN KEY (ContactID) REFERENCES Contacts(ContactID)
);

CREATE TABLE EmailAddresses (
    EmailID INT AUTO_INCREMENT PRIMARY KEY,
    ContactID INT,
    Email VARCHAR(100),
    Type VARCHAR(20),
    FOREIGN KEY (ContactID) REFERENCES Contacts(ContactID)
);

CREATE TABLE ContactsGroup(
    GroupID INT AUTO_INCREMENT PRIMARY KEY,
    GroupName VARCHAR(50)
);

CREATE TABLE ContactsGroupDetails (
    ContactID INT,
    GroupID INT,
    PRIMARY KEY (ContactID, GroupID),
    FOREIGN KEY (ContactID) REFERENCES Contacts(ContactID),
    FOREIGN KEY (GroupID) REFERENCES ContactsGroup(GroupID)
);

INSERT INTO Contacts (ContactID, FirstName, LastName, BirthDate) VALUES
    (1, 'John', 'Doe', '1985-04-12'),
    (2, 'Jane', 'Smith', '1990-11-23'),
    (3, 'Emily', 'Johnson', '1975-06-15'),
    (4, 'Michael', 'Brown', '1980-01-10'),
    (5, 'Sarah', 'Davis', '1995-09-30'),
    (6, 'David', 'Wilson', '1988-12-25'),
    (7, 'Laura', 'Taylor', '1970-03-05'),
    (8, 'James', 'Moore', '1983-07-19'),
    (9, 'Olivia', 'Clark', '1992-10-10'),
    (10, 'Sophia', 'Lee', '2000-08-08');

INSERT INTO Addresses (AddressID, ContactID, AddressLine1, City, State, ZipCode, Country) VALUES
    (1, 1, '123 Elm St', 'New York', 'NY', 10001, 'USA'),
    (2, 2, '456 Oak Ave', 'New York', 'NY', 10001, 'USA'),
    (3, 3, '789 Pine Rd', 'Chicago', 'IL', 60601, 'USA'),
    (4, 4, '321 Maple Blvd', 'Houston', 'TX', 77001, 'USA'),
    (5, 5, '654 Cedar Lane', 'Phoenix', 'AZ', 85001, 'USA'),
    (6, 6, '987 Birch Way', 'Houston', 'TX', 77001, 'USA'),
    (7, 7, '147 Spruce Dr', 'San Antonio', 'TX', 78201, 'USA'),
    (8, 8, '258 Redwood Ct', 'Dallas', 'TX', 75201, 'USA'),
    (9, 9, '369 Willow Pl', 'Los Angeles', 'CA', 90001, 'USA'),
    (10, 10, '753 Poplar St', 'Los Angeles', 'CA', 90001, 'USA');

INSERT INTO PhoneNumbers (PhoneID,ContactID,PhoneNumber,Type) VALUES
    (1,1,'555-1234','Mobile'),
    (2,1,'555-5678','Work'),
    (3,2,'555-8765','Home'),
    (4,3,'555-4321','Mobile'),
    (5,4,'555-9101','Work'),
    (6,5,'555-1122','Mobile'),
    (7,6,'555-3344','Home'),
    (8,7,'555-5566','Mobile'),
    (9,8,'555-7788','Work'),
    (10,9,'555-9900','Mobile');

INSERT INTO EmailAddresses (EmailID,ContactID,Email,Type) VALUES
    (1,1,'john.doe@gmail.com','Personal'),
    (2,1,'john.doe@gmail.com','Work'),
    (3,2,'jane.smith@yahoo.com','Personal'),
    (4,3,'emily.johnson@hotmail.com','Personal'),
    (5,4,'michael.brown@outlook.com','Work'),
    (6,5,'sarah.davis@gmail.com','Personal'),
    (7,6,'david.wilson@yahoo.com','Personal'),
    (8,7,'laura.taylor@hotmail.com','Personal'),
    (9,8,'james.moore@outlook.com','Work'),
    (10,9,'olivia.clark@gmail.com','Personal'),
    (11,10,'sophia.lee@yahoo.com','Personal');

INSERT INTO ContactsGroup (GroupID,GroupName) VALUES
    (1,'Family'),
    (2,'Friends'),
    (3,'Work'),
    (4,'Neighbors'),
    (5,'School');

INSERT INTO ContactsGroupDetails (ContactID,GroupID) VALUES
    (1,1),
    (3,1),
    (5,1),
    (9,1),
    (2,2),
    (4,2),
    (8,2),
    (1,3),
    (2,3),
    (4,3),
    (6,3),
    (10,3),
    (3,4),
    (7,4),
    (5,5),
    (7,5);

-- SELECT FirstName, LastName, BirthDate
-- FROM Contacts
-- WHERE DATE(BirthDate) > DATE('1990-01-01')

-- SELECT PhoneNumbers.PhoneNumber, PhoneNumbers.ContactID
-- FROM PhoneNumbers
-- WHERE Type='Mobile'

-- SELECT ContactsGroup.GroupName, COUNT(ContactsGroupDetails.ContactID) AS ContactCount
-- FROM ContactsGroup
-- JOIN ContactsGroupDetails ON ContactsGroupDetails.GroupID=ContactsGroup.GroupID
-- GROUP BY ContactsGroup.GroupName

SELECT Contacts.FirstName, Contacts.LastName, COUNT(ContactsGroupDetails.ContactID) AS GroupCount
FROM Contacts
JOIN ContactsGroupDetails ON ContactsGroupDetails.ContactID=Contacts.ContactID
JOIN ContactsGroup ON ContactsGroup.GroupID=ContactsGroupDetails.GroupID
GROUP BY Contacts.ContactID,Contacts.FirstName,Contacts.LastName

ORDER BY GroupCount DESC