I need your help again. I need to calculate the monthly growth for the trips.
The query that I have written is below:
SELECT EXTRACT(YEAR FROM DATE (stoptime)) AS Year, EXTRACT(MONTH FROM DATE (stoptime)) AS Month, COUNT(bikeid) AS Trips, ROUND(100* (COUNT(bikeid)- LAG(COUNT(bikeid),1,0) OVER (PARTITION BY 'Year' ORDER BY 'Year', 'Month'))/lag(COUNT(bikeid),1) OVER (PARTITION BY 'Year' ORDER BY 'Year', 'Month')) ||'%' AS growth FROM `bigquery-public-data.new_york_citibike.citibike_trips` WHERE end_station_name LIKE '%University%' GROUP BY Year, Month ORDER BY Year DESC, Month DESC
However, the query below cannot give me the right output. The output from the query above as below:
I do not know which part of my query is wrong. Kindly help me with this.
My desired output example: