Add duration_chart endpoint for Cycle Analytics

What does this MR do?

This MR exposes Cycle Analytics duration data for the scatterplot chart. For each stage returns max 500 items (for performance reasons) in the following format:

• X axis: time
• Y axis: duration

[
  {
     "duration_in_seconds": 1234,
     "finished_at": "2012-04-23T18:25:43.511Z"
  }
]

Note: later on the schema can be extended to include MR or Issue information.

Try it

To try the feature, make sure that you enable the FF:

Feature.enable(:cycle_analytics)

Example Endpoint With a Default Stage:

-/analytics/cycle_analytics/stages/review/duration_chart?group_id=gitlab-org&created_after=2019-01-01&created_before=2019-05-01

Example Endpoint With a Persisted Stage:

In rails console:

group = Group.find_by_full_path("gitlab-org")
user = User.find_by_username("root")
Analytics::CycleAnalytics::Stages::CreateService.new(parent: group, params: { name: 'My Stage', start_event_identifier: :merge_request_created, end_event_identifier: :merge_request_merged }, current_user: user).execute

puts Analytics::CycleAnalytics::GroupStage.find_by_name("My Stage").id

Use the printed id in the URL:

-/analytics/cycle_analytics/stages/$STAGE_ID/duration_chart?group_id=gitlab-org&created_after=2019-01-01&created_before=2019-05-01

Query

The query has similar structure like other CA queries. Here we select two columns, limit the result set to 500 (will be tweaked later).

Issue based stage

SELECT ROUND(EXTRACT(EPOCH
               FROM COALESCE("issue_metrics"."first_associated_with_milestone_at", "issue_metrics"."first_added_to_board_at") - "issues"."created_at")) AS duration_in_seconds,
       COALESCE("issue_metrics"."first_associated_with_milestone_at", "issue_metrics"."first_added_to_board_at") AS finished_at
FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
INNER JOIN "issue_metrics" ON "issue_metrics"."issue_id" = "issues"."id"
INNER JOIN
  (WITH RECURSIVE "base_and_descendants" AS (
                                               (SELECT "namespaces".*
                                                FROM "namespaces"
                                                WHERE "namespaces"."type" IN ('Group')
                                                  AND "namespaces"."id" = 9970)
                                             UNION
                                               (SELECT "namespaces".*
                                                FROM "namespaces",
                                                     "base_and_descendants"
                                                WHERE "namespaces"."type" IN ('Group')
                                                  AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces".*
   FROM "base_and_descendants" AS "namespaces") namespaces ON namespaces.id=projects.namespace_id
WHERE "issues"."created_at" >= '2019-07-01'
  AND "issues"."created_at" <= '2019-11-01'
  AND ("issue_metrics"."first_added_to_board_at" IS NOT NULL
       OR "issue_metrics"."first_associated_with_milestone_at" IS NOT NULL)
  AND COALESCE("issue_metrics"."first_associated_with_milestone_at", "issue_metrics"."first_added_to_board_at") >= "issues"."created_at"
ORDER BY COALESCE("issue_metrics"."first_associated_with_milestone_at", "issue_metrics"."first_added_to_board_at")
LIMIT 500

Plan

Merge Request based stage

SELECT ROUND(EXTRACT(EPOCH
               FROM "merge_request_metrics"."merged_at" - "merge_requests"."created_at")) AS duration_in_seconds,
       "merge_request_metrics"."merged_at" AS finished_at
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
INNER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
INNER JOIN
  (WITH RECURSIVE "base_and_descendants" AS (
                                               (SELECT "namespaces".*
                                                FROM "namespaces"
                                                WHERE "namespaces"."type" IN ('Group')
                                                  AND "namespaces"."id" = 9970)
                                             UNION
                                               (SELECT "namespaces".*
                                                FROM "namespaces",
                                                     "base_and_descendants"
                                                WHERE "namespaces"."type" IN ('Group')
                                                  AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces".*
   FROM "base_and_descendants" AS "namespaces") namespaces ON namespaces.id=projects.namespace_id
WHERE "merge_requests"."created_at" >= '2019-07-01'
  AND "merge_requests"."created_at" <= '2019-11-01'
  AND "merge_request_metrics"."merged_at" >= "merge_requests"."created_at"
ORDER BY "merge_request_metrics"."merged_at"
LIMIT 500

Plan

Screenshots

Does this MR meet the acceptance criteria?

Conformity

• Changelog entry
• Documentation created/updated or follow-up review issue created
• Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• Separation of EE specific content

Availability and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
• Tested in all supported browsers

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

• Label as security and @ mention @gitlab-com/gl-security/appsec
• The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
• Security reports checked/validated by a reviewer from the AppSec team

Closes #12183