1

Please login or register to vote for this query.

(click on this box to dismiss)

Shows the top 100 user countries

Q&A for enthusiasts and power users of the Android operating system

DECLARE @sql nvarchar(MAX)
DECLARE @countries table (Name nvarchar(128))
DECLARE @unitedStates table (Name nvarchar(32) PRIMARY KEY CLUSTERED)
DECLARE @canada table (Name nvarchar(32) PRIMARY KEY CLUSTERED)
DECLARE @otherPlaces table (Name nvarchar(32) PRIMARY KEY CLUSTERED, RealName nvarchar(32))

CREATE TABLE #allUsers (EmailHash varchar(32) PRIMARY KEY CLUSTERED WITH(IGNORE_DUP_KEY = ON), Location nvarchar(100))

SET NOCOUNT ON

SELECT
  @sql = COALESCE(@sql + N' UNION ALL ', N'') +
    N'SELECT u.EmailHash COLLATE SQL_Latin1_General_CP1_CI_AI, u.Location COLLATE SQL_Latin1_General_CP1_CI_AI FROM ' + QUOTENAME(d.name) + N'..[Users] u WHERE u.EmailHash IS NOT NULL'
    FROM sys.databases d
    WHERE
      (d.database_id > 4) AND
      (d.name NOT LIKE '%.Meta.%')

INSERT INTO @unitedStates(Name)
  VALUES ('AL'),  ('AK'), ('AS'), ('AZ'), ('AR'), ('CA'), ('CO'), ('CT'), ('DE'), ('DC'), ('FM'), ('FL'),
    ('GA'), ('GU'), ('HI'), ('ID'), ('IL'), ('IN'), ('IA'), ('KS'), ('KY'), ('LA'), ('ME'), ('MH'), ('MD'),
    ('MA'), ('MI'), ('MN'), ('MS'), ('MO'), ('MT'), ('NE'), ('NV'), ('NH'), ('NJ'), ('NM'), ('NY'), ('NC'),
    ('ND'), ('MP'), ('OH'), ('OK'), ('OR'), ('PW'), ('PA'), ('PR'), ('RI'), ('SC'), ('SD'), ('TN'), ('TX'),
    ('UT'), ('VT'), ('VI'), ('VA'), ('WA'), ('WV'), ('WI'), ('WY'), ('AE'), ('AA'), ('AP'), ('US'),
    ('Alabama'), ('Alaska'), ('American Samoa'), ('Arizona'), ('Arkansas'), ('California'), ('Colorado'),
    ('Connecticut'), ('Delaware'), ('District of Columbia'), ('Federated States of Micronesia'), ('Florida'),
    ('Georgia'), ('Hawaii'), ('Hawai''i'), ('Idaho'), ('Illinois'), ('Indiana'), ('Iowa'), ('Kansas'), ('Kentucky'),
    ('Louisiana'), ('Maine'), ('Maryland'), ('Massachusetts'), ('Michigan'), ('Minnesota'), ('Mississippi'),
    ('Missouri'), ('Montana'), ('Nebraska'), ('Nevada'), ('New England'), ('New Hampshire'), ('New Jersey'), ('New Mexico'),
    ('New York'), ('North Carolina'), ('North Dakota'), ('Ohio'), ('Oklahoma'), ('Oregon'), ('Palau'),
    ('Pennsylvania'), ('Puerto Rico'), ('Rhode Island'), ('South Carolina'), ('South Dakota'), ('Tennessee'),
    ('Texas'), ('Utah'), ('Vermont'), ('Virgin Islands'), ('Virginia'), ('Washington'), ('West Virginia'),
    ('Wisconsin'), ('Wyoming'),
    ('USA'), ('United States of America'), ('Silicon Valley'), ('NYC'), ('New York City'), ('San Francisco'),
    ('Seattle'), ('Boston'), ('Baltimore'), ('D.C.'), ('Indianapolis'), ('Phoenix'),
    ('Los Angeles'), ('San Diego'), ('Atlanta'), ('Philadelphia'), ('Pittsburgh'), ('Houston'), ('Minneapolis'),
    ('Chicago'), ('Washington DC'), ('Las Vegas'), ('Vegas'), ('Austin'), ('Denver'), ('Dallas'), ('Salt Lake City')

INSERT INTO @canada(Name)
  VALUES ('AB'), ('BC'), ('MB'), ('NB'), ('NL'), ('NT'), ('NS'), ('NU'), ('ON'), ('PE'), ('QC'), ('SK'), ('YT'),
    ('Alberta'), ('British Columbia'), ('Manitoba'), ('New Brunswick'), ('Newfoundland'), ('Northwest Territories'),
    ('Nova Scotia'), ('Nunavut'), ('Ontario'), ('Prince Edward Island'), ('Quebec'), ('Saskatchewan'), ('Yukon Territory'),
    ('Toronto'), ('Montreal'), ('Vancouver'), ('Ontario, Canada'), ('Ottawa'), ('Calgary'), ('Edmonton')

INSERT INTO @otherPlaces(Name, RealName)
  VALUES ('UK', 'United Kingdom'), ('GB', 'Great Britian'), ('London', 'United Kingdom'),
    ('Hong Kong', 'China'), ('Bangalore', 'India'), ('Auckland', 'New Zealand'), ('Brasil', 'Brazil'),
    ('AU', 'Australia'), ('Sydney', 'Australia'), ('Melbourne', 'Australia'), ('Brisbane', 'Australia'), ('Western Australia', 'Australia'),
    ('Paris', 'France'), ('Edinburgh', 'Scotland'), ('Madrid', 'Spain'), ('Dublin', 'Ireland'), ('CZ', 'Czech Republic'),
    ('Prague', 'Czech Republic'), ('Belfast', 'Northern Ireland'), ('Bangkok', 'Thailand'), ('Warsaw', 'Poland'), ('Oslo', 'Norway'),
    ('Sao Paulo', 'Brazil'), ('Vienna', 'Austria'), ('Copenhagen', 'Denmark'), ('Glasgow', 'Scotland'),
    ('Berlin', 'Germany'), ('Stockholm', 'Sweden'), ('Hyderabad', 'India'), ('Munich', 'Germany'), ('Manchester', 'United Kingdom'),
    ('NZ', 'New Zealand'), ('Moscow', 'Russia'), ('SE', 'Sweden'), ('Mumbai', 'India'), ('Istanbul', 'Turkey'), ('FR', 'France'),
    ('NO', 'Norway'), ('CH', 'Switzerland'), ('Barcelona', 'Spain'), ('Amsterdam', 'The Netherlands'), ('PL', 'Poland'),
    ('Beijing', 'China'), ('Tokyo', 'Japan'), ('Netherlands', 'The Netherlands')

INSERT INTO #allUsers(EmailHash, Location)
  EXEC(@sql)

INSERT INTO @countries(Name)
  SELECT
    (
      CASE WHEN a.CommaIndex > 0 THEN
        LTRIM(RIGHT(a.Location, LEN(a.Location) - a.CommaIndex))
      ELSE
        a.Location
      END
    ) AS RightPart
    FROM
    (
      SELECT
        u.Location,
        CHARINDEX(',', u.Location) AS CommaIndex
        FROM #allUsers u
        WHERE LEN(Location) > 0
    ) a

UPDATE @countries
  SET Name = 'United States'
  WHERE Name COLLATE SQL_Latin1_General_CP1_CI_AI IN (SELECT Name COLLATE SQL_Latin1_General_CP1_CI_AI FROM @unitedStates)

UPDATE @countries
  SET Name = 'Canada'
  WHERE Name COLLATE SQL_Latin1_General_CP1_CI_AI IN (SELECT Name COLLATE SQL_Latin1_General_CP1_CI_AI FROM @canada)

UPDATE c
  SET c.Name = o.RealName
  FROM @countries c
  INNER JOIN @otherPlaces o ON o.Name = c.Name COLLATE SQL_Latin1_General_CP1_CI_AI

SET NOCOUNT OFF

SELECT TOP 100
  Name COLLATE SQL_Latin1_General_CP1_CI_AI,
  COUNT(1) AS Count
  FROM @countries
  GROUP BY Name COLLATE SQL_Latin1_General_CP1_CI_AI
  ORDER BY Count DESC

DROP TABLE #allUsers

fork query

edited may 9 12

created may 9 12 Jon Seigel