I need your help again. I need to calculate the monthly growth for the trips.
The query that I have written is below:
I do not know which part of my query is wrong. Kindly help me with this.
My desired output example:
Thank you!
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:
!

Asked by snsd94
(1 rep)
Feb 3, 2021, 05:07 PM
Last activity: Jun 9, 2025, 07:08 AM
Last activity: Jun 9, 2025, 07:08 AM