0

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:

1

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!

1
  • what is your desired output
    – eshirvana
    Feb 3 at 17:44
0

and if you are calculating monthly growth, why aren't you calculating the number of trips in eahc month :

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','Month' ORDER BY 'Year', 'Month' desc))
                 /lag(COUNT(bikeid),1) OVER (PARTITION BY 'Year','Month' ORDER BY 'Year', 'Month' desc)) ||'%' 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
4
  • isn't my query calculate for each month? in the table, there are several years and months.
    – snsd94
    Feb 3 at 17:52
  • my desired output example: Year Month Trips Growth 2016 12 1750 2016 11 1500 -14% 2016 10 1000 -33%
    – snsd94
    Feb 3 at 17:52
  • did you try my query ?
    – eshirvana
    Feb 3 at 18:05
  • Yes. I've tried your query above, but the outcome quite similar to my previous one
    – snsd94
    Feb 4 at 4:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.