Styjun

Where is the logic in castrating fighters?

Should breaking down something like a door be adjudicated as an attempt to beat its AC and HP, or as an ability check against a set DC?

Is Jon Snow the last of his House?

Where have Brexit voters gone?

Is there an online tool which supports shared writing?

Reduction from Exact Cover to Fixed Exact Cover

Difference between audio interface and mixer

Why are values I enter in the interface getting halved?

Would jet fuel for an F-16 or F-35 be producible during WW2?

Did people go back to where they were?

What was the idiom for something that we take without a doubt?

Employer asking for online access to bank account - Is this a scam?

I unknowingly submitted plagarised work

In the current era, do any wizards survive from 1372 DR?

Why are C64 games inconsistent with which joystick port they use?

Is it true that cut time means "play twice as fast as written"?

How strong are Wi-Fi signals?

Is neural networks training done one-by-one?

Does the unit of measure matter when you are solving for the diameter of a circumference?

Is it unethical to use a published code in my PhD thesis work?

Is the Indo-European language family made up?

Why were helmets and other body armour not commonplace in the 1800s?

Looking for a soft substance that doesn't dissolve underwater

What are these arcade games in Ghostbusters 1984?

How to SQL the ten most upvoted questions for the ten most used tags (in SEDE)?

How do I perform an IF…THEN in an SQL SELECT?How to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?How can foreign key constraints be temporarily disabled using T-SQL?How can I do an UPDATE statement with JOIN in SQL?How do I escape a single quote in SQL Server?SQL Server: How to Join to first rowHow do I UPDATE from a SELECT in SQL Server?How to use the Stack Exchange Data Explorer to find every user's top tags?How to get ALL users' posts' tags (include answer's tags) in the Stack Exchange Data Explorer?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

-1

In the Stack Exchange Data Explorer (SEDE), I'm trying to get the ten most upvoted questions for the ten most used tags (i.e. JavaScript, HTML, ...).

I see how to get the most used tags:

select Id, TagName, Count
from Tags t
order by count desc

Now for each tag, I'd like to get the top ten upvoted questions. I need some sort of Join I guess. The problem is that in the Posts table, Tags is an array.

tsql sql-server-2017 dataexplorer

share|improve this question

edited Mar 24 at 5:33

Brock Adams

71k16163220

asked Mar 23 at 20:55

TMOTTMTMOTTM

1,09431328

add a comment | 

-1

In the Stack Exchange Data Explorer (SEDE), I'm trying to get the ten most upvoted questions for the ten most used tags (i.e. JavaScript, HTML, ...).

I see how to get the most used tags:

select Id, TagName, Count
from Tags t
order by count desc

Now for each tag, I'd like to get the top ten upvoted questions. I need some sort of Join I guess. The problem is that in the Posts table, Tags is an array.

tsql sql-server-2017 dataexplorer

share|improve this question

edited Mar 24 at 5:33

Brock Adams

71k16163220

asked Mar 23 at 20:55

TMOTTMTMOTTM

1,09431328

add a comment | 

In the Stack Exchange Data Explorer (SEDE), I'm trying to get the ten most upvoted questions for the ten most used tags (i.e. JavaScript, HTML, ...).

I see how to get the most used tags:

select Id, TagName, Count
from Tags t
order by count desc

Now for each tag, I'd like to get the top ten upvoted questions. I need some sort of Join I guess. The problem is that in the Posts table, Tags is an array.

tsql sql-server-2017 dataexplorer

share|improve this question

edited Mar 24 at 5:33

Brock Adams

71k16163220

asked Mar 23 at 20:55

TMOTTMTMOTTM

1,09431328

select Id, TagName, Count
from Tags t
order by count desc

tsql sql-server-2017 dataexplorer

tsql sql-server-2017 dataexplorer

share|improve this question

edited Mar 24 at 5:33

Brock Adams

71k16163220

asked Mar 23 at 20:55

TMOTTMTMOTTM

1,09431328

share|improve this question

edited Mar 24 at 5:33

Brock Adams

71k16163220

asked Mar 23 at 20:55

TMOTTMTMOTTM

1,09431328

edited Mar 24 at 5:33

Brock Adams

71k16163220

edited Mar 24 at 5:33

Brock Adams

71k16163220

asked Mar 23 at 20:55

TMOTTMTMOTTM

1,09431328

asked Mar 23 at 20:55

TMOTTMTMOTTM

1,09431328

1 Answer
1

active

oldest

votes

0

Refer to the SEDE schema. Use the PostTags table to link tags to questions.

Then you can use ROW_NUMBER() to rank the top 10 questions per tag.

Here's one way (See it live in SEDE):

WITH topTags AS (
 SELECT TOP 10
 t.Id,
 t.TagName,
 t.Count,
 tagRank = ROW_NUMBER() OVER (ORDER BY t.Count DESC)
 FROM Tags t
 ORDER BY t.Count DESC
)
SELECT
 qbt.TagName AS [Tag],
 --qbt.tagRank AS [Tag Rank],
 qbt.Count AS [Q's for Tag],
 qbt.Score AS [Qst Score],
 qbt.Id AS [Post Link],
 qbt.qstRow AS [Rank in tag]
FROM (
 SELECT
 tt.TagName,
 --tt.tagRank,
 tt.Count,
 q.Score,
 q.Id,
 qstRow = ROW_NUMBER() OVER (PARTITION BY tt.Id ORDER BY tt.Id, q.Score DESC)
 FROM topTags tt
 LEFT JOIN PostTags pt ON pt.TagId = tt.Id
 LEFT JOIN Posts q ON q.ID = pt.PostId
) qbt
WHERE qbt.qstRow <= 10
ORDER BY qbt.Count DESC,
 qbt.Score DESC

share|improve this answer

answered Mar 24 at 5:30

Brock AdamsBrock Adams

71k16163220

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55318286%2fhow-to-sql-the-ten-most-upvoted-questions-for-the-ten-most-used-tags-in-sede%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Refer to the SEDE schema. Use the PostTags table to link tags to questions.

Then you can use ROW_NUMBER() to rank the top 10 questions per tag.

Here's one way (See it live in SEDE):

WITH topTags AS (
 SELECT TOP 10
 t.Id,
 t.TagName,
 t.Count,
 tagRank = ROW_NUMBER() OVER (ORDER BY t.Count DESC)
 FROM Tags t
 ORDER BY t.Count DESC
)
SELECT
 qbt.TagName AS [Tag],
 --qbt.tagRank AS [Tag Rank],
 qbt.Count AS [Q's for Tag],
 qbt.Score AS [Qst Score],
 qbt.Id AS [Post Link],
 qbt.qstRow AS [Rank in tag]
FROM (
 SELECT
 tt.TagName,
 --tt.tagRank,
 tt.Count,
 q.Score,
 q.Id,
 qstRow = ROW_NUMBER() OVER (PARTITION BY tt.Id ORDER BY tt.Id, q.Score DESC)
 FROM topTags tt
 LEFT JOIN PostTags pt ON pt.TagId = tt.Id
 LEFT JOIN Posts q ON q.ID = pt.PostId
) qbt
WHERE qbt.qstRow <= 10
ORDER BY qbt.Count DESC,
 qbt.Score DESC

share|improve this answer

answered Mar 24 at 5:30

Brock AdamsBrock Adams

71k16163220

add a comment | 

0

Refer to the SEDE schema. Use the PostTags table to link tags to questions.

Then you can use ROW_NUMBER() to rank the top 10 questions per tag.

Here's one way (See it live in SEDE):

WITH topTags AS (
 SELECT TOP 10
 t.Id,
 t.TagName,
 t.Count,
 tagRank = ROW_NUMBER() OVER (ORDER BY t.Count DESC)
 FROM Tags t
 ORDER BY t.Count DESC
)
SELECT
 qbt.TagName AS [Tag],
 --qbt.tagRank AS [Tag Rank],
 qbt.Count AS [Q's for Tag],
 qbt.Score AS [Qst Score],
 qbt.Id AS [Post Link],
 qbt.qstRow AS [Rank in tag]
FROM (
 SELECT
 tt.TagName,
 --tt.tagRank,
 tt.Count,
 q.Score,
 q.Id,
 qstRow = ROW_NUMBER() OVER (PARTITION BY tt.Id ORDER BY tt.Id, q.Score DESC)
 FROM topTags tt
 LEFT JOIN PostTags pt ON pt.TagId = tt.Id
 LEFT JOIN Posts q ON q.ID = pt.PostId
) qbt
WHERE qbt.qstRow <= 10
ORDER BY qbt.Count DESC,
 qbt.Score DESC

share|improve this answer

answered Mar 24 at 5:30

Brock AdamsBrock Adams

71k16163220

add a comment | 

Refer to the SEDE schema. Use the PostTags table to link tags to questions.

Then you can use ROW_NUMBER() to rank the top 10 questions per tag.

Here's one way (See it live in SEDE):

WITH topTags AS (
 SELECT TOP 10
 t.Id,
 t.TagName,
 t.Count,
 tagRank = ROW_NUMBER() OVER (ORDER BY t.Count DESC)
 FROM Tags t
 ORDER BY t.Count DESC
)
SELECT
 qbt.TagName AS [Tag],
 --qbt.tagRank AS [Tag Rank],
 qbt.Count AS [Q's for Tag],
 qbt.Score AS [Qst Score],
 qbt.Id AS [Post Link],
 qbt.qstRow AS [Rank in tag]
FROM (
 SELECT
 tt.TagName,
 --tt.tagRank,
 tt.Count,
 q.Score,
 q.Id,
 qstRow = ROW_NUMBER() OVER (PARTITION BY tt.Id ORDER BY tt.Id, q.Score DESC)
 FROM topTags tt
 LEFT JOIN PostTags pt ON pt.TagId = tt.Id
 LEFT JOIN Posts q ON q.ID = pt.PostId
) qbt
WHERE qbt.qstRow <= 10
ORDER BY qbt.Count DESC,
 qbt.Score DESC

share|improve this answer

answered Mar 24 at 5:30

Brock AdamsBrock Adams

71k16163220

WITH topTags AS (
 SELECT TOP 10
 t.Id,
 t.TagName,
 t.Count,
 tagRank = ROW_NUMBER() OVER (ORDER BY t.Count DESC)
 FROM Tags t
 ORDER BY t.Count DESC
)
SELECT
 qbt.TagName AS [Tag],
 --qbt.tagRank AS [Tag Rank],
 qbt.Count AS [Q's for Tag],
 qbt.Score AS [Qst Score],
 qbt.Id AS [Post Link],
 qbt.qstRow AS [Rank in tag]
FROM (
 SELECT
 tt.TagName,
 --tt.tagRank,
 tt.Count,
 q.Score,
 q.Id,
 qstRow = ROW_NUMBER() OVER (PARTITION BY tt.Id ORDER BY tt.Id, q.Score DESC)
 FROM topTags tt
 LEFT JOIN PostTags pt ON pt.TagId = tt.Id
 LEFT JOIN Posts q ON q.ID = pt.PostId
) qbt
WHERE qbt.qstRow <= 10
ORDER BY qbt.Count DESC,
 qbt.Score DESC

share|improve this answer

answered Mar 24 at 5:30

Brock AdamsBrock Adams

71k16163220

answered Mar 24 at 5:30

Brock AdamsBrock Adams

71k16163220

answered Mar 24 at 5:30

Brock AdamsBrock Adams

71k16163220