Write a query using window functions to calculate the running total of revenue generated per host, resetting the count at the start of each calendar year.
Use the hosts and trips tables. Include completed trips only. The running total must be chronological within each host and year, with trip_id as the tie-breaker for trips on the same date.
host_id, host_name, calendar_year, trip_id, trip_date, revenue, and running_totalhost_id, calendar_year, trip_date, and trip_id| Column | Type | Description |
|---|---|---|
| host_idPK | INT | Unique identifier for a host |
| host_name | VARCHAR(100) | Display name of the host |
| Column | Type | Description |
|---|---|---|
| trip_idPK | INT | Unique identifier for a trip |
| host_id | INT | Host associated with the trip |
| trip_date | DATE | Date the trip was completed or recorded |
| revenue | NUMERIC(10,2) | Revenue attributed to the trip |
| status | VARCHAR(20) | Trip processing status |