We're starting to track "unanswered topics" as a KPI. Is it possible to grab this historically? (e.g.: "For each forum, return the count of unanswered topics on February 1st, 2024")
I'm okay with a bit of trickery, but am currently at a loss. Any pointers would be appreciated!
Aah, I see what you are getting at, well, that's a probably a bit more complex to figure out, but I think it should be doable. I think, I would get all topics together with their associated replies in your date range. I'd probably transform the data into an array of (nested) objects, where each object is a topic/discussion and the replies are "within" the object, so everything that belongs together is already where it should be before analysis. Then I'd loop through the discussions, filtering out all replies that were posted outside your date-range of interest, actually I would make a copy of the original nested objects-array and literally delete all the reply objects from it if they are not of "interest". After you have done that, simply loop again and filter out topics/discussions that do not have any replies left.
🤯 while I am still trying to wrap my mind around this, I think it sounds feasible.
I'm sorry for the delayed response, I missed your reply until now, but still would like to work this out. Thanks!
keithkelly The below query might be helpful to you to get the unanswered topics count.
select count(*) from messages where conversation.solved=false and board.id="boardid" and depth=0 and post_time > 2024-02-01T00:00:00.000-00:00 and post_time < 2024-02-01T23:59:59.999-00:00
yes, this should work, but I think >=, <= should be used instead of >,< for post_time constraint
Well: Is "unanswered" the same as "unsolved"? Not sure, maybe keithkelly could clarify?
In my view "unanswered" is a topic that hasn't received any replies where a topic can have replies but none of these replies were marked as a solution, e.g. is "unsolved" (which would be what the query above would return).
To get back topics with no replies you'd want to use replies.count(*) = 0, e.g. something like this:
SELECT count(*) FROM messages WHERE depth = 0 AND replies.count(*) = 0
Yes, I'm referring to topics where replies = 0. Or, "response-less" topics.
But rather than asking "How many topics - posted in January - don't have a response?" I'd like to ask "On January 1, how many topics on the Policy board were responseless?"
