public
Last active

With SQL, how to achieve the following goal

  • Download Gist
gistfile1.txt
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
CREATE TABLE `users` (
`id` int(11) DEFAULT NULL,
`email` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`born_at` varchar(255) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
INSERT INTO `users` (`id`,`email`,`born_at`) VALUES ('1','same@example.com','1980-01-01');
INSERT INTO `users` (`id`,`email`,`born_at`) VALUES ('2','2@example.com','1980-01-02');
INSERT INTO `users` (`id`,`email`,`born_at`) VALUES ('3','3@example.com','1980-01-01');
INSERT INTO `users` (`id`,`email`,`born_at`) VALUES ('4','4@example.com','1980-01-03');
INSERT INTO `users` (`id`,`email`,`born_at`) VALUES ('5','same@example.com','1980-01-02');
INSERT INTO `users` (`id`,`email`,`born_at`) VALUES ('6','same@example.com','1980-01-02');
 
 
 
# Desired format
# count_with_similar_born_at_but_different_email born_at
2 1980-01-01
2 1980-01-02
1 1980-01-03

Solution : SELECT born_at, COUNT(DISTINCT(email)) FROM users GROUP BY born_at

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.