0

Please login or register to vote for this query.

(click on this box to dismiss)

Shows the top 100 user countries

Q&A for Ubuntu users and developers

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'),
    ('San Francisco Bay Area'), ('Southern California'), ('Bay Area'), ('SoCal'), ('CA, USA'), ('Northern California'),
    ('SF Bay Area'), ('Northern Virginia'), ('Long Island'), ('New York, United States'),
    ('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'), ('ON, Canada'),
    ('BC, Canada'), ('Quebec, Canada'), ('AB, Canada')

INSERT INTO @otherPlaces(Name, RealName)
VALUES ('UK', 'United Kingdom'), ('GB', 'United Kingdom'), ('London', 'United Kingdom'), ('Scotland', 'United Kingdom'),
    ('Wales', 'United Kingdom'), ('Northern Ireland', 'United Kingdom'), ('England', 'United Kingdom'),
    ('Hong Kong', 'China'), ('Bangalore', 'India'), ('Auckland', 'New Zealand'), ('Brasil', 'Brazil'),
    ('AU', 'Australia'), ('Sydney', 'Australia'), ('Melbourne', 'Australia'), ('Brisbane', 'Australia'), ('Western Australia', 'Australia'),
    ('Adelaide', 'Australia'), ('South Australia', 'Australia'), ('Delhi', 'India'), ('New Delhi', 'India'), ('Banglore', 'India'),
    ('Paris', 'France'), ('Edinburgh', 'United Kingdom'), ('Madrid', 'Spain'), ('Dublin', 'Ireland'), ('CZ', 'Czech Republic'),
    ('Prague', 'Czech Republic'), ('Belfast', 'United Kingdom'), ('Bangkok', 'Thailand'), ('Warsaw', 'Poland'), ('Oslo', 'Norway'),
    ('Sao Paulo', 'Brazil'), ('Vienna', 'Austria'), ('Copenhagen', 'Denmark'), ('Glasgow', 'United Kingdom'),
    ('Berlin', 'Germany'), ('Stockholm', 'Sweden'), ('Hyderabad', 'India'), ('Munich', 'Germany'), ('Manchester', 'United Kingdom'),
    ('Kerala', 'India'), ('Noida', 'India'), ('Ahmedabad', 'India'), ('Pune', 'India'), ('Republic of Lithuania', 'Lithuania'),
    ('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'), ('Republic of Latvia', 'Latvia'),
    ('Chennai', 'India'), ('Myanmar', 'Burma'), ('Shanghai', 'China'), ('Indore', 'India'), ('chandigarh', 'India'),
    ('Viet nam', 'Vietnam'), ('kolkata', 'India'), ('jaipur', 'India'), ('coimbatore', 'India'), ('Lahore', 'Pakistan'),
    ('CN', 'China'), ('Dhaka', 'Bangladesh'), ('Minsk', 'Belarus'), ('Karachi', 'Pakistan'), ('Gujarat', 'India'),
    ('Gurgaon', 'India'), ('Turkiye', 'Turkey'), ('Rajkot', 'India'), ('Cochin', 'India'), ('Buenos Aires', 'Argentia'),
    ('Kampala', 'Uganda'), ('Surat', 'India'), ('Jakarta', 'Indonesia'), ('Nagpur', 'India'), ('Colombo', 'Sri Lanka'),
    ('Hamburg', 'Germany'), ('Budapest', 'Hungary'), ('Bucharest', 'Romania'), ('Kharkov', 'Ukraine'), ('Rome', 'Italy'),
    ('Palestinian Territories', 'Palestine'), ('Islamabad', 'Pakistan'), ('Catalonia', 'Spain'), ('Beijing China', 'China'),
    ('Goa', 'India'), ('Tamil Nadu', 'India'), ('Kiev', 'Ukraine'), ('Mysore', 'India'), ('Brussels', 'Belgium'),
    ('Seoul', 'South Korea'), ('Lisbon', 'Portugal'), ('Cape Town', 'South Africa'), ('Bengaluru', 'India'),
    ('bhopal', 'India'), ('Leeds', 'United Kingdom'), ('Italia', 'Italy'), ('Trivandrum', 'India'), ('Deutschland', 'Germany'),
    ('Bristol', 'United Kingdom'), ('Belgrade', 'Serbia'), ('Espana', 'Spain'), ('İzmir', 'Turkey'), ('Kathmandu', 'Nepal'),
    ('Rio de Janeiro', 'Brazil'), ('London UK', 'United Kingdom'), ('Gujarat, India', 'India'), ('cairo', 'Egypt'),
    ('ANKARA', 'Turkey'), ('Athens', 'Greece'), ('Gold Coast', 'Australia'), ('Bavaria', 'Germany'), ('Tehran', 'Iran'),
    ('Nederland', 'The Netherlands'), ('Holland', 'The Netherlands'), ('Lahore Pakistan', 'Pakistan'), ('Vadodara', 'India'),
    ('Zurich', 'Switzerland'), ('Taipei', 'Taiwan'), ('Sheffield', 'United Kingdom'), ('Hyderabad International Airport', 'India'),
    ('baroda', 'India'), ('Cambridge', 'United Kingdom'), ('Ernakulam', 'India'), ('Ludhiana', 'India'), ('Maharashtra', 'India'),
    ('Mexico City', 'Mexico'), ('pondicherry', 'India'), ('Srilanka', 'Sri Lanka'), ('Zagreb', 'Croatia'),
    ('Omsk', 'Russia'), ('Russian', 'Russia'), ('Saint-Petersburg', 'Russia'), ('Sofia', 'Bulgaria'),
    ('Russian Federation', 'Russia'), ('Lucknow', 'India'), ('Kuala Lumpur', 'Malaysia'), ('Gandhinagar', 'India'),
    ('Bali', 'Indonesia'), ('Bandung', 'Indonesia'), ('Kochi', 'India'), ('Kerala, India', 'India'), ('Madurai', 'India'),
    ('EU', 'Europe'), ('European Union', 'Europe') -- , ('', ''), ('', ''), ('', ''), ('', ''), ('', ''), ('', ''), ('', '')

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

UPDATE c
  SET c.Name = 'China' -- Not working
  FROM @countries c
  WHERE c.Name COLLATE SQL_Latin1_General_CP1_CI_AI = N'中国' 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 10 12 Mark Hurd

created may 9 12 Jon Seigel