title: Improve SQL query to find range between start and end date
body: <p>I'm working with a database called <code>international_education</code> from the <code>world_bank_intl_education</code> dataset of <code>bigquery-public-data</code>.</p>
  
  <pre><code>FIELDS            
  country_name      
  country_code      
  indicator_name    
  indicator_code    
  value   
  year
  </code></pre>
  
  <p>My aim is to plot a line graph with countries who have had the biggest and smallest change in Population growth (annual %) (one of the <code>indicator_name</code> values).</p>
  
  <p>I have done this below using two partitions finding the first and last value of the year by each country but I'm rough on my SQL and am wondering if there is a way to optimize this formula.</p>
  
  <pre><code>query = """
  WITH differences AS
      (
      SELECT country_name, year, value, 
        FIRST_VALUE(value)
        OVER (
            PARTITION BY country_name
            ORDER BY year
            RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS small_value,
        LAST_VALUE(value)
        OVER (
            PARTITION BY country_name
            ORDER BY year
            RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS large_value
      FROM `bigquery-public-data.world_bank_intl_education.international_education`
      WHERE indicator_name = 'Population growth (annual %)'
      ORDER BY year
      )
  SELECT country_name, year, (large_value-small_value) AS total_range, value
  FROM differences
  ORDER BY total_range
          """
  </code></pre>
  
  <p>Convert to pandas dataframe.</p>
  
  <pre><code>df= wbed.query_to_pandas_safe(query)
  df.head(10)
  </code></pre>
  
  <p>Resulting table.</p>
  
  <pre><code>    country_name            year    total_range value
  0   United Arab Emirates    1970    -13.195183  14.446942
  1   United Arab Emirates    1971    -13.195183  16.881671
  2   United Arab Emirates    1972    -13.195183  17.689814
  3   United Arab Emirates    1973    -13.195183  17.695296
  4   United Arab Emirates    1974    -13.195183  17.125615
  5   United Arab Emirates    1975    -13.195183  16.211873
  6   United Arab Emirates    1976    -13.195183  15.450884
  7   United Arab Emirates    1977    -13.195183  14.530119
  8   United Arab Emirates    1978    -13.195183  13.033461
  9   United Arab Emirates    1979    -13.195183  11.071306
  </code></pre>
  
  <p>I would then plot this with python as follows.</p>
  
  <pre><code>all_countries = df.groupby('country_name', as_index=False).max().sort_values(by='total_range').country_name.values
  
  countries = np.concatenate((all_countries[:3], all_countries[-4:]))
  
  plt.figure(figsize=(16, 8))
  sns.lineplot(x='year',y='value', data=df[df.country_name.isin(countries)], hue='country_name')
  </code></pre>