BigQuery: GitHub回购同比增长

WITH yearly_data AS (
  SELECT
    EXTRACT(YEAR FROM repurchase_date) AS year,
    SUM(shares * price) AS total_repurchase_value
  FROM
    `your_dataset.github_repurchases`
  GROUP BY
    year
),
year_over_year_growth AS (
  SELECT
    y1.year,
    (y1.total_reparging_value - IFNULL(y2.total_repurchasing_value, 0)) / IFNULL(y2.total_repurchasing_value, 1) * 100 AS growth_percentage
  FROM
    yearly_data y1
  LEFT JOIN
    yearly_data y2
  ON
    y1.year = y2.year + 1
)
SELECT
  *
FROM
  year_over_year_growth
ORDER BY
  year;