Analytics SQL

What it does

Readable analytics queries you can trust.

Your agent loads it when the user writes a report, metric or analytics query. It carries 5 rules and reads a profile.md with your details before it starts, so the output matches your brand and project.

SKILL.md, first 40 lines

---
name: analytics-sql
description: "Use when the user says 'write a query for', 'weekly active users', 'retention by cohort', 'funnel conversion', 'why do these numbers not match', 'fix this slow report query', 'running total by month', or wants a readable analytics SQL query. Produces a CTE-per-step query with the grain stated per CTE, join fan-out checks, date spines, time zone and NULL handling, a note on how to read the result and the sanity checks that were run. Reads profile.md for database, ORM, data size and conventions; pattern library per dialect in patterns.md."
---

# Analytics SQL

Write analytics queries that a colleague can read top to bottom and that give the same number twice. Most wrong dashboards come from three causes: a join that multiplied rows, a date boundary that leaked a day, or a NULL that silently dropped a group. This skill writes the query so each of those is visible and checked.

## Before you start

Read `profile.md` in this folder:

| profile.md line | Use |
|---|---|
| **Database** | Picks the dialect for date functions, `QUALIFY`, `DATE_TRUNC`, percentile syntax. Section of the same name in `patterns.md`. |
| **ORM** | If an ORM is named, write the query as raw SQL for the reporting path and show where it plugs in (view, raw query, dbt model). "none, raw SQL" means deliver plain SQL. |
| **Data size** | Under 1M rows: clarity first. 1M-100M: filter early on the partition or date column, avoid `SELECT *`. 100M+: partition pruning, pre-aggregated daily tables, approximate distinct counts where the user accepts them. |
| **Conventions** | Naming (snake_case, plural tables, `_at` timestamps, `_id` keys) and style rules. Follow them over your defaults. |

If a line says "not set", ask once, write it into profile.md, continue. Open `patterns.md` and read the section for the database and the pattern you need.

## Workflow

1. **Restate the question as a metric.** Name the numerator, the denominator, the grain of the answer (per user per week?), the time window and the time zone. If two readings are plausible, state both and ask which one.
2. **Find the tables and keys.** Read the schema or ask for it. Write down each table's grain ("one row per order line") and its unique key. Never guess a key; run a uniqueness check (below).
3. **Build in CTEs, one step each.** Every CTE starts with a comment line `-- grain: one row per <thing>`. Order: filter source, derive, join, aggregate, final select. No subquery nested deeper than one level.
4. **Join with intent.** Name the key on both sides, state the expected cardinality (1:1, 1:many), and aggregate the "many" side to the grain of the "one" side before joining.
5. **Handle time deliberately.** Convert to the reporting time zone once, in the first CTE. Use half-open ranges `>= start AND < end`. Build a date spine when empty days must show as 0.
6. **Run the sanity checks** (below) if you have a database connection. If you cannot run anything, say so and list the checks for the user to run. Never claim a check passed that you did not run.
7. **Deliver** in the Output format.

## Decision table

| Situation | Do this | Not this |
|---|---|---|
| Count users after joining orders | `COUNT(DISTINCT user_id)`, or aggregate orders to one row per user first | `COUNT(*)` after a 1:many join |
| Date range for "March" | `>= '2025-03-01' AND < '2025-04-01'` | `BETWEEN ... AND '2025-03-31'` (drops 31 March after midnight) |
| Group with no activity should show 0 | Date spine `LEFT JOIN` facts, `COALESCE(n, 0)` | Inner join that drops the empty day |
| Average of a nullable column | State that `AVG` ignores NULL; use `COALESCE` only if NULL truly means 0 | Silent `AVG` over a mostly-NULL column |

Install

One command, in your project folder. It detects your agent; add --agent all for every agent.

npx proskills-cli add analytics-sql

Or copy the skill folder (the one holding SKILL.md) into the directory your agent reads, then restart it or start a new session.

More from Data & SQL

• Schema Designer$9Tables, keys and constraints from your domain.
• SQL Reviewer$9Catches the NULL, fan-out and lock bugs.
• Query Optimizer$12EXPLAIN-driven tuning with before and after timings.