Cross-site overlap; based on the other site

0

Please login or register to vote for this query.

(click on this box to dismiss)

To find where the users of a given site are active outside the given site. For example, running the script on Worldbuilding.SE would tell which user overlap the site shares with the others. Active is here defined as having a certain reputation on both sites. The base for the percentages is the total users on the other site.

Stack Overflow

Q&A for professional and enthusiast programmers

-- Original: https://data.stackexchange.com/space/query/355774/cross-site-query-template

-- Minimum reputation to be considered active
DECLARE @minrep int  = ##minrep##
DECLARE @min_str nvarchar(5) = CAST(@minrep AS nvarchar(5))

-- Number of users
DECLARE @nusers int
SELECT @nusers = count(*) FROM Users WHERE Reputation>@minrep;

-- Variables for within the loop
 -- cursor pointer
DECLARE @site_ite_name nvarchar(max)
 -- calculated overlap
DECLARE @ov real
 -- parameter for sp_executesql
DECLARE @parameter nvarchar(16) = N'@cnt int OUTPUT'


-- Create cursor for list of sites
DECLARE sites CURSOR FOR
  SELECT name
  FROM sys.databases
  WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Data.StackExchange')
-- And declare some variables
DECLARE @sitedbname AS nvarchar(max)
DECLARE @sitehostname AS nvarchar(max)
DECLARE @ispersitemeta AS bit

DECLARE @query AS nvarchar(max)
CREATE TABLE #out (
  Site nvarchar(max) NOT NULL,
  -- ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
  -- COLUMN NAMES WANT TO ADD SHOULD GO HERE   
  Name nvarchar(max) NOT NULL,
  Overlap real NOT NULL--,
  --[User Count] int NOT NULL
  -- ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑
)
-- These variables are for the SPOOKY HOSTNAME GENERATION CODE
DECLARE @spooky_string AS nvarchar(max)
DECLARE @spooky_delimiter AS char(1) = '.'
DECLARE @spooky_xml AS xml
DECLARE @spooky_result AS nvarchar(max)

-- Step through cursor
OPEN sites
FETCH NEXT FROM sites INTO @sitedbname
WHILE @@FETCH_STATUS = 0
BEGIN
  -----------------------------------------------------------------------------
  -- BEGIN SPOOKY HOSTNAME GENERATION CODE ------------------------------------
  -- adapted from <http://data.stackexchange.com/stackoverflow/query/256747/> -
  -----------------------------------------------------------------------------
  SET @spooky_string = @sitedbname
  SET @spooky_xml = CAST(('<X>' + REPLACE(@spooky_string, @spooky_delimiter, '</X><X>') + '</X>') AS xml)
  SET @spooky_result = ''

  SELECT
    C.value('.', 'nvarchar(max)') AS [Piece],
    C.value('for $i in . return count(../*[. << $i]) + 1', 'int') AS [Index]
  INTO #spooky_pieces
  FROM @spooky_xml.nodes('X') AS X(C)

  SELECT @spooky_result = COALESCE(@spooky_result + '.', '') + [Piece]
  FROM #spooky_pieces
  ORDER BY [Index] DESC

  DROP TABLE #spooky_pieces

  SET @sitehostname = 'http://' + RIGHT(@spooky_result, LEN(@spooky_result)-1) + '.com'
  SET @ispersitemeta = (CASE WHEN @sitedbname LIKE '%Meta%' AND @sitedbname != 'StackExchange.Meta' THEN 1 ELSE 0 END)

  ----------------------------------------
  -- HERE COME THE SPOOKY SPECIAL CASES --
  ----------------------------------------
  -- Meta MathOverflow doesn't have a redirect; see <http://meta.stackexchange.com/q/215071/224428>
  IF @sitedbname = 'StackExchange.Mathoverflow.Meta' SET @sitehostname = 'http://Meta.MathOverflow.net'
  -- For some reason probably involving the AVP/Audio/Video/Sound hullabaloo, there is 
  -- still a StackExchange.Audio DB that's getting updated. http://audio.stackexchange.com/
  -- no longer exists, so we use Video.SE for the hostname instead.
  IF @sitedbname = 'StackExchange.Audio' SET @sitehostname = 'http://Video.StackExchange.com'
  -----------------------------------------------------------------------------
  -- END SPOOKY HOSTNAME GENERATION CODE --------------------------------------
  -----------------------------------------------------------------------------

  -- ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
  -- CODE YOU WANT TO ADD SHOULD GO HERE
  IF @ispersitemeta=0
  BEGIN
      DECLARE @cnt_ov int = 0
  
      DECLARE @squery nvarchar(200)
      SET @squery = N'SELECT @cnt=count(*) FROM Users u1 inner join ['+@sitedbname+']..[Users] u2 ON u1.AccountId = u2.AccountId WHERE u1.Reputation>'+@min_str+'and u2.Reputation>'+@min_str
  
      EXECUTE sp_executesql @squery, @parameter, @cnt=@cnt_ov OUTPUT

      DECLARE @cnt_base int = 0
  
      DECLARE @squery2 nvarchar(200)
      SET @squery2 = N'SELECT @cnt=count(*) FROM ['+@sitedbname+']..[Users] WHERE Reputation>'+@min_str
      EXECUTE sp_executesql @squery2, @parameter, @cnt=@cnt_base OUTPUT
  
      SET @ov = 100.0*CAST(@cnt_ov as real)/CAST(@cnt_base as real)
      
      INSERT INTO #out 
        SELECT '' + @sitehostname + '|' + @sitedbname + '' AS [Site], @sitedbname AS Name, @ov AS Overlap
  END

  -- ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑

  FETCH NEXT FROM sites INTO @sitedbname
END
CLOSE sites
DEALLOCATE sites


-- Print the results
SELECT
  ROUND(Overlap,2) as "%",
  Site as [Site]
FROM 
  #out
ORDER BY Overlap DESC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results