My README claimed 50 SQL templates and shipped 8. Now the tests enforce the claim.

Open the README above a small SQL library and you will see numbers. I have written those numbers myself, and this is the story of the one that was wrong.

My SQL playground repo started with a README that claimed "50+ query templates". The actual count was 8. I did not lie on purpose. The number drifted the way most README drift happens: I wrote the line while planning the library, shipped a fraction of it, and then stopped looking at the gap.

The fix was not writing 50 more templates. The fix was making the claim impossible to break.

The structural fix

The whole template library lives in two data files, templates.py and templates_advanced.py. The Streamlit app renders whatever is in the library. The tests import the same files. One source of truth, two consumers.

Then tests/test_templates.py runs the entire library through pytest with parametrization, currently 115 tests, and each template fails CI if any of these happen:

1. The query errors against the sample database.
2. It runs but returns zero rows, which usually means a broken join or a filter that filters everything.
3. The total count drops below 50, which turns the README claim into something the CI pipeline enforces instead of something I have to remember.

The last one is the odd-looking test, and it is the one I would defend hardest. A test that asserts your library is large enough for your own documentation sounds like a stunt, but it is the same logic as a smoke test on an API: the promise either holds at a fixed threshold or it doesn't, and a number explicitly written in public deserves a guard.

The sample database makes honesty cheap

All of this works because the sample data is deterministic. Seeded RNG in sample_data.py generates an in-memory e-commerce schema in DuckDB, 4 tables and roughly 19k rows, identical on every run. A parametrized test suite over 55 templates can only be trusted if the ground under it never moves.

That was also why I picked DuckDB over SQLite for this project. The library teaches things SQLite cannot express: QUALIFY, FILTER (WHERE ...), ROLLUP, PERCENTILE_CONT, and generate_series for date spines. Recreating those in SQLite would have meant rewriting the lessons or teaching a dialect that keeps the boring half of the topic.

What the categories look like

The 55 templates are grouped by what they teach rather than by SQL keyword.

• Basics and filtering: 12 templates covering computed columns and anti-joins with NOT EXISTS.
• Joins: 7 templates, including a self-join and the filter-in-the-ON semantic that trips people up constantly.
• Aggregation and window functions: 15 templates covering ROLLUP subtotals, percentiles, NTILE buckets, and running totals.
• Date and time: 6 templates including signup-to-first-order gaps.
• CTEs and subqueries: 6 templates with multi-CTE funnels and a recursive month spine.
• Business analytics: 9 templates that stack into Pareto 80/20, RFM scoring, cohort retention, and market basket analysis.

You can run the whole thing without installing anything: DuckDB compiles to WebAssembly, so the Hugging Face demo runs the real engine in the browser, and the same code is on Streamlit Cloud.

What I would tell a junior writing a portfolio repo

Pick one number you want to be known for and make it a test. Mine turned out to be the template count. It could be a major version of your pipeline throughput, a latency budget, or a coverage floor. When the claim and the code are wired together, updating one without the other stops being possible, and a README bullet becomes a contract instead of a wish.

Full repo: https://github.com/Reaver1000/sql-query-builder