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 anime and manga fans

-- Where are our users from? (per-site version)
-- Shows the top 100 user countries

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))

SET NOCOUNT ON

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 @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 Users 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

fork query

created jun 17 11 Jon Seigel