User Ride Trends with Windows | Dataford Interview Questions

Your question is User Ride Trends with Windows. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

RideFlow wants to analyze how rider activity changes month over month. Write a PostgreSQL query to calculate user-level ride trends over time using window functions.

Requirements

Return one row per user per month for completed rides only.
For each user-month, calculate:
- total completed rides
- total completed fare
- previous month's completed ride count
- month-over-month change in completed rides
- a trend label: growing, declining, or flat
Include users even if they have no matching city record.
Order the final output by user_id and month.

Schema

users

Column	Type	Description
user_idPK	INT	Unique rider identifier
user_name	VARCHAR(100)	Rider full name
signup_date	DATE	Date the rider signed up
city_id	INT	Home city reference

cities

Column	Type	Description
city_idPK	INT	Unique city identifier
city_name	VARCHAR(100)	City name

rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
user_id	INT	Rider who took the trip
ride_date	DATE	Date of the ride
status	VARCHAR(20)	Ride status such as completed or cancelled
fare_amount	NUMERIC(10,2)	Fare charged for the ride