Analyze User Ride Activity with SQL

Use the following tables to answer four SQL analysis tasks.

rides(ride_id, ride_date, ride_rating, user_id)
users(user_id, city)

Constraints & Assumptions

• Write ANSI-style SQL and state any date functions that depend on your dialect.
• user_id is unique in users; ride_id is unique in rides.
• ride_date is a timestamp, and ride_rating may be null.
• A user with no matching row in rides has taken zero rides.
• An active month is a distinct calendar month in which a user completed at least one ride; inactive calendar months do not advance the active-month rank.

Clarifying Questions to Ask

• Is the seven-day window ending July 7 inclusive, and in which time zone is ride_date interpreted?
• Should a null rating be ignored by the database average?
• What output format should represent a calendar month?
• Should cities with no qualifying users appear with a zero count?

Part 1: Inactive Users in a Seven-Day Window

Count users with zero rides during the inclusive seven-day window ending July 7, 2024. Include users who have never taken a ride.

What This Part Should Cover

• A left join or NOT EXISTS condition that preserves users without rides.
• Date predicates placed so they do not accidentally turn an outer join into an inner join.
• Clear inclusive-start and exclusive-end boundaries.

Part 2: Monthly Ride Aggregation

For each calendar month, return the total number of rides and average non-null rating rounded to two decimals. Sort months from newest to oldest.

What This Part Should Cover

• Calendar-month truncation, ride-level counting, null-aware averaging, rounding, and descending sort.

Part 3: Low-Frequency Users by City

For each city, count distinct users who have taken zero or one ride across all available history. Include users who have never taken a ride.

What This Part Should Cover

• A user-level ride count built without losing zero-ride users, followed by a city-level aggregation.
• Protection against counting rides rather than qualifying users.

Part 4: Ratings in the First and Third Active Months

For each city, compute the average ride rating separately for users' first and third active months. The final average must be across all qualifying rides, not an average of user-level or month-level averages.

What This Part Should Cover

• Deduplication to one user-month before ranking active months.
• A per-user chronological rank that skips inactive calendar months.
• A join back to ride-level data before calculating the final weighted average.