WITH GitHubUsers AS ( SELECT u.Id, u.WebsiteUrl, u.UpVotes,

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

WITH GitHubUsers AS (
  SELECT 
    u.Id, 
    u.WebsiteUrl, 
    u.UpVotes, 
    u.Reputation,
    u.AboutMe,
    (
      SELECT COUNT(p.Id)
      FROM Posts p 
      WHERE p.PostTypeId = 2 AND p.OwnerUserId = u.Id
    ) as answers,
    (
      SELECT COUNT(p.Id)
      FROM Posts p 
      WHERE p.PostTypeId = 1 AND p.OwnerUserId = u.Id
    ) as questions
  FROM Users u
  WHERE 
    WebsiteUrl LIKE '%github%' 
    OR AboutMe LIKE '%github%'
), 
PluralSightUsers AS (
  SELECT 
    u.Id, 
    u.WebsiteUrl, 
    u.UpVotes, 
    u.Reputation,
    u.AboutMe,
    (
      SELECT COUNT(p.Id)
      FROM Posts p 
      WHERE p.PostTypeId = 2 AND p.OwnerUserId = u.Id
    ) as answers,
    (
      SELECT COUNT(p.Id)
      FROM Posts p 
      WHERE p.PostTypeId = 1 AND p.OwnerUserId = u.Id
    ) as questions
  FROM Users u
  WHERE 
    WebsiteUrl LIKE '%pluralsight%' 
    OR AboutMe LIKE '%pluralsight%'
)

SELECT gh.* FROM GitHubUsers gh
JOIN PluralSightUsers ps ON gh.Id = ps.Id

Enter Parameters

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