Get Location Numbers of All Users Across Communities

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow на русском Meta

Вопросы и ответы о сайте для программистов

/* 
  This script will give you total number of users across all communities
  who used your search value in their location.
  If you want to see location stats for the entire StackExchange Network,
  enter the value 0 for the BreakdownPerCommunity paramter
  The Network wide statistics isn't the number of unique users 
  per location, as it evaluates the same StackExchange Network user
  as a different user in each community.
  Basically, if you have 3 profiles across the StackExchange Network, it will
  count your location 3 times.
  
  
*/
IF OBJECT_ID('tempdb..#temp_databases') is NOT NULL
  DROP TABLE #temp_databases

SELECT IDENTITY(int, 1,1) as id, [name]
INTO #temp_databases
FROM sys.databases
WHERE name NOT IN ('master','tempdb','model','msdb', 'Data.StackExchange');

DECLARE @searchLocation NVARCHAR(100) = REPLACE(##LocationToSearch:string##, CHAR(39), ''); -- LocationToSearch: Location (Mandatory) - Phrase to find in location field: 
DECLARE @showPerCommunity TINYINT = ABS(##ShowPerCommunity:int?1##); -- ShowPerCommunity: BreakdownPerCommunity (Optional) - 1 for true; 0 for false: 
DECLARE @count INT = 1;
DECLARE @databaseName VARCHAR(256);
DECLARE @sql VARCHAR(MAX) = '';


IF OBJECT_ID('tempdb..#database_users') is NOT NULL
  DROP TABLE #database_users_location;
CREATE TABLE #database_users_location (ID INT IDENTITY(1,1), DatabaseName VARCHAR(256), Location NVARCHAR(100), UserCount INT);

WHILE(@count <= (SELECT COUNT(*) FROM #temp_databases))
BEGIN
  SET @databaseName = (SELECT [name] FROM #temp_databases WHERE id = @count);
  SET @sql = 'INSERT INTO #database_users_location(DatabaseName, Location, UserCount) ' + CHAR(10) + CHAR(13) +
    'SELECT ''' + @databaseName + ''' AS [DatabaseName], [Location], COUNT(1) AS [UserCount] FROM [' + @databaseName + ']..Users (NOLOCK) GROUP BY [Location];';
  EXEC (@sql);
  SET @count += 1;
END
SET @count += 1;

IF(@showPerCommunity <> 0)
BEGIN
  SELECT DatabaseName as [Community], SUBSTRING(Location, CHARINDEX(@searchLocation, Location), LEN(@searchLocation)) as [Location], SUM(UserCount) AS [LocationUserCount]
  FROM #database_users_location
  WHERE Location LIKE ('%' + @searchLocation + '%')
  GROUP BY DatabaseName, SUBSTRING(Location, CHARINDEX(@searchLocation, Location), LEN(@searchLocation))
  ORDER BY [LocationUserCount] DESC;
END
ELSE
BEGIN
  SELECT 'StackExchange Network' as [Community], SUBSTRING(Location, CHARINDEX(@searchLocation, Location), LEN(@searchLocation)) as [Location], SUM(UserCount) AS [LocationUserCount]
  FROM #database_users_location
  WHERE Location LIKE ('%' + @searchLocation + '%')
  GROUP BY SUBSTRING(Location, CHARINDEX(@searchLocation, Location), LEN(@searchLocation))
  ORDER BY [LocationUserCount] DESC;
END

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results