Bitcoin Data Transformation

Create a view in BigQuery with the following SQL code:

SELECT DATE(date) as date,
  open,
  high,
  low,
  close,
  volume_btc,
  volume_usdt,
  tradecount,
  (high + low) / 2 as mid_price,

  -- assign the most recent price into a column --
  CASE when RANK() OVER (
        ORDER BY date desc
    ) = 1 then ((high + low) / 2) else 0 end as latest_price,

  -- assign the price from the day before the most recent price into a column --
  CASE when RANK() OVER (
        ORDER BY date desc
    ) = 2 then ((high + low) / 2) else 0 end as price_yesterday,

  -- assign the price from 7 days before the most recent price into a column --
  CASE when RANK() OVER (
        ORDER BY date desc
    ) = 8 then ((high + low) / 2) else 0 end as price_7_days_ago,

  -- assign the price from 30 days before the most recent price into a column --
  CASE when RANK() OVER (
        ORDER BY date desc
    ) = 31 then ((high + low) / 2) else 0 end as price_30_days_ago,

  -- assign the price from 90 days before the most recent price into a column --
  CASE when RANK() OVER (
        ORDER BY date desc
    ) = 91 then ((high + low) / 2) else 0 end as price_90_days_ago,

  -- assign the price from 90 days before the most recent price into a column --
  CASE when RANK() OVER (
        ORDER BY date desc
    ) = 181 then ((high + low) / 2) else 0 end as price_180_days_ago,

  -- assign the price from 365 days before the most recent price into a column --
  CASE when RANK() OVER (
        ORDER BY date desc
    ) = 365 then ((high + low) / 2) else 0 end as price_1_year_ago
FROM `marquin-personal-tools.marquinsmith_dot_com.bitcoinusdprice`
ORDER BY date desc