codeseed
Sign in
Explore Projects
SQLadvancedaiAI generated

Ride-Sharing Trip and Fare Analytics Warehouse

Build a star-schema-style warehouse of riders, drivers, trips, and fares, then write analytical queries computing surge-pricing revenue impact and driver utilization rates by city and hour. Learners practice dimensional modeling, window functions, and query performance tuning on large synthetic datasets.

Estimate
~14.5h
Steps
6
Completed by
0
Proposed by
codeseed.app

PostgreSQL · pgAdmin · EXPLAIN ANALYZE

Sign in to claim this project

Project roadmap

  1. 01

    Design the fact and dimension schema

    ~2.5h

    Model trips as a fact table with dimensions for riders, drivers, cities, and time.

  2. 02

    Generate a large synthetic dataset

    ~3h

    Write a data-generation script to populate millions of realistic trip and fare rows.

  3. 03

    Compute driver utilization by hour

    ~2.5h

    Write a window-function query computing driver utilization rate per city and hour.

  4. 04

    Quantify surge-pricing revenue impact

    ~2h

    Write a query comparing revenue during surge versus non-surge periods.

  5. 05

    Optimize slow queries

    ~2.5h

    Use EXPLAIN ANALYZE to identify bottlenecks and add indexes to speed up the reporting queries.

  6. 06

    Build a materialized view

    ~2h

    Create a materialized view summarizing daily city-level metrics and schedule its refresh.

Resources

Ready to build this?

Get a GitHub repo and start building. Your AI reviewer checks each step as you go.

~14.5h · 6 steps

Tech stack

PostgreSQLpgAdminEXPLAIN ANALYZE