Rideshare Star Schema Sql Generator

What the source does

• FilesystemNone observedNo reads or writes in the source
• NetworkNone observedNo outbound calls in the source
• ShellNone observedNo commands run in the source
• DependenciesPinnedNothing floats
• SecretsNone foundNo keys in source

Five audit layers

Gates applied: no behavioural pass.

Audit 0.4.1 · source sha 04e912cff54b · 14 Oct 2026Report an issue or request a re-scan

• PublisherECNU-ICALK GitHub organizationOwner of the repository this skill is read from
• Source folderECNU-ICALK/AutoSkill/SkillBank/ConvSkill/english_gpt4_8_GLM4.7/rideshare_star_schema_sql_generatorThe exact folder we audited
• LicenceNot declaredAs the repository states it
• Commit pinned for this verdict04e912cff54bA new audit runs when the source changes
• Repository statusNot archivedLast push 157 days ago
• Sandboxed runNot runSkills are read, not executed; layer L4 is skipped

Its description: 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.

---
id: "e615d66d-76b3-4fa4-81ef-4f7fad7e1cb8"
name: "rideshare_star_schema_sql_generator"
description: "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."
version: "0.1.1"
tags:
  - "database design"
  - "mysql"
  - "star schema"
  - "ride-share"
  - "sql"
  - "data warehouse"
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"
---

# rideshare_star_schema_sql_generator

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.

## 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
1. **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`.
2. **Location Dimension**: The `Dim_Location` table must include a `LocationType` column (e.g., Residential, Commercial, Airport) to categorize locations.
3. **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`.
4. **Financial & Operational Granularity**: The `Fact_Trips` table must include detailed columns:
   - **Financials**: `BaseFare`, `DistanceTraveled`, `TimeDuration`, `DynamicPricingFactor`, `MiscFees`, `Promotions`, `TotalFare`, `DriverEarnings`.
   - **Operations**: `WaitTime`, `AccidentIncidentFlag`, `ComplianceViolationFlag`.
5. **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.
6. **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
1. Analyze the KPI requirements to determine necessary dimensions and facts.
2. Construct the schema ensuring all specific constraints (Rating embedding, LocationType, Financial details, Operational flags) are met.
3. Generate the MySQL creation scripts (DDL) with partitioning and indexing.
4. 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

Excerpt, first 3,936 characters of SKILL.md, verbatim from the audited commit. It is text from someone else’s repository, quoted so you can judge it before your agent reads it.

git clone https://github.com/ECNU-ICALK/AutoSkill
git -C AutoSkill checkout 04e912cff54b

Works in

1. 2026-10-14
   ✓SAFE B 89 / 100

   first audit · source 04e912cff54b

Questions about Rideshare Star Schema Sql Generator

• github.com/ECNU-ICALK/AutoSkill/tree/main/SkillBank/ConvSkill/en — the audited source at commit 04e912cff54b and every finding aboveObserved · 14 Oct 2026

Spotted something wrong? Report a correction · Methodology