instruction: Average Score per Tag. Shows which tags bring in the most viewers
response: SELECT Tags.TagName, AVG(PostScore.Score) AS "average_score", COUNT(*) AS "question_count" FROM Posts INNER JOIN PostTags ON Posts.Id = PostTags.PostId INNER JOIN Tags ON PostTags.TagId = Tags.Id LEFT JOIN (SELECT Votes.PostId, SUM(CASE VoteTypeId WHEN 2 THEN 1 ELSE -1 END) AS score FROM Votes WHERE VoteTypeId IN (2, 3) GROUP BY Votes.PostId) AS PostScore ON PostScore.PostId = Posts.Id GROUP BY Tags.TagName HAVING COUNT(*) = 4 ORDER BY 'average_score' DESC