SQL code to report customer by country

I mentioned this earlier that I was looking for a way to directly connect user e-mail address to specific countries. Here's the SQL code I came up with:

SELECT        Customer.Id, Customer.Email, Address.CountryId, Country.Name
FROM            Customer INNER JOIN
                         CustomerAddresses ON CustomerAddresses.Customer_Id = Customer.Id INNER JOIN
                         Address ON Address.Id = CustomerAddresses.Address_Id INNER JOIN
                         Country ON Country.Id = Address.CountryId
WHERE        (Customer.Email IS NOT NULL)
ORDER BY Customer.Id

If you want to limit the report to one country, you need the country code (Example: US = 1) and add it immediately after (Customer.Email IS NOT NULL) thusly:

WHERE        (Customer.Email IS NOT NULL) and (address.CountryId = '1')

It's a quick and dirty way to scroll through your e-mail addresses to pick out obvious "troll" accounts like this one: "[email protected]"

I don't think your query works well for "trolls".  
- Address info entered in the registration page does not create an "Address" record.  (It creates GenericAttribute records.  Although I think some of it might 'copy' into the new address form during initial checkout).
- Trolls who do enter  address info in the registration page are not likely going to use their real country.

These are some queries I use to find/delete bogus customers:

--1) long names are 'spam' messages
--DELETE Customer
select Id, Email, CreatedOnUtc, Active, Deleted, LastIpAddress from Customer
WHERE Id IN (select EntityId from GenericAttribute where KeyGroup = 'Customer' and [Key] = 'FirstName' and  len(value) > 50 )

--2) the 'google' company
--DELETE Customer
select Id, Email, CreatedOnUtc, Active, Deleted, LastIpAddress from Customer
WHERE Id IN (select  EntityId from GenericAttribute g where g.Value = 'google')

--3) only registered and nothing more (LastVisitedPage)
;with del as
(
    select Id, Email, CreatedOnUtc, Active, Deleted, LastIpAddress, (select Value from GenericAttribute ca where ca.EntityId = c.Id and ca.[Key] = 'Company') as Company
      from Customer c
     where 1=1
       and CreatedOnUtc = LastActivityDateUtc
       and Email is not null
       and not exists (select  1 from  [Order] o where o.CustomerId = c.Id)
       and exists (select 1 from GenericAttribute ca where ca.EntityId = c.Id and ca.[Key] = 'LastVisitedPage' and (ca.value like '%/register%' /*or ca.value like '%/contactus'*/))
)
--DELETE Customer where Id in (select Id from del)
select * from del

WOOOOO!! Excellent!