Skip to content

skillData and AI

SQL Analytics Queries

Writes readable CTE-based SQL for funnels, cohorts and retention, with the grain stated up front. Use it for product and revenue analytics questions.

npx workspacetemplates add sql-analytics-queries --tool claude-code

Writes 1 file to .claude/skills/sql-analytics-queries/

.claude/skills/sql-analytics-queries/SKILL.md28 lines · 1.6 KBFile
---
name: sql-analytics-queries
description: Writes readable CTE-based SQL for funnels, cohorts and retention, with the grain stated up front. Use it for product and revenue analytics questions.
---

# SQL analytics queries

Write the query as a story of small named steps. Know the grain of every step.

## Steps

1. Restate the question as a metric: numerator, denominator, time window, and the unit counted (users, orders, sessions). Ask if any is unclear.
2. Inspect the tables first: columns, keys, and a few rows. Confirm the grain of each (one row per what?) and the time zone of timestamps.
3. Build with CTEs, one idea each, named for what they hold: `signups`, `first_purchase`, `weekly_activity`. Put filters in the earliest CTE possible.
4. Funnels: take each user's first event per step with `MIN(event_time)`, join steps on user and require time order. Count distinct users, never events.
5. Cohorts and retention: cohort = `DATE_TRUNC` of first activity; offset = periods between activity and cohort; divide by the cohort size, not by total users.
6. Use window functions (`ROW_NUMBER`, `LAG`, `SUM() OVER`) instead of self-joins where possible.
7. Check: the first funnel step equals a plain `COUNT(DISTINCT user_id)`; every step is less than or equal to the one before; cohort sizes add up to the total.

## Rules

- Never join two tables at different grains without aggregating one first.
- Write `LEFT JOIN` when losing rows would bias the metric, and say why.
- Match the dialect already used in the project.

## Report

The query, a two line explanation of each CTE, the sanity checks and their results, and every assumption such as "excluded internal accounts".

What it does

Analytics queries go wrong quietly: a join that multiplies rows, a funnel that counts users twice, a cohort off by one week. This skill writes each query as a chain of named steps, states the grain of every step, and checks totals against a simple count.

You get SQL a colleague can read top to bottom, plus a note on the assumptions behind it so the number can be defended.

Example

you › Weekly retention for users who signed up in September, last 8 weeks.

agent › Query in analytics/retention_sept.sql using 4 CTEs: signups, activity, offsets, cohort_sizes. Week 0 is 100% by construction; week 1 is 38.2%, week 4 is 21.7%, week 8 is 14.9%. Check: cohort size 9,402 equals COUNT(DISTINCT user_id) on signups. Assumption: any login counts as active; test accounts excluded by the is_internal flag.

Questions

Which SQL dialect does it write?
The one your project already uses. It reads existing queries and migrations for hints, and asks if there is nothing to go on.
Does it run queries against my database?
Only if you have given the agent a command line client and a connection. It prefers read-only queries and shows the SQL before running anything expensive.
  • Data Pipeline Design

    SkillData and AI

    Free
  • dbt Modeling

    SkillData and AI

    Free