Team Metova


Please login or register to vote for this query.

(click on this box to dismiss)

Ask Ubuntu

Q&A for Ubuntu users and developers

/* */

CREATE TABLE #MetovaUsers (Name nvarchar(100), StackOverflowUserId int)

INSERT INTO #MetovaUsers (Name, StackOverflowUserId)
SELECT 'Abbey Jackson', 5032318 UNION
SELECT 'Andrew Markley', 5683335 UNION
SELECT 'Andrew Neal', 420015 UNION 
SELECT 'Barrett Montgomery', 5683336 UNION
SELECT 'Bart Dorsey', 1518333 UNION
SELECT 'Bryan Lindsey', 5630906 UNION
SELECT 'Chris Johnson', 720083 UNION
SELECT 'Coburn Berry', 4126069 UNION
SELECT 'Not Dave Lane', 672380 UNION
SELECT 'David Knapp', 5630998 UNION
SELECT 'David Thacher', 809069 UNION
SELECT 'Emmy DeLoach', 4735818 UNION
SELECT 'Jami Couch', 2473275 UNION
SELECT 'Jason Ellis', 2937885 UNION
SELECT 'Jason Robinson', 291827 UNION 
SELECT 'Kaleb Lape', 5631086 UNION
SELECT 'Kenny Houser', 2665568 UNION
SELECT 'Levi Hobbs', 2539607 UNION 
SELECT 'Logan Gauthier', 4431666 UNION 
SELECT 'Logan Serman', 29595 UNION 
SELECT 'Nicholas Craig', 5683606 UNION
SELECT 'Nick Griffith', 2792531 UNION 
SELECT 'Nick Sinas', 78550 UNION 
SELECT 'Noah Yarian', 5078619 UNION
SELECT 'Riley Mills', 634997 UNION
SELECT 'Ron Unger', 178758 UNION 
SELECT 'Sarah Klinefelter', 1639078 UNION
SELECT 'Shahin Zangenehpour', 4727913 UNION
SELECT 'Zachary Hensley', 3987923 


  '' + CAST(SOU.Id as nvarchar) + '|' + SOU.DisplayName AS SOUserName, 
  SOU.Reputation AS StackOverflowRep,
  SOU.CreationDate AS SOJoinDate,
  SOU.Reputation / DATEDIFF(WEEK,SOU.CreationDate,GETDATE()) AS [SO Rep/Week Average],
  SUM(SOU.Reputation) OVER (PARTITION BY SOU.Id) AS SumStackOverflowRep,
  AVG(SOU.Reputation) OVER (PARTITION BY SOU.Id) AS AvgStackOverflowRep,
  '' + CAST(CRU.Id as nvarchar) + '|' + CRU.DisplayName AS CRUserName, 
  CRU.Reputation AS CodeReviewRep,
  CRU.CreationDate AS CRJoinDate,
  CRU.Reputation / DATEDIFF(WEEK,CRU.CreationDate,GETDATE()) AS [CR Rep/Week Average],
  '' + CAST(PU.Id as nvarchar) + '|' + PU.DisplayName AS ProgrammersUserName,
  PU.Reputation AS ProgrammersRep,
  PU.CreationDate AS ProgrammersJoinDate,
  PU.Reputation / DATEDIFF(WEEK,PU.CreationDate,GETDATE()) AS [Programmers Rep/Week Average]
FROM #MetovaUsers MU
  INNER JOIN StackOverflow.dbo.Users SOU ON SOU.Id = MU.StackOverflowUserId
  LEFT OUTER JOIN [StackExchange.CodeReview].dbo.Users CRU ON CRU.AccountId = SOU.AccountId
  LEFT OUTER JOIN [StackExchange.Programmers].dbo.Users PU ON PU.AccountID = SOU.AccountId
ORDER BY ISNULL(SOU.Reputation,0)+ISNULL(CRU.Reputation,0)+ISNULL(PU.Reputation,0) DESC, SOJoinDate ASC

Enter Parameters

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