Annual Station Revenue Growth

A bike-sharing network collects a fare whenever a rider begins a trip. Its planning team wants to compare each station's annual fare revenue with its previous recorded year.

Table: ride_payments

+------------+---------------+
| Column Name| Type          |
+------------+---------------+
| payment_id | integer       |
| station_id | integer       |
| fare_amount| decimal(12,2) |
| paid_at    | timestamp     |
+------------+---------------+
+------------+---------------+
| Column Name| Type          |
+------------+---------------+
| payment_id | integer       |
| station_id | integer       |
| fare_amount| decimal(12,2) |
| paid_at    | timestamp     |
+------------+---------------+

payment_id is the primary key. Each row records one fare paid for a trip that started at a station.

For every station and calendar year, calculate the total fare_amount. Then compare that total with the same station's previous recorded year, meaning the preceding row when that station's yearly totals are ordered by year. If a station has no activity in an intervening calendar year, compare with its most recent earlier year that does have data.

Return these columns:

• year
• station_id
• curr_year_revenue, rounded to 2 decimal places
• prev_year_revenue, rounded to 2 decimal places
• yoy_rate, calculated as (curr_year_revenue - prev_year_revenue) / prev_year_revenue * 100 and rounded to 2 decimal places

For a station's first recorded year, return NULL for both prev_year_revenue and yoy_rate. If the previous year's revenue is 0, return NULL for yoy_rate.

Order the results by station_id ascending, then year ascending.

Example

Input:

ride_payments table:
+------------+------------+-------------+---------------------+
| payment_id | station_id | fare_amount | paid_at             |
+------------+------------+-------------+---------------------+
| 1          | 101        | 40.25       | 2023-02-14 08:30:00 |
| 2          | 101        | 59.75       | 2023-09-02 17:15:00 |
| 3          | 101        | 150.00      | 2024-06-10 12:00:00 |
| 4          | 101        | 120.00      | 2025-03-21 09:45:00 |
+------------+------------+-------------+---------------------+
ride_payments table:
+------------+------------+-------------+---------------------+
| payment_id | station_id | fare_amount | paid_at             |
+------------+------------+-------------+---------------------+
| 1          | 101        | 40.25       | 2023-02-14 08:30:00 |
| 2          | 101        | 59.75       | 2023-09-02 17:15:00 |
| 3          | 101        | 150.00      | 2024-06-10 12:00:00 |
| 4          | 101        | 120.00      | 2025-03-21 09:45:00 |
+------------+------------+-------------+---------------------+

Output:

+------+------------+-------------------+-------------------+----------+
| year | station_id | curr_year_revenue | prev_year_revenue | yoy_rate |
+------+------------+-------------------+-------------------+----------+
| 2023 | 101        | 100.00            | NULL              | NULL     |
| 2024 | 101        | 150.00            | 100.00            | 50.00    |
| 2025 | 101        | 120.00            | 150.00            | -20.00   |
+------+------------+-------------------+-------------------+----------+
+------+------------+-------------------+-------------------+----------+
| year | station_id | curr_year_revenue | prev_year_revenue | yoy_rate |
+------+------------+-------------------+-------------------+----------+
| 2023 | 101        | 100.00            | NULL              | NULL     |
| 2024 | 101        | 150.00            | 100.00            | 50.00    |
| 2025 | 101        | 120.00            | 150.00            | -20.00   |
+------+------------+-------------------+-------------------+----------+

Explanation:

The two payments from 2023 total 100.00, so that becomes station 101's first annual value. Revenue rises from 100.00 to 150.00 in 2024, a 50.00% increase. It then falls from 150.00 to 120.00 in 2025, a -20.00% change.