Data Explorer (SEDE)

« first day (1861 days earlier) ← previous day next day → last day (471 days later) »

Martin

04:57

Recently posted: Staff escalation stats does not count some international sites due to a SQL query bug

I am not sure whether I would call it a bug - it correctly counts the posts with status-review.

Of course, if somebody needs escalations from the whole network, the tags mentioned in the posts are relevant, too.

> Here is a list of localized [status-review] tags I noticed (in the alphabetical order of site URLs):

> [estado-en-revisión] es.meta.stackoverflow.com/tags/estado-en-revisi%c3%b3n/synonyms

> [調査中] ja.meta.stackoverflow.com/tags/%e8%aa%bf%e6%9f%bb%e4%b8%ad/…

> [status-revisão] pt.meta.stackoverflow.com/tags/status-revis%c3%a3o/synonyms

> [статус-проверка] ru.meta.stackoverflow.com/tags/…

It seems that status-review is a synonym of those tags. (But the master tag is the non-English tag.)

Feeds

Q: Staff escalation stats does not count some international sites due to a SQL query bug

This is a bug report of a SQL query used at What posts should be escalated to staff using [status-review], and how do I escalate them?. In the above post, [status-review] posts are counted using this SEDE query: https://data.stackexchange.com/meta.stackexchange/query/1245452/posts-with-status-rev...

Martin

The relevant part of the query is AND ph.Text LIKE ''%<status-review>%''.

@Glorfindel I suppose the way to fix that query is to add the alternative spellings in some way?

Checking for tags where status-review is a synonym would probably made the query rather complex. Or is that a better way to go?

3 hours later…

Feeds

07:56

Q: Finding my upvotes on Data Explorer

This T-SQL finds all the questions I have answered on SO. https://data.stackexchange.com/stackoverflow/query/1779459/questions-i-have-answered I want to determine if I upvoted the question or not (as I wish to UV ALL questions that I have answered). I can't see an appropriate column Database sch...

support data-explorer api

2 hours later…

Martin

09:44

Can you help me with the OUTER APPLY part of the query, which aims to select the current status? In English, all those tags start with status- - what's the Japanese equivalent? Or should I just use a list of tags? — Glorfindel ♦ 20 mins ago

 OUTER APPLY (
    SELECT TOP 1 t.TagName
      FROM ' + quotename(name) + '.dbo.PostTags AS pt
      INNER JOIN ' + quotename(name) + '.dbo.Tags AS t
        ON pt.TagId = t.Id
      WHERE pt.PostId = p.Id
        AND (t.TagName LIKE ''status-%''
          OR t.TagName LIKE ''estado-%''
          OR t.TagName LIKE ''完了''
          OR t.TagName LIKE ''статус-%'')
  ) t

@Glorfindel I'd guess that on international sites one could get the names of status tags as those tags which have some "English named" status tag as a synonym: Spanish, Japanese, Russian

Glorfindel

@Martin thanks for the ping, adding alternative spelling is indeed the easiest, if I manage to get the Unicode handling right. (I've already prefixed the string literals with N to no avail - which is strange, because it does work here: data.stackexchange.com/rume/query/edit/1779469)

Martin

SELECT
  SourceTagName, TargetTagName
FROM TagSynonyms
WHERE SourceTagName LIKE 'status-%'

Glorfindel

yeah, that'd work but will make the query much longer

Martin

Yes, I was just about to say that it actually doesn't help much here - since this doesn't work on English speaking sites.

If nothing else, one can at least copy-paste the Japanese spellings from that query.

Glorfindel

yeah, except for me failing to understand Unicode

Martin

10:03

@Glorfindel I tried exactly the same query as you - only for Japanese - and it returns something: data.stackexchange.com/jame/query/1779472/…

Oh, I probably misunderstood what you mean.

It works when you try the per-site queries - but not in the multisite query. That's what you meant, right?

Glorfindel

yes

Martin

I have tried adding print @sql near the end of the query to see what is actually being run.

The Japanese lines do not look good there:

AND (ph.Text LIKE N'%<status-review>%'
  OR ph.Text LIKE N'%<estado-en-revisión>%'
  OR ph.Text LIKE N'%<???>%'
  OR ph.Text LIKE N'%<status-revisão>%'
  OR ph.Text LIKE N'%<??????-????????>%')

Here is a link to the query: data.stackexchange.com/meta.stackexchange/query/1779477/…

Martin

10:51

Maybe something like this would work - but it seems cumbersome

OR ph.Text LIKE N''' + nchar(0x8ABF)+nchar(0x67FB)+nchar(0x4E2D) + '''

Glorfindel

@Martin thanks, that pointed me in the right direction. When building up the @sql I need to use Unicode literals as well (see line 25).

Martin

I see that your query nor returns Japanese and Russian posts.

The line 25 was changed from FROM ' + quotename(name) + '.dbo.PostHistory AS ph to FROM ' + QUOTENAME(name) + N'.dbo.PostHistory AS ph.

(Unless I missed something.)

So adding N in several places where it was needed was the thing to do, right?

Glorfindel

Yes

2 hours later…

Martin

13:30

@Glorfindel I might have still missed some status tag on that site, but after this modification of the OUTER APPLY part I am getting something in the column which is supposed to be the current status tag. The current version of your query seems to have only status-completed. — Martin 5 mins ago

2 hours later…

Martin

15:12

@Glorfindel Thank you for your quick response. For future reference, I created a query to get a list of the translated names of the tags. data.stackexchange.com/jame/query/1779534/… — mjy 57 mins ago

@Glorfindel Regarding the Japanese tag names, Martin's comment is correct. Since these do not have a common string at the beginning, there is no way to smartly match them. — mjy 55 mins ago

@mjy thank you, I've added those to the query — Glorfindel ♦ 11 mins ago

« first day (1861 days earlier) ← previous day next day → last day (471 days later) »

all rooms