2

What I wish to obtain is a neat table displaying these columns from left to right - "User Name", "Reputation", "Main site posts count", "Meta site posts count".

The problem here is that SEDE won't (or at least I think it won't, I may be wrong) be able to count posts from both the meta and main site. The SEDE site selector allows me to select, for example, "Mathematics" or "Mathematics Meta" but not both at once.

Of course, I can run SEDE once on the main site and another time on the meta site and then combine the two tables, but (1) I don't know how to combine the two tables, and (2) I hope there's a more efficient method here. Is there a way to combine Main and Meta sites in one query?

2

It's certainly possible, you just need to fully qualify the database names ([StackExchange.Math].dbo.Users instead of just Users). You'll lose some of the functionality such as easy site switching (note that it's a Stack Overflow query) and the magic links for posts and users (I had to build the URL myself).

enter image description here

For reference, here is the complete query:

SELECT 'https://math.stackexchange.com/u/' + CAST(Main.Id AS NVARCHAR)
  + '|' + Main.DisplayName,
  Main.Reputation,
  Main.Total AS '# of posts (main)',
  Meta.Total AS '# of posts (meta)',
  CASE WHEN Meta.Total = 0 THEN NULL
       ELSE ROUND(CAST(Main.Total AS FLOAT) / Meta.Total, 2) END AS 'Ratio'

FROM

(SELECT u.Id, u.DisplayName, u.Reputation, COUNT(*) AS Total
  FROM [StackExchange.Math].dbo.Users AS u
  INNER JOIN [StackExchange.Math].dbo.Posts AS p ON u.Id = p.OwnerUserId
  GROUP BY u.Id, u.DisplayName, u.Reputation) AS Main

INNER JOIN

(SELECT u.Id, COUNT(*) AS Total
  FROM [StackExchange.Math.Meta].dbo.Users AS u
  INNER JOIN [StackExchange.Math.Meta].dbo.Posts AS p ON u.Id = p.OwnerUserId
  GROUP BY u.Id) AS Meta

ON Main.Id = Meta.Id

ORDER BY Main.Reputation DESC
  • Woah, cool! Thanks! Can you please add a fifth column - "Ratio of main site to meta site post counts" as well? (I know I should have added it into the question originally, but forgot, sorry!) – Gaurang Tandon Mar 16 '18 at 6:51
  • Also, an optimization problem for you: currently getting this query to work for another site, say Chemistry SE, will require changing five separate instances of text. Is there any way to store the site name in a global variable at the top of the code so it will need to be changed only once? Many thanks! – Gaurang Tandon Mar 16 '18 at 6:55
  • The ratio is easy to add, see the updated answer. I bet you could've done that yourself. For your second comment, you have to do something like this query where you build the SQL string dynamically and then execute it. You lose syntax highlighting, that's a tradeoff and as you can see, there are many sites for which the database name doesn't match the URL, so you have to build a lot of exceptions. – Glorfindel Mar 16 '18 at 7:47
  • Thank you! Yes, it is pretty clear now. "I bet you could've done that yourself" That is one thing I've been really trying to do for the past week. I took the SEDE tutorial this site has, but I still am not sure if it was taught how to do CASE WHEN Meta.Total = 0 THEN NULL ELSE ROUND(CAST(Main.Total AS FLOAT) / Meta.Total, 2) END AS 'Ratio' in it. Maybe it was but I missed it? Idk, but I still don't feel completely confident doing these queries, so I am trying to learn by example code as much as possible. This code was very helpful for me to learn new things, thanks for that! – Gaurang Tandon Mar 16 '18 at 8:05
  • You could've come up with Main.Total / Meta.Total; that would have failed for two reasons: Meta.Total can be 0, which leads to a division-by-zero error, and when you divide two integers, you get another integer (that's where the CAST and ROUND are for). – Glorfindel Mar 16 '18 at 8:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .