Starred posts | chat.meta.stackexchange.com

rene

Nov 15 09:16

@Martin escaping rules are tricky and not consistent across programming languages / dialects. I always find myself verifying whether I made the right assumptions.

rene

Nov 13 19:35

@Martin yes, [ has a special meaning. You use it as a one of range or in set: learn.microsoft.com/en-us/sql/t-sql/language-elements/…. So body like '[bc]ar' will find posts with bar and car. You need to escape [ by enclosing it in brackets: [[] note that ] doesn't need escaping. See learn.microsoft.com/en-us/sql/t-sql/language-elements/…

Martin

Jul 28 08:48

BTW this room is now six years old.

rene

Jul 24 17:42

@ТymaGaidash because it is a security incident and those are better handled in private until the issue has been resolved. See also: stackexchange.com/about/security

rene

Jul 14 06:50

Looking at initialized and made_available data.stackexchange.com/stackoverflow/query/1851434 I kind of reversengineer that there must have been 7 jobs running in parallel process a single site for the refresh. Following each made_available time and the next closest initialized indicates which job was involved. That worked till 1:43 and then restarted an hour later with just one job.

Aaron Bertrand

Apr 12 21:41

TIL this room existed

2

rene

Jul 1 20:47

@Martin I know a ticket is logged when the job fails but I'm also wondering if Aaron implemented some retry logic because some failures were just due to some race condition where restarting the job would resolve the issue. That doesn't need an SRE, that can be done automatically. Maybe that is in-place now.

rene

Jun 18 17:55

@Martin in that query you only need one join with posttags and tags because you can do tagname in ('tag1', 'tag2'). That expands to tagname='tag1' or tagname = 'tag2'.

rene

May 18 18:50

Only one index on Id, so doing a join on votes.userid = p.owneruserid and votetpeid =8 will be a full tablescan

rene

May 18 18:36

@Martin I initially had votetypedid = 8 (start bounty) in it but that would only substract reputation for bounties on your own question. I'm not sure if this needs another join with votes for just votetypeid=8 or if the current join clause can be made more clever.

rene

Apr 26 13:06

@Martin it loos like all but PostHistoryTypeId = 9 (that is tag rollback) have been corrected: data.stackexchange.com/meta.stackexchange/query/1837958 and I doubt there is work in progress on that. Given the man in charge is active, lets give him a ping /cc @AaronBertrand

rene

Apr 19 13:21

@AaronBertrand this one: data.stackexchange.com/stackoverflow/query/1239509/…

Aaron Bertrand

Apr 19 13:18

@rene Thanks! This is useful. Do you have a link handy for the query you used to use to see the status of SEDE refresh?

rene

Apr 14 06:06

@AaronBertrand we are impressed: data.stackexchange.com/stackoverflow/query/1834765 that is awesome!

rene

Apr 12 07:01

in Tavern on the Meta, 9 hours ago, by Aaron Bertrand

need a couple of beta testers

rene

Mar 4 21:28

@KarlKnechtel No, [Post Link] has an insane post processing step server side that involves running an extra query. See meta.stackexchange.com/questions/284040/…

rene

Mar 4 10:50

@KarlKnechtel this is the only way I can get it going: data.stackexchange.com/stackoverflow/query/… remember that [Post Link] is expensive, specially when you have > 50,000 rows

rene

Jan 20 08:24

also note that format is a relatively recent addition to T-SQL. It is kind-of convenient for me because it is the same as what the .NET Framework offers in the ToString formatting for datetime type. And C# / .Net is my "native" programming language.

rene

Jan 20 07:32

DateTimes are tricky because in some situations a server datetime setting might return different results for the same query. Not a problem within SEDE but something to keep in mind if you apply that trick on a SQL database that has a different setting.

rene

Jan 20 07:24

@Martin that ... is creative. here are some other alternatives: data.stackexchange.com/mathoverflow/query/1815675

rene

Sep 13, 2023 17:48

@Mithical I think your work around is the only reliable way to achieve that.

Mithical

Sep 12, 2023 11:47

Hopefully :) Restricting it to 5k might work, but that makes it more annoying to keep tabs on the total numbers. I was hoping to get everything into one query instead of two or three.

Mithical

Sep 12, 2023 10:34

Anyone know how I can make sure that a query result is sortable by the different columns? I'm not getting the sort options if I take out the ORDER BY clause, but I'd like it to be sortable by either the view count or the creation date. (I resorted to an ORDER BY ##ColumnID## DESC for the moment but I'd prefer to have the results themselves just easily sortable.)

rene

Sep 11, 2023 20:28

relevant in this context as well: meta.stackexchange.com/questions/312509/…

Martin

Sep 1, 2023 10:57

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

Glorfindel

Sep 1, 2023 10:54

@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).

Glorfindel

Sep 1, 2023 09:56

@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)

M--ßţřịƙïñĝ

Jun 23, 2023 02:20

ok, idk what's going on. I can download the results as CSV, but nothing on the page: i.sstatic.net/RMPpe.png could be my userscripts or something. Anyway, I can work with the csv. ignore my rants above.

M--ßţřịƙïñĝ

Jun 23, 2023 02:07

wait nothing works, is SEDE down or something?

GS - Apologise to Monica

Jun 7, 2023 21:05

Hello! If I'm doing a cross-site query using sys.databases tricks, is there a standard way to construct the URL of the relevant site?

Sonic the Anonymous Hedgehog

Apr 11, 2023 19:13

@rene I'm currently working through a course learning SQL. I don't have any right now, but if I do have questions later it'd be nice if you'd help me with them.

Feeds

Jan 21, 2023 22:19

2

Q: The current SEDE refresh will be delayed until at least Monday, Jan 23rd

This morning we had a failure on a backup share that won't be fully resolved until Monday. We've immediately re-routed backups to multiple secondary locations, so that concern has been mitigated. Unfortunately, the Data Explorer refresh job and the processes that feed into it rely on the content ...

rene

Dec 7, 2022 18:28

@Martin I clicked ... facepalm

Feeds

Dec 4, 2022 13:20

1

Q: SEDE refresh triggered twice today

As we all know, SEDE is refreshed every Sunday morning, but immediately after it finished (Stack Overflow is the last site, always somewhere before noon UTC), it seems to have triggered a new refresh cycle, as this query shows: (Solana is the newest site and hence the first one to be refreshed)....

bug data-explorer

Glorfindel

Dec 4, 2022 13:07

Either the order of the sites being refreshed has changed, or it's rather slow today and I don't understand the system: data.stackexchange.com/stackoverflow/query/1682315

Feeds

Sep 17, 2021 20:08

11

Q: Planned SEDE maintenance scheduled for Sept 22 and 24, 2021 at 01:00-04:00 UTC (Sept 21 and 23 9:00 pm-midnight EDT)

Due to a planned network upgrade, data.stackexchange.com (a.k.a. SEDE or the Stack Exchange Data Explorer) may be unavailable for up to 6 hours this week, likely considerably less. The first window will be Tuesday, Sept 21, 2021 between 9 pm and midnight US/Eastern (a.k.a. NYC-time). The second...

discussion featured data-explorer announcements maintenance

2

rene

Nov 22, 2022 18:08

@starball no, it means it was deleted before it was closed, or it was an answer.

starball

Nov 21, 2022 23:23

If a question has no ClosedDate, but has DeletionDate, does that mean it was deleted by roomba?
I was just making a query and noticed this ("deleted2") https://data.stackexchange.com/stackoverflow/query/1674861

rene

Nov 20, 2022 19:47

@Martin yes, that makes sense. I assumed using the log10 would help me here as there would be many posts with less than 1000 chars while I am trying to analyze post size at the tail end of that graph. Your queries at least shows/explains to me the behavior, so that is helpful. Thanks.

rene

Nov 20, 2022 14:24

@Martin if you're around, I don't understand why rouding my log10 of text lengths get these weird jumps: data.stackexchange.com/math/query/1674211?decimals=3#graph it seems to lessen if I round to 4 decimals but there is either something wrong with how I think I can use that round function or there is a strange side-effect with taking the log. I'm trying to get a kind of "distribution" of post lenght.

rene

Oct 29, 2022 06:08

LEFT OUTER JOIN Badges b on b.UserId = Users.id and b.name = 'Informed'
WHERE b.Id IS NULL

Safdar Faisal

Oct 28, 2022 17:32

How would I check for everyone who doesn't have a certain badge? I'm trying to make a SEDE query to find everyone who posted their first question without having the informed badge.

rene

Oct 8, 2022 06:03

What is the functional relevance of MarkdownConcensusDescription, where is it used / shown and why is it called (sic)

rene

May 18, 2020 19:50

Sede Refresh status query

3

Feeds

Jun 3, 2021 16:58

0

Q: Trivial **Pet**suit Event

The Stack Exchange Data Explorer (SEDE) offers many interesting insights into our site that would be impossible to gain otherwise. For one month (June 2021) we want to extract one interesting or unusual fact about the site each day.

discussion data-explorer event

2

tripleee

Sep 17, 2022 09:39

screen shot with freehand circle to help future visitors figure out where to look i.sstatic.net/OZuui.jpg

Taryn

Feb 29, 2020 13:15

@rene it's back for now - meta.stackexchange.com/a/344297/164200

3

rene

Jun 18, 2022 14:11

@Martin I expect count to be there for performance reasons as count is shown on the tag on-hover dialog, that gets loaded dynamically from the server. I know this then leaves the question: why isn't the watcher/followers count in the public schema then? Not a clue. It might well be in the real production schema of the db. getting the follower count can be done but a bit more cumbersome: meta.stackexchange.com/q/274618

Glorfindel

Jun 14, 2022 10:28

5

Q: Why can't I sort SEDE results for some queries?

I was just checking out who earned the most of the new badges that were announced this week. I have a SEDE query for this which sorts by default on the number of sites you earned a badge. I noticed that when I run it for the harder badge, Lifeboat (awarded to 1089 users on the network), I can cli...

feature-request support data-explorer sorting

Martin

Jun 2, 2022 16:06

I have tried to find what are the oldest suggested edits on metas which are still pending: data.stackexchange.com/stackoverflow/query/1601770/…