5
$\begingroup$

Movies&TV query

I found this query on Stack Exchange Data Explorer Movies&TV wrote that shows the monthly frequency input of id my movie questions and total questions on the site. As you can see on the resulting graph, they solved their issue at 2018.

Movies&TV graph.

enter image description here

Earth Science query

I changed from Movies&TV code %identify% --> %identification% and selected Earth Science on the menu and aparently, the query works for us.

Here you can run it ("Run Query" button) to generate the data and the graph. There should be a way to make queries for different periods but I don't know how. Stack has a tutorial to use the tool.

select 
    dateadd(month, datediff(month, 0, p.CreationDate), 0) mo,
    count(distinct p.id) as QuestionCount,
    count(distinct case when p.tags like '%identification%' then p.id else null end) as QuestionCountID,
    count(v.id) as VoteCount,
    sum(case when p.tags like '%identification%' and v.id is not null then 1 else 0 end) as VoteCountID
from postswithdeleted p
  left outer join votes v
    on v.postid = p.id
        and v.votetypeid in
    (
        4, 6, 7, 10, 11, 12, 15, 16
    )
where p.posttypeid = 1
group by dateadd(month, datediff(month, 0, p.CreationDate), 0)
order by dateadd(month, datediff(month, 0, p.CreationDate), 0)

Earth Science graph (23/11/2019).

enter image description here

It looks like we reduced the input of id questions around september/october, when we closed them.

Let's select only id questions (deleting lines 3 and 5 in the query and running it), wich reduces y-scale.

enter image description here

What I see is we started to have a trouble sometime between 2016 and 2018, probably because we started to appear first at Google for searches as "identify my rock".

We have broken the tendency closing all the questions, apparently users coming from Google have understood it; good news!

Let's see if deleting them we completely solve the issue as Movies&TV did!

$\endgroup$
7
  • 2
    $\begingroup$ Good work! FYI: A SE employee has promised to help with auto-deleting any closed identification-request questions, but they may have more urgent issues to deal with so it's unsure if/when they get around to doing it. $\endgroup$
    – gerrit Mod
    Nov 23, 2019 at 12:27
  • $\begingroup$ Question: does this include deleted questions? $\endgroup$
    – gerrit Mod
    Nov 23, 2019 at 13:31
  • $\begingroup$ @gerrit I think, but not sure, the query is for posted questions taged with identification-request and the votes. Then a deleted question counts in the data, it was published and the SQL query returns published questions that month. $\endgroup$
    – user18261
    Nov 23, 2019 at 13:32
  • $\begingroup$ But anyhow the trouble looks 95% solved. Deleting them probably as happened on Movies&TV the questions will disappear. Also they have blacklisted the tag. I don't know what that exactly means. There is a warning on M&TV if you try to ask what is this movie. $\endgroup$
    – user18261
    Nov 23, 2019 at 13:35
  • $\begingroup$ We could blacklist the tag, but I worry that people will just cause ID questions without tagging them as such, which will make it harder for us to find, close, and delete them. $\endgroup$
    – gerrit Mod
    Nov 23, 2019 at 13:38
  • $\begingroup$ @gerrit I don't know, you mods :) but we have also clouds id questions, that are not off-topic. your choise, but don't worry a lot, apparently the issue with I found this white stone is being solved, past months we had 50 questions and we are in less than 10 $\endgroup$
    – user18261
    Nov 23, 2019 at 13:41
  • $\begingroup$ @gerrit I think they should be bussy, she is gonna sue them with users money! But +2k users are deleting the questions, we will have the issue solved soon, there are only 200 questions from the initial +400. You migth don't need to ban the tag, but at least add a clear warning in uppercase WE DON'T ID ROCKS OR MINERALS. $\endgroup$
    – user18261
    Nov 24, 2019 at 9:41

0

Reset to default

You must log in to answer this question.