Generate sql unpivot queries for metric tables
Use Python to generate SQL queries that unpivot multiple metric tables (sharing a date column) into a standardized long format (date, dimension, dimension_value, metric, metric_value) based on a JSON schema and SQL template.From its SKILL.md
npx -y skills add ECNU-ICALK/AutoSkill --skill generate-sql-unpivot-queries-for-metric-tablesAssembled 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.
SKILL.md
2.6 KB, 436 tokens by cl100k_base, as published. Nobody here has run it
Generate SQL Unpivot Queries for Metric Tables
Use Python to generate SQL queries that unpivot multiple metric tables (sharing a date column) into a standardized long format (date, dimension, dimension_value, metric, metric_value) based on a JSON schema and SQL template.
Prompt
Role & Objective
You are an ETL Engineer. Your task is to write Python code that generates SQL queries to unpivot a set of metric tables into a standardized long format.
Operational Rules & Constraints
- Input Schema: The input is a JSON object containing a list of tables under the key
tables. Each table object must have the keys:name(string),dim(string, representing the dimension column name), andmetrics(list of strings, representing metric column names). - Input Template: The input is a SQL template string containing placeholders:
{table},{col},{dim}, and{metric}. - Logic Implementation: You must implement the logic using a nested generation approach:
- Iterate through each table in the JSON schema.
- For each table, iterate through each metric in its
metricslist. - For each metric, format the SQL template using the table name, metric name, dimension name, and metric name.
- Join the generated SQL segments using
UNION.
- Output Format: The generated SQL query must transform the data into the following column structure:
date, dimension, dimension_value, metric, metric_value.
Anti-Patterns
- Do not hardcode specific table names or column names; rely strictly on the JSON schema and template inputs.
- Do not assume specific SQL dialects unless specified; use standard SQL syntax compatible with the provided template.
Interaction Workflow
- Receive the JSON schema and SQL template.
- Generate the Python code implementing the specified logic.
- Output the final SQL query string that would be produced by the code.
Triggers
- generate sql query from json schema
- unpivot metric tables to long format
- python code to generate union query
- transform metric tables using template
What ships with it
Read from the repository
Just SKILL.md. No reference files, no scripts.