Ride-Sharing Trip and Fare Analytics Warehouse

Project roadmap

1. 01

   Design the star schema

   ~2h

   Write DESIGN.md and schema.sql: a fact_trips table (request time, pickup and dropoff times, rider, driver, city, date and hour keys, distance, duration, base fare, surge multiplier, total fare and status) and dimension tables dim_rider, dim_driver (with shift start and end times for utilization), dim_city (with time zone) and dim_date and dim_hour. Explain the grain (one row per trip) and why dimensions are separate. Done when: the schema loads and DESIGN.md explains the model.

2. 02

   Generate a large dataset

   ~2.5h

   Write a generation script (with generate_series and PL/pgSQL, or a Python script using COPY) that creates several million trips over 6 months in 5 cities with realistic patterns: rush hours, weekends, surge pricing more common at peak times and in bad weather days, cancellations, and driver shifts. Load it quickly with COPY and create indexes afterwards. Done when: the data loads, and quick counts by hour show the rush-hour pattern.

3. 03

   Compute driver utilization

   ~2.5h

   Define utilization as the share of a driver's online time spent on trips. Using shifts and trips, compute per city and hour the online minutes and busy minutes (splitting trips and shifts that cross hour boundaries, for example with generate_series over hours and overlap calculations), and the utilization rate. Use window functions to add each hour's rank within the city and a 7-day rolling average. Done when: utilization numbers are between 0 and 100% and peak at rush hours.

4. 04

   Measure surge pricing's impact

   ~2h

   Compare surge and non-surge trips per city and hour: trips, average fare, extra revenue from surge (total fare minus base fare), and cancellation rates. Estimate how much of the revenue difference comes from surge itself. Present the results as a view ordered by the biggest surge revenue. Done when: the analysis clearly shows when and where surge adds revenue.

5. 05

   Optimize the slow queries

   ~2h

   Run each report with EXPLAIN (ANALYZE, BUFFERS), record the timings, then improve them: indexes on the fact table's keys and times (consider BRIN for time), partitioning fact_trips by month, and rewriting queries that do unnecessary work. Record before and after timings and plans in README.md. Done when: README.md shows measured improvements for each report.

6. 06

   Build a materialized view

   ~1.5h

   Create a materialized view with daily metrics per city (trips, revenue, surge revenue, average wait and utilization), with a unique index so it can be refreshed with REFRESH MATERIALIZED VIEW CONCURRENTLY without blocking readers. Schedule the refresh (with pg_cron or a cron job). Done when: dashboard queries on the view return in milliseconds, and refreshes don't block reads.

7. 07

   Test the results

   ~1.5h

   Write test.sql that inserts a small hand-made set of shifts and trips with known answers (including a trip crossing an hour boundary and a cancelled trip), checks the utilization and surge queries against them, raising an error on any difference, then rolls back. Done when: psql -f test.sql passes.

Resources

• ArticleStar schema (Wikipedia)
• DocsPostgreSQL: window functions
• DocsPostgreSQL: using EXPLAIN
• DocsPostgreSQL: materialized views
• DocsPostgreSQL: table partitioning