How can I track the number of unanswered posts over time?

[NicolaC]

I would like to compare how many topics went unanswered in July 2018 versus how many went unanswered in July 2019.

[VikasB]

@NicolaC 
The below queries can help you to get the topics count which were posted in a specific month but not resolved yet. 
For July 2019

Show more

select * from messages where conversation.solved=false and depth=0 and post_time > 2019-07-01T00:00:00.000-00:00 and post_time < 2019-07-31T23:59:59.999-00:00

For July 2018

Show more

select * from messages where conversation.solved=false and depth=0 and post_time > 2018-07-01T00:00:00.000-00:00 and post_time < 2018-07-31T23:59:59.999-00:00

You can run it you have studio/admin access. 
Studio > API Browser 
Here is the screenshot for your reference https://prnt.sc/px8v6p

[NicolaC]

Thanks VikasB.

We only have Studio on staging. Is there any way to do this on the live site?

Thanks

[VikasB · Accepted solution]

@NicolaC 
You can use the below URL to run it for production. You need to replace the base URL with your community base URL

Show more

community.YourCommunity.com/api/2.0/search?q=select * from messages where conversation.solved=false and depth=0 and post_time > 2019-07-01T00:00:00.000-00:00 and post_time < 2019-07-31T23:59:59.999-00:00

[NicolaC]

Thank you!! So helpful.

[PAULEM]

Hi @VikasB 

I have tried this and it works a treat - thanks for the tip!  When I "select * from..." I get a size of 25 but when I changed the query to "select count(*) from..." I get a count of 969.  Do you know why this is? 

[VikasB]

@PAULEM 
By default, API returns 25 results. You can pass a specific limit up to 1000 to get more results. 

Show more

community.YourCommunity.com/api/2.0/search?q=select * from messages where conversation.solved=false and depth=0 and post_time > 2019-07-01T00:00:00.000-00:00 and post_time < 2019-07-31T23:59:59.999-00:00 limit 1000