Query duplicate user accounts from DB?

Question

How can I check for duplicate user accounts using SQL? The back office is not robust enough to show me what I want and I shouldn't really need to get a plugin to do this.

I just want to check to see if there are accounts with the same email address.

Magento 1.4.0.1 (Yes I know it's old but I cannot upgrade due to contractor making core mods)

Answer 1

Magento save customer data at table customer_entity in DB and here magento is save customer email also.

customer_entity table mail table for Mage::getModel('customer/customer') models.

If you want to check mail address already exit in system then try below Query

SELECT , COUNT() c FROM customer_entity GROUP BY email HAVING c > 1

Answer 2

SELECT
  count(entity_id) counter,
  email,
  store_id
FROM customer_entity
GROUP BY email, store_id
HAVING counter > 1;