We want to know which electric vehicles have made the most year$-$on$-$

year improvement in electric range. Write a query using common table ex$-$

pressions to return the vehicles $($make$,$ model$)$ that made the most single year

improvement to their electric range. Output just the top $10$ largest year$-$on$-$

year improvements. The output table should have at least seven columns $($extra

ev$\_$wa

$$ Columns $(14)$

id

vin

county

city

state

postal$\_$code

model$\_$year

make

model

ev$\_$type

cafv$\_$eligibilit.

electric$\_$range

base$\_$msrp

census$\_$tractcolumns are okay$)$: make, model, year$1,$ year$2,$ range$1($range in year$1),$ range$2$

$($range in year$2),$ and diff $($difference in the range between the two years$).$

Note: A single year improvement is made between adjacent years, e$.$g$.,$ the

improvement between $2022$ and $2023.$ Hypothetically, if Toyota Rav$4$s had an

electric range of $50$ in $2022$ and $60$ in $2023,$ the year$-$on$-$year improvement would

be $10.$ If the range were $60$ in $2022$ and $50$ in $2023,$ the year$-$on$-$year improvement

would be $-10($not an improvement at all$).$

Hint: This query should build upon your previous query. If you are having

difficulty with the previous query, you can still get partial credit on this problem

by writing the rest of the query assuming your query from problem $1$ is correct.

Next, we want to know whether there is a relationship between median household

income and prevalence of electric cars. We will use census tract as a minimum geo$-$

graphic unit. Median household income per census tract can be found in the $$income$$

table, with each tract identified by the census geo ID$.$ This geo ID maps to the census

tracts in the $$ev$\_$wa$$ table.

However, the census tract identifier from the $$ev$\_$wa$$ table takes a different form than

the census$\_$geo$\_$id from the $$income$$ table. You will need a new function SUBSTRING

to join the two tables in this database on census tract. The following example query

joins these two columns together by taking a substring of census$\_$geo$\_$id and match$-$

ing it against census$\_$tract. A few rows of the resulting join are provided for reference.

SELECT e$.$census$\_$tract, i$.$census$\_$geo$\_$id

FROM ev$\_$wa e

JOIN income i ON SUBSTRING$($i$.$census$\_$geo$\_$id$,8)=$ e$.$census$\_$tract

LIMIT $5$;

census$\_$tract $|$ census$\_$geo$\_$id

$--------------+--------------------$

$08005006861|14000$US$08005006861$

$53029971302|14000$US$53029971302$

$06031980000|14000$US$06031980000$

$06073018509|14000$US$06073018509$

$06001423200|14000$US$06001423200$