Global Archaeologist progress summary

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- result table, don't rename and keep the site column
CREATE TABLE #results (site nvarchar(250),
                       userid int,
                       editcount int);

DECLARE @sql NVARCHAR(MAX) = ''   -- holds build up sql string

-- build one biq union sql, for each db
SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
SELECT ''' + name + ''', MAX(u.Id), COUNT(DISTINCT(p.Id))
  FROM ' + quotename(name) + '.dbo.Users AS u
  INNER JOIN ' + quotename(name) + '.dbo.PostHistory AS h ON u.Id = h.UserId  
  INNER JOIN ' + quotename(name) + '.dbo.Posts AS p ON p.Id = h.PostId
  OUTER APPLY (
    SELECT MAX(CreationDate) AS PreviousEditDate
      FROM ' + quotename(name) + '.dbo.PostHistory
      WHERE PostId = h.PostId
        AND PostHistoryTypeId IN (1, 2, 3, 4, 5, 6) -- all edits count
        AND Id < h.Id -- suggested edits you improved will have the same timestamp but do not count
  ) AS oa
  WHERE u.AccountId = ' + CAST(##AccountId:int## as nvarchar) + '
    AND h.PostHistoryTypeId IN (4, 5) -- Edit Title, Edit Body
    AND h.CreationDate >= DATEADD(DAY, 180, oa.PreviousEditDate)
    AND p.PostTypeId IN (1, 2)
    -- tag wikis/excerpts do not count
    AND (p.OwnerUserId IS NULL OR p.OwnerUserId != u.Id)
    -- exclude own questions and answers
'
FROM sys.databases
WHERE database_id > 5
  AND name NOT LIKE 'StackExchange.Arabic%'
-- AccountId: The global account ID of the user, which you can find in the URL of their network profile. Visit this link: http://stackexchange.com/users/current to see your own ID.

-- execute it
EXEC (@sql)

SELECT 
  LOWER('https://' +
    IIF(PATINDEX('%.Mathoverflow%', site) > 0,
    IIF(PATINDEX('%.Meta', site) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'),
      IIF(PATINDEX('%.Ubuntu%', site) > 0,
      IIF(PATINDEX('%.Meta', site) > 0, 'meta.askubuntu.com', 'askubuntu.com'),
        IIF(PATINDEX('StackExchange.%', site) > 0,
          CASE SUBSTRING(site, 15, 200)
          WHEN 'Audio' THEN 'video'
          WHEN 'Audio.Meta' THEN 'video.meta'
          WHEN 'Beer' THEN 'alcohol'
          WHEN 'Beer.Meta' THEN 'alcohol.meta'
          WHEN 'CogSci' THEN 'psychology'
          WHEN 'CogSci.Meta' THEN 'psychology.meta'
          WHEN 'Garage' THEN 'mechanics'
          WHEN 'Garage.Meta' THEN 'mechanics.meta'
          WHEN 'Moderators' THEN 'communitybuilding'
          WHEN 'Moderators.Meta' THEN 'communitybuilding.meta'
          WHEN 'Photography' THEN 'photo'
          WHEN 'Photography.Meta' THEN 'photo.meta'
          WHEN 'Programmers' THEN 'softwareengineering'
          WHEN 'Programmers.Meta' THEN 'softwareengineering.meta'
          WHEN 'Vegetarian' THEN 'vegetarianism'
          WHEN 'Vegetarian.Meta' THEN 'vegetarianism.meta'
          WHEN 'Writers' THEN 'writing'
          WHEN 'Writers.Meta' THEN 'writing.meta'
          ELSE SUBSTRING(site, 15, 200)
          END + '.stackexchange.com',
          IIF(PATINDEX('StackOverflow.%', site) > 0,
            CASE SUBSTRING(site, 15, 200)
            WHEN 'Br' THEN 'pt'
            WHEN 'Br.Meta' THEN 'pt.meta'
            ELSE SUBSTRING(site, 15, 200)
            END + '.stackoverflow.com',
            IIF(PATINDEX('%.Meta', site) > 0,
              'meta.' + SUBSTRING(site, 0, PATINDEX('%.Meta', site)) + '.com',
              site + '.com'
            )
          )
        )
      )
      )) + '/users/' + CAST(userid AS NVARCHAR) + '?tab=activity&sort=revisions|' + site,
  editcount AS '# of Archaeologist edits'
  FROM #results
  WHERE editcount > 0
  ORDER BY editcount DESC
     
DROP TABLE #results

Enter Parameters

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