Main Users Active On Meta

0

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

-- To run this query select a *main* site. The corresponding meta site will
-- be automatically queried. Does not work on MSE or StackApps.
--
-- 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.
--
-- https://meta.stackexchange.com/a/293155

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

DECLARE @Query NVARCHAR(MAX) =
  'INSERT INTO #Results SELECT
    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 Users WHERE Reputation > ##MinReputation?1##
     AND DATEDIFF(DAY, LastAccessDate, GETDATE()) <= ##ActiveDays?30##) main
    FULL OUTER JOIN 
    (SELECT Id FROM [' + DB_NAME() + '.Meta].dbo.Users WHERE Reputation > ##MinReputation##
     AND DATEDIFF(DAY, LastAccessDate, GETDATE()) <= ##ActiveDays##) meta
    ON main.Id = meta.Id'

EXEC(@Query)

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

Enter Parameters

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