« first day (354 days earlier)      last day (1764 days later) » 

Martin
Martin
7:51 AM
@SonictheAnonymousWizHog This query was what I was able to create: data.stackexchange.com/math/revision/1077404/1331059/…
Even if we ignore the fact that there might be some deleted posts which might change the counts and that the query considers posts with multiple additions and removals only once, there is also the problem that the query is rather slow.
For bit tags, these two lines are causing it to be slow:
(SELECT COUNT(*) FROM datesaddedremoved_cte d2 WHERE d2.FirstAddedDate<=d1.LastRemovedDate) AS CountAddedBefore,
(SELECT COUNT(*) FROM datesaddedremoved_cte d2 WHERE d2.LastRemovedDate<=d1.LastRemovedDate) AS CountRemovedBefore
Maybe somebody can find a better way to make the same query.
Here is some testing.
generalization: edits, counts, removals
significance: edits, counts, removals
surjective: edits, counts, removals
But this fails miserably on the tags which had many questions - time runs out.
abstract-algebra: edits show over 3500 posts, counts and removals reach timeout.
Maybe instead of SELECT COUNT(*) a more efficient solution would be first creating a table using INNER JOIN ON d2.FirstAddedDate<=d1.LastRemovedDate and similarly for the other condition?
 
Martin
Martin
8:14 AM
Still, I get timeout on abstract-algebra: data.stackexchange.com/math/revision/1077409/1331068/…
Maybe somebody knows suitable test cases which are a bit smaller.
 
 
6 hours later…
rene
rene
2:19 PM
@Martin I created a temptable for the first CTE and that seems to be enough to resolve the time bottleneck ... I didn't even had to add an index ...
 
Martin
Martin
BTW it's bad how forgetful I am.
When I wanted a large tag on Mathematics that was removed I should have tried (algebra) and not (abstract-algebra). (The latter still exists.)
It seems that there were some 1500 posts with the algebra tag (which are not deleted by now): data.stackexchange.com/math/revision/1077119/1330654/…
This tag was removed a few times and eventually it was blacklisted.
For algebra I still get "Exectution Timeout Expired" data.stackexchange.com/math/query/1077506/…
Perhaps it would be good to know whether some of the queries above helped for the tags that Sonic the Anonymous WizHog was interested in.
I have tried to do the same thing as in your query with two tables: data.stackexchange.com/math/revision/1077518/1331189/…
The results of the query are consistent with my recollection that the tag was first removed in February 2012.
 
rene
rene
2:50 PM
cool, so we now learned that for timeseries queries we better first shape a couple of #temptables and then work with those
 
 
7 hours later…
Sonic the Anonymous WizHog
Sonic the Anonymous WizHog
9:42 PM
@rene I get a timeout when I try to run it for the specific tag I'm trying to have blacklisted: the [google] tag on Super User.
 

« first day (354 days earlier)      last day (1764 days later) »