« first day (716 days earlier)      last day (1381 days later) » 

Feeds
Feeds
3:02 PM
1
Q: Counting the number of deleted answers on questions that have been protected

RubiksmooseSo, I'm continuing my dig into the protection feature on my site for a data deep dive and I'm getting stuck trying to find this data. I'm looking this time for the list of all questions that have been protected at any point on the site (whether or not they currently are) along with the number of ...

support data-explorer
 
 
1 hour later…
Rubiksmoose
Rubiksmoose
4:21 PM
Anybody have an idea of what I'm doing wrong with my Inner Joins here?
I'm trying to find the number of questions that have been unprotected at any point, that have also had an answer with a spam flag.
I'm actually hoping this turns out to be 0 results, but when I changed the unprotected to protected I'd expect lots of results, but still get 0. So I assume something is wrong.
 
ArtOfCode
ArtOfCode
4:37 PM
@Rubiksmoose Answers can't be protected - you have a WHERE that's specifying answers only
 
Rubiksmoose
Rubiksmoose
@ArtOfCode doh!
 
ArtOfCode
ArtOfCode
Needs a subquery, most likely
SELECT (stuff) FROM posts WHERE (has been unprotected) AND (Id IN (SELECT ParentId FROM Posts WHERE (is answer) AND (has been spam flagged))
 
Rubiksmoose
Rubiksmoose
Ahhh ok I think that makes sense.
Alright edit made, but still getting same result.
I'm assuming those joins are necessary since I need access to all those tables.
 
ArtOfCode
ArtOfCode
You want the joins to be in the query they relate to - which probably means putting the votes join in the inner query
 
Rubiksmoose
Rubiksmoose
oh ho! I think we may be working now :)
 
ArtOfCode
ArtOfCode
4:50 PM
\o/
 
Rubiksmoose
Rubiksmoose
I think the last thing that needed specifying was FROM PostsWithDeleted p instead of just FROM PostsWithDeleted?
It seems to be giving me what I want regardless :)
Thanks a ton for your help.
 
ArtOfCode
ArtOfCode
No worries :)
 

« first day (716 days earlier)      last day (1381 days later) »