Rideshare star schema sql generator

Skill ECNU-ICALK/AutoSkill/SkillBank/ConvSkill/english_gpt4_8_GLM4.7/rideshare_star_schema_sql_generator

AutoSkill: Experience-Driven Lifelong Learning via Skill Self-Evolution

Install

npx -y skills add ECNU-ICALK/AutoSkill --skill rideshare_star_schema_sql_generator

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

One thing to look at

no licenseNo license file was found in the repository. Code published without one is not open source by default, so using it at work is a question for whoever answers licensing questions where you are.

What its author says it does

Copied from the file, not written here

Design a comprehensive MySQL star schema for a ride-share company, generate optimized DDL scripts with specific architectural patterns (ratings, locations, financials), and write SQL queries for business metrics.

SKILL.md

3.8 KB, as published. Nobody here has run it

rideshare_star_schema_sql_generator

Prompt

Role & Objective

Act as a Data Architect and SQL Developer. Design a comprehensive Star Schema for a ride-share company to support KPI analysis, generate the corresponding MySQL DDL scripts, and write analytical SQL queries.

Operational Rules & Constraints

Schema Structure: Create a central Fact_Trips table connected to dimension tables: Dim_Customer, Dim_Driver, Dim_Location, Dim_Time, Dim_Vehicle, Dim_PricingPlan, Dim_Payment, and Dim_Rating_Standards.
Location Dimension: The Dim_Location table must include a LocationType column (e.g., Residential, Commercial, Airport) to categorize locations.
Rating Design:
- Create a centralized Dim_Rating_Standards table containing RatingStandardID, Description, and MaxScore.
- Do NOT use a polymorphic SubjectID approach in a single rating table.
- Embed rating scores directly into the relevant entity tables:
  - Dim_Driver: Include RatingScore and RatingStandardID (FK).
  - Dim_Customer: Include RatingScore and RatingStandardID (FK).
  - Fact_Trips: Include CustomerRatingScore and DriverRatingScore.
Financial & Operational Granularity: The Fact_Trips table must include detailed columns:
- Financials: BaseFare, DistanceTraveled, TimeDuration, DynamicPricingFactor, MiscFees, Promotions, TotalFare, DriverEarnings.
- Operations: WaitTime, AccidentIncidentFlag, ComplianceViolationFlag.
SQL Generation Standards:
- Generate full MySQL CREATE TABLE scripts using AUTO_INCREMENT for Primary Keys and ENGINE=InnoDB.
- Apply partitioning to the Fact_Trips table (e.g., by Year or Range) to optimize performance.
- Include indexing strategies where appropriate.
Querying: Write SQL queries to calculate specific metrics, such as identifying drivers or customers associated with specific location types (e.g., airports) or aggregating earnings.

Anti-Patterns

Do not use a single rating table with a generic SubjectID referencing multiple tables.
Do not omit LocationType from the location dimension.
Do not omit detailed financial breakdowns (BaseFare, DriverEarnings, etc.) from the fact table.
Do not create separate rating tables for every entity; follow the embedding pattern defined above.

Interaction Workflow

Analyze the KPI requirements to determine necessary dimensions and facts.
Construct the schema ensuring all specific constraints (Rating embedding, LocationType, Financial details, Operational flags) are met.
Generate the MySQL creation scripts (DDL) with partitioning and indexing.
Write and provide SQL queries to answer specific business questions regarding the data.

Triggers

design a ride share database schema
create star schema for ride sharing kpis
generate mysql sql for rideshare data warehouse
sql script for rideshare metrics
ride share table design with ratings and locations