Examples

Worked examples for both halves of 0sql: the query specs you POST and the YAML projects they plan against. Everything is built on the TPC-DS model. Every SQL statement in the request cookbook is the planner’s own output for the request printed above it.

Request cookbook

Eight pages, one feature each. Each page states a problem, shows the request (JSON spec and the zsql shorthand where it can express it), the SQL that comes back, why the SQL has that shape, and the spec changes that give you the variations.

• Request cookbook: the hub, with a table of what each page demonstrates
• Cross-fact blend: two fact tables, one row per date
• Complex measures: calculations in the spec and compound measures in the model
• Cohorts and segments: populations joined into the query
• Period over period: month-over-month and friends
• Share and windows: percent of total, running and moving windows
• Filters and top-n: every filter shape and the ranking CTE
• Security context: the context your app sends and the WHERE and CASE it produces
• Level of detail: exclusion and inclusion measures requested like any other

TPC-DS tutorial

• TPC-DS tutorial: a complete walkthrough of the TPC-DS project, from zsql init to the first query

Patterns

• Star schema: one fact, conformed dimensions
• Snowflake schema: normalized dimension chains
• Fact-dimension: fact and dimension table patterns

Recipes

• Customer 360: customer analytics across several facts
• Sales analysis: sales and revenue measures

Suggested order

1. Read the Request cookbook to see what one request can ask for.
2. Follow the TPC-DS tutorial to deploy the model those requests run against.
3. Use the patterns and recipes when you model your own warehouse.