Main Users Active On Meta, All Sites

1

Please login or register to vote for this query.

(click on this box to dismiss)

Super User

Q&A for computer enthusiasts and power users

-- Output is number of users recently active on main, meta, both, and the
-- fraction of users active on main who are also active on both. Excludes MSE
-- and StackApps, which do not have a corresponding meta.
--
-- https://meta.stackexchange.com/a/293155

DECLARE @db SYSNAME
DECLARE @Query NVARCHAR(max)
DECLARE c CURSOR FOR 
  SELECT Name 
  FROM sys.databases 
  WHERE database_id > 5
  AND name NOT LIKE '%.Meta'
  AND name NOT LIKE 'StackApps%'

CREATE TABLE #Results ([Site] NVARCHAR(100), [Active Main] INT, [Active Meta] INT, [Active Both] INT)

OPEN c
FETCH NEXT FROM c INTO @db
WHILE (@@FETCH_STATUS = 0) BEGIN
  SET @Query =
    'INSERT INTO #Results SELECT
      ''' + @db + ''' [Site],
      COUNT(main.Id) [Active Main],
      COUNT(meta.Id) [Active Meta],
      COALESCE(SUM(CASE WHEN main.Id IS NOT NULL AND meta.Id IS NOT NULL THEN 1 ELSE 0 END), 0) [Active Both]
    FROM
      (SELECT Id FROM ' + QUOTENAME(@db) + '.dbo.Users WHERE Reputation > ##MinReputation?1##
       AND DATEDIFF(DAY, LastAccessDate, GETDATE()) <= ##ActiveDays?30##) main
      FULL OUTER JOIN 
      (SELECT Id FROM ' + QUOTENAME(@db + '.Meta') + '.dbo.Users WHERE Reputation > ##MinReputation##
       AND DATEDIFF(DAY, LastAccessDate, GETDATE()) <= ##ActiveDays##) meta
      ON main.Id = meta.Id'
  EXEC(@Query)
  FETCH NEXT FROM c INTO @db
END;
CLOSE c;
DEALLOCATE c;

SELECT 
  *,
  CASE WHEN [Active Main] > 0 
  THEN 1.0 * [Active Both] / [Active Main]
  ELSE 0 END [Fraction Active On Both]
FROM 
  #Results
ORDER BY
  [Site]

Enter Parameters

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