Untitled
unknown
mysql
a year ago
1.1 kB
3
Indexable
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);
Editor is loading...
Leave a Comment