Query Stack Overflow for Top 5 Tags and Children Posts

Select the Top Five Tags by Count

The core of my query are the tags. Everything else is based on knowing what exactly the top five tags are in the Stack Overflow database.

As mentioned earlier, this information has already been calculated in the `Tags table. Here is the structure:

All I care about at this point is Id; this is the foreign key that will relate back to Posts. I will get TagName and Count later.

DECLARE @n_tags int;

SET @n_tags = 5;

SELECT 
  TOP (@n_tags) Tags.Id
FROM 
  Tags
ORDER BY 
  Tags.Count DESC;

Table: Select the top five tags by count

This gives us the result set,

I create the variable n_tags so that if you or I want to expand the query later we can easily do so without editing the query itself.

Select All Posts for Respective Tags

Now that I know my top five tags, I need to find all posts that belong to each tag. I use PostTags for this and return only PostId and TagId.

The query I built in the previous step will become my subquery. I am telling the server to only return all posts where the tag id is contained in this list of values.

DECLARE @n_tags int;

SET @n_tags = 5;

SELECT
  PostTags.PostId, 
  PostTags.TagId
FROM 
  PostTags
WHERE
  PostTags.TagId IN (
    SELECT 
      TOP (@n_tags) Tags.Id
    FROM 
      Tags
    ORDER BY 
      Tags.Count DESC);

Table: Select all posts for each top five tag

And a sample of the result set:

There are thousands of results returned so I won’t list them all here.

Left Join Posts and Parition Over PostTags.TagId, Ordered by Posts.Score Descending

Now I need to get my hands slightly dirtier.

DECLARE @n_tags int;

SET @n_tags = 5;

SELECT
  PostTags.PostId, 
  PostTags.TagId, 
  n = ROW_NUMBER() OVER (
    PARTITION BY 
      PostTags.TagId
    ORDER BY 
      Posts.Score DESC
  )
FROM 
  PostTags
LEFT JOIN
  Posts
ON 
  PostTags.PostId = Posts.Id
WHERE
  PostTags.TagId IN (
    SELECT 
      TOP (@n_tags) Tags.Id
    FROM 
      Tags
    ORDER BY 
      Tags.Count DESC);

Table: Join Posts and number all posts within each tag, ordered by Score

I have proceeded to LEFT JOIN Posts to my query in order to use Posts.Score. By ordering Posts.Score DESC, I can assign ROW_NUMBER() to each post PARTITION BY PostTags.TagId. In other words, this is another way of grouping rows and assigning values to each row within a group. Because I have ordered the results by Posts.Score within each TagId, the function ROW_NUMBER() works as a ranking assignment, thus giving n.

| PostId  | TagId | n   |
|---------|-------|-----|
| 111102  | 3     | 1   |
| 503093  | 3     | 2   |
| 1789945 | 3     | 3   |
| 1335851 | 3     | 4   |
| 178325  | 3     | 5   |
| ...     | ...   | ... |

You don’t see it here (nor in the online browser without modifying the query) but for every unique TagId exists a range of n values from 1 through unknown (however many posts exist within the tag).

Now I have a ranking for each post within every tag group. This sets me up nicely to use n shortly to filter out the result set as intended.

DECLARE @n_tags int;
DECLARE @n_posts int;

SET @n_tags = 5;
SET @n_posts = 5;

SELECT 
  PostRanks.Id, 
  PostRanks.Score, 
  PostRanks.TagId, 
  Posts.Title, 
  Posts.Tags
FROM (
  SELECT
    Posts.Id,
    Posts.Score, 
    PostTags.TagId, 
    n = ROW_NUMBER() OVER (
      PARTITION BY
        PostTags.TagId
      ORDER BY 
      Posts.Score DESC)
  FROM 
    PostTags
  LEFT JOIN
    Posts
  ON 
    PostTags.PostId = Posts.Id 
  WHERE
    PostTags.TagId IN (
      SELECT 
        TOP (@n_tags) Tags.Id
      FROM 
        Tags
      ORDER BY 
        Tags.Count DESC)) AS PostRanks
LEFT JOIN
  Posts
ON Posts.Id = PostRanks.Id
WHERE 
  n <= @n_posts
ORDER BY
  PostRanks.TagId, 
  PostRanks.Score DESC;

Tips

DECLARE @n_tags int;

SET @n_tags = 5;

SELECT
  TOP 5 PostTags.PostId, 
  PostTags.TagId, 
  Posts.Score
FROM 
  PostTags
LEFT JOIN
  Posts
ON 
  PostTags.PostId = Posts.Id
WHERE
  PostTags.TagId IN (
    SELECT 
      TOP (@n_tags) Tags.Id
    FROM 
      Tags
    ORDER BY 
      Tags.Count DESC)
GROUP BY 
  PostTags.TagId
ORDER BY
  Posts.Score DESC;

Column 'PostTags.PostId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Conclusion

You now have a query that you can easily edit to expand tag groups or include more posts per group. Data Exchange returns 50,000 rows in the result pane so you have plenty of room to work with.

Some things you may want to try:

1. Instead of sorting by Posts.Score, can you edit the code to sort by Posts.CreationDate?

2. Examine the execution plan of my query? With that information, modify the code to include Posts.Title and Posts.Tags on the second subquery (starting at line #19). This removes the need for the outer LEFT JOIN statement (line #44) but how does this impact the execution time of the query?

3. If you are feeling really brave, instead of relying on PostTags.Count, can you split Posts.Tags and aggregate posts based on each tag? How does this query compare to the simpler SELECT query used originally?

The raw query and dataset can be found in my Datasets repo. If you have noticed any errors or simply want to fork the code, please feel free.

Lastly, please feel free to reach out to me on twitter for any questions or comments.

Thank you for reading!