Sample Header Ad - 728x90

Monthly Growth - Bigquery

0 votes
1 answer
678 views
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: !Referenced image I do not know which part of my query is wrong. Kindly help me with this. My desired output example: enter image description here Thank you!
Asked by snsd94 (1 rep)
Feb 3, 2021, 05:07 PM
Last activity: Jun 9, 2025, 07:08 AM