Task 1

Find names (DisplayName) of TOP 500 users with the highest reputation

Answer

SELECT
  TOP 500
  DisplayName, Reputation
FROM
  Users
ORDER BY
  Reputation DESC

Task 2

From the previous query, find those users who are from USA (Location from dataset Users)

Answer

SELECT
  TOP 500
  DisplayName, Reputation, Location
FROM
  Users
WHERE
  Location like '%USA%' OR Location like '%United States%'
ORDER BY
  Reputation DESC

Task 3

Also, find only those users who are from USA and who wrote posts about SQL (tags 'sql' from Posts)

Answer

SELECT
  TOP 500
  DisplayName, Reputation, Location, Tags
FROM
  Users
  INNER JOIN
  Posts ON Users.Id = Posts.OwnerUserId
WHERE
  (Location like '%USA%' OR Location like '%United States%') AND 
  Tags LIKE '%sql%'
ORDER BY
  Reputation DESC

Task 4

Leave only users obsessing gold badges. To cope with this task, you should use subquery.

Hint Gold badges can be found with help of column 'BadgesClass'; their code number is 1.

Answer

SELECT
  DisplayName, Reputation, Location, Tags
FROM
  Users
  INNER JOIN
  Posts ON Users.Id = Posts.OwnerUserId
WHERE
  (Location like '%USA%' OR Location like '%United States%') AND 
  Tags LIKE '%sql%' AND
  Users.Id IN (SELECT Users.Id FROM Users INNER JOIN 
  Badges On Users.Id = Badges.UserId
  WHERE Badges.Class = '1')
ORDER BY
  Reputation DESC

Task 6

Count percentages of users who got badges "Nice Answer", "Good Answer", "Great Answer".

Answer

SELECT
  Badges.Name, 
  ROUND((Count(Badges.UserId)* 100.0 / 
  (Select Count(*) From Users)), 0) AS Badge_perc
FROM
   Badges
WHERE
  Badges.Name IN ('Nice Answer', 'Good Answer', 'Great Answer')
GROUP BY
  Badges.Name

Task 7

Find TOP 25 the most popular names of tags (TagName from Tags) of users from USA.

Hint 1 Count the number of tags

Hint 2 We have following ties between datasets: Users – Posts – PostTags – Tags

Visualize them.

Answer

SELECT
  Tags.TagName, Count(*) as tagc
FROM
  Users
  INNER JOIN
  Posts ON Users.Id = Posts.OwnerUserId
  INNER JOIN
  PostTags ON Posts.ID = PostTags.PostId
  INNER JOIN
  Tags ON PostTags.TagId = Tags.Id
WHERE
  Users.Location like '%USA%'
Group by
  Tags.TagName
ORDER BY
  tagc desc