Data Explorer (SEDE)

« first day (782 days earlier) ← previous day next day → last day (1315 days later) »

3:47 PM

Hi there. A brief question. I'm creating a query to list users with highest number of sites having a given badge. I made one but it is counting the total number of badges across all sites. This inflates the numbers.
my code:
select top 100 b.userid, u.displayname, count(*) as gold
from badges b
inner join users u on u.id = b.userid
where name in ('BADGE')
group by b.userid, u.displayname
order by gold desc
I feel I only need a small tweek
but can't figure it out

Something like SELECT TOP ... FROM

(i'm total noob on SQL)

Martin

4:06 PM

I suppose this is your query: data.stackexchange.com/stackoverflow/revision/1295515/1593992

I made a small modification to get also a link to the users' profile: data.stackexchange.com/stackoverflow/query/1295516

@luchonacho It seems that this query counts badges a single site, not from all sites.

For example, the top user has 273 Farmous Question badges on Stack Overflow.

If you want to create a query which goes over all sites, that is more complicated. I do not have much experience experience in queries going multiple sites, but there are many other users who create them regularly.

If you wanted to make something which looks on all sites, I am pretty sure rene will be able to give you some advice.

luchonacho

Hi Martin

thanks

but that query counts multiple badges in the same site

I need to count only one

per site

most of the top user questions are in one site

the analysis is per site

I tried count(1) unsuccessfully too

Iit might be something like SELECT TOP

@Martin

Martin

So you wanted to create some query which goes over several sites, not only on SO?

luchonacho

yes

sorry

I thought my code did that

doesnt?

it is not site specific

as far as I can see

Martin

As I have mentioned, queries which go over several sites are more complicated. (And they are not something I am able to do.)

See here for an example of a query which goes through several sites:

Dec 20 '19 at 14:42, by rene

Nope. Don't worry, it is not much work. most of the query comes from my default template I have for that: https://data.stackexchange.com/stackoverflow/query/1004129/multi-db-template-with-hostname

luchonacho

Q: Allow cross-site queries in data explorer

I think it would be useful if we could join across databases in the data-explorer. For example, I'd like to query up a list of questions that I had a hand in migrating away from SO, and see what percentage of them were subsequently closed or re-migrated. I realise I could download the various d...

Martin

4:17 PM

@luchonacho Your query checks only SO, since you have run it on SO. See the site name in the link and in the top right corner. You can run the same query on Stack Overflow, Mathematics, MathOVerflow, etc.

If you have some SEDE query, to run it on another site, you can use the selector which says "switch sites".

luchonacho

I see

Martin

4:29 PM

In any case, you'll probably get some better advice from more experienced users. I have at least mentioned that this query runs on Stack Overflow only.

See you later!

« first day (782 days earlier) ← previous day next day → last day (1315 days later) »

all rooms