Transforming a stage query to get duration and finish timestamp [skip ci]experimental-cycle-analytics-chart-data-source

4 files changed, 39 insertions, 3 deletions

diff --git a/lib/gitlab/cycle_analytics/base_event_fetcher.rb b/lib/gitlab/cycle_analytics/base_event_fetcher.rb
index 2c8ecabb11f..84033e9d846 100644
--- a/lib/gitlab/cycle_analytics/base_event_fetcher.rb
+++ b/lib/gitlab/cycle_analytics/base_event_fetcher.rb
@@ -40,7 +40,7 @@ module Gitlab
       end
 
       def events_query
-        diff_fn = subtract_datetimes_diff(base_query, @options[:start_time_attrs], @options[:end_time_attrs])
+        diff_fn = subtract_datetimes_diff(@options[:start_time_attrs], @options[:end_time_attrs])
 
         base_query.project(extract_diff_epoch(diff_fn).as('total_time'), *projections).order(order.desc).take(MAX_EVENTS)
       end
diff --git a/lib/gitlab/cycle_analytics/base_stage.rb b/lib/gitlab/cycle_analytics/base_stage.rb
index 996af87f819..598f1eb79e9 100644
--- a/lib/gitlab/cycle_analytics/base_stage.rb
+++ b/lib/gitlab/cycle_analytics/base_stage.rb
@@ -4,6 +4,7 @@ module Gitlab
   module CycleAnalytics
     class BaseStage
       include BaseQuery
+      attr_reader :project
 
       def initialize(project: nil, options:)
         @project = project
diff --git a/lib/gitlab/cycle_analytics/scatterplot_chart_data_fetcher.rb b/lib/gitlab/cycle_analytics/scatterplot_chart_data_fetcher.rb
new file mode 100644
index 00000000000..56431efac41
--- /dev/null
+++ b/lib/gitlab/cycle_analytics/scatterplot_chart_data_fetcher.rb
@@ -0,0 +1,35 @@
+# frozen_string_literal: true
+
+module Gitlab
+  module CycleAnalytics
+    class ScatterplotChartDataFetcher < BaseStage
+      def initialize(stage)
+        @stage = stage
+      end
+
+      def fetch
+        ActiveRecord::Base.connection.execute(build_query.to_sql)
+      end
+
+      private
+      
+      attr_reader :stage
+
+      def build_query
+        duration = subtract_datetimes_diff(stage.start_time_attrs, stage.end_time_attrs)
+        days_took = Arel::Nodes::NamedFunction.new("EXTRACT", [Arel::Nodes::NamedFunction.new("DAY FROM", [duration])])
+
+        query = stage.stage_query(stage.send(:projects).map(&:id))
+        query.projections = []
+        query.project(days_took.as('days_took'))
+        query.project(
+          Arel::Nodes::NamedFunction.new("DATE", [
+            Arel::Nodes::NamedFunction.new("COALESCE", Array.wrap(stage.end_time_attrs))
+          ]).as('finished_at')
+        )
+        query.where(duration.gteq(zero_interval))
+        query
+      end
+    end
+  end
+end
diff --git a/lib/gitlab/database/date_time.rb b/lib/gitlab/database/date_time.rb
index 79d2caff151..be5a44fe32d 100644
--- a/lib/gitlab/database/date_time.rb
+++ b/lib/gitlab/database/date_time.rb
@@ -10,12 +10,12 @@ module Gitlab
       # Note: For MySQL, the interval is returned in seconds.
       #       For PostgreSQL, the interval is returned as an INTERVAL type.
       def subtract_datetimes(query_so_far, start_time_attrs, end_time_attrs, as)
-        diff_fn = subtract_datetimes_diff(query_so_far, start_time_attrs, end_time_attrs)
+        diff_fn = subtract_datetimes_diff(start_time_attrs, end_time_attrs)
 
         query_so_far.project(diff_fn.as(as))
       end
 
-      def subtract_datetimes_diff(query_so_far, start_time_attrs, end_time_attrs)
+      def subtract_datetimes_diff(start_time_attrs, end_time_attrs)
         if Gitlab::Database.postgresql?
           Arel::Nodes::Subtraction.new(
             Arel::Nodes::NamedFunction.new("COALESCE", Array.wrap(end_time_attrs)),