Untitled

CREATE DATABASE  eliq_assignment;
USE  eliq_assignment;

CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(255),
email VARCHAR(255),
signup_date DATE,
PRIMARY KEY (id),
INDEX index_email (email),
INDEX index_username (username)
);

INSERT INTO users (username, email, signup_date) 
VALUES  ('user2', 'user1@', '2024-01-26'),
		('user2', 'user2@', '2024-01-27'),
		('user1', 'user3@', '2024-01-28'),
		('user1', 'user4@', '2024-01-29'),
		('user3', 'user5@', '2024-01-27'),
		('user5', 'user6@', '2024-01-20'),
        ('user6', 'user7@', '2024-01-28'),
        ('user6', 'user8@', '2024-01-30');

#A
SELECT username, MAX(signup_date) AS most_recent_signup_date
FROM users
GROUP BY username
HAVING COUNT(username) > 1;

#B
SELECT DISTINCT email FROM users
WHERE username IN (
    SELECT username
    FROM users
    GROUP BY username
    HAVING COUNT(username) > 1
)
AND (username, signup_date) IN (
    SELECT username, MAX(signup_date) AS most_recent_signup_date
    FROM users
    GROUP BY username
    HAVING COUNT(username) > 1
);

#D
ALTER TABLE users 
ADD CONSTRAINT unique_username_constraint UNIQUE (username);