I have a table which contain date and a metric. I want to be able to add the metric with value of the next month and also metric with the value of last year from the next month date. I have got no problem with my query, however, it took long time to run. Even after 1 hour, it still haven’t finish running.

Here’s my example of my table.

And here’s my query in Big Query

WITH first_table AS (
  SELECT current_date, next1month_date, DATE(DATETIME_SUB(next1month_date, INTERVAL 1 YEAR)) AS previous_date, metric
  FROM (SELECT current_date, DATE(DATETIME_ADD(current_date, INTERVAL 1 MONTH)) AS next1month_date, metric
    FROM `table.name`)
),

second_table AS (
  SELECT current_date, future_metric
  FROM (SELECT current_date, metric AS future_metric
    FROM `table.name`)
),

third_table AS (
  SELECT a.current_date, a.next1month_date, a.previous_date, a.metric, b.future_metric
  FROM first_table AS a FULL JOIN second_table AS b 
  ON a.next1month_date = b.new_date
),

forth_table AS (
  SELECT current_date, previous_metric
  FROM (SELECT current_date, actual AS previous_metric
    FROM `table.name`)
)

SELECT c.current_date, c.next1month_date, c.previous_date, c.metric, c.future_metric, d.previous_metric
FROM third_table AS c FULL JOIN forth_table AS d
ON c.next1month_date = d.metric_date

And the result should be something like this:

The problem here is why it take quite long for the query to be execute? Is there any problem with my query? Is there’s any, what’s the wrong in my query and how should I fix it?