Issues Analytics: Add total number of open issues total chart (backend)
Problem to solve
Currently Issues Analytics only shows open issues. As explained in #233905 (closed), it would be valuable if it also showed total cumulative opened issues.
Proposal
We would need to create a new endpoint, or extend the
analytics_issues
-endpoint (I cannot find an existing one) to return total_issues for a given month, this requires severalcount
queries to the database with potentially no limit since we need to count "issues created before a certain date and issues either not closed at all or closed after a certain date" for each month. (source)
Total open issues = number of issues opened before the end of given month and (not closed at all OR closed after the end of given month).
Technical details
We can do 1 query for all opened issues before selected timespan and then do the math for selected period with small queries:
total_jan = (created_at <= Jan AND closed_at IS NULL OR closed_at > Jan)
total_feb = total_jan + opened_feb - closed_feb
total_mar = total_feb + opened_mar - closed_mar
....