sql-generation

Name: sql-generation
Author: Productfculty-aipm

70stars33forks

How to install sql-generation?

productfculty-aipm/pm-copilot-by-product-faculty/sql-generation

$npx -y skills add Productfculty-aipm/PM-Copilot-by-Product-Faculty --skill sql-generation

Installs into the current project.

Summary

Use this skill when the user asks to "write a SQL query", "help me query this data", "how do I get this metric from the database", "generate SQL for", "query for retention", "SQL to find churned users", "write a query for my analytics", or needs SQL to answer a specific product a

Files · 1

View on GitHub

SKILL.md

1	# SQL Generation for Product Analytics
2
3	You are generating SQL queries for product analytics use cases. The queries are designed to work with standard event-based analytics schemas (similar to Segment, Amplitude, Mixpanel raw exports, or most data warehouse setups).
4
5	## Step 1 — Load Context
6
7	Read `memory/user-profile.md` for analytics tool and any database or schema information the user has provided. Read `context/company/analytics-baseline.md` for metric definitions that should inform the query.
8
9	## Step 2 — Clarify the Schema
10
11	Ask (or infer from context):
12	- What is the main events table called? (e.g., `events`, `tracking_events`, `user_events`)
13	- What are the key columns? (e.g., `user_id`, `event_name`, `timestamp`, `properties`)
14	- Is the properties column a JSON field? (Common in event-based schemas)
15	- Are there separate user-level or session-level tables?
16
17	If the user hasn't specified, use this standard schema and note the assumption:
18	```sql
19	-- Assumed schema
20	-- events(user_id, event_name, timestamp, properties JSONB)
21	-- users(user_id, created_at, plan, segment)
22	```
23
24	## Step 3 — Common Product Analytics Queries
25
26	Generate the appropriate query for the requested use case:
27
28	User Retention Cohort (N-day retention):
29	```sql
30	-- D7 retention: % of users who performed an event 7 days after their first event
31	WITH first_events AS (
32	SELECT user_id, MIN(DATE(timestamp)) AS cohort_date
33	FROM events
34	WHERE event_name = 'app_opened' -- or signup event
35	GROUP BY user_id
36	),
37	day_7_events AS (
38	SELECT DISTINCT e.user_id
39	FROM events e
40	JOIN first_events f ON e.user_id = f.user_id
41	WHERE DATE(e.timestamp) = f.cohort_date + INTERVAL '7 days'
42	AND e.event_name = 'app_opened' -- or core action
43	)
44	SELECT
45	f.cohort_date,
46	COUNT(DISTINCT f.user_id) AS cohort_size,
47	COUNT(DISTINCT d.user_id) AS retained_users,
48	ROUND(100.0 * COUNT(DISTINCT d.user_id) / COUNT(DISTINCT f.user_id), 1) AS d7_retention_pct
49	FROM first_events f
50	LEFT JOIN day_7_events d ON f.user_id = d.user_id
51	GROUP BY f.cohort_date
52	ORDER BY f.cohort_date;
53	```
54
55	Activation Funnel:
56	```sql
57	-- Users completing each step of the activation flow
58	WITH users AS (SELECT DISTINCT user_id FROM events WHERE event_name = 'signed_up')
59	SELECT
60	'Step 1: Signed Up' AS step, COUNT(DISTINCT user_id) AS users FROM users
61	UNION ALL
62	SELECT 'Step 2: Connected Tool', COUNT(DISTINCT e.user_id)
63	FROM events e JOIN users u ON e.user_id = u.user_id WHERE e.event_name = 'connected_tool'
64	UNION ALL
65	SELECT 'Step 3: Completed Core Action', COUNT(DISTINCT e.user_id)
66	FROM events e JOIN users u ON e.user_id = u.user_id WHERE e.event_name = 'core_action_completed';
67	```
68
69	Weekly Active Users (Core Action):
70	```sql
71	SELECT
72	DATE_TRUNC('week', timestamp) AS week,
73	COUNT(DISTINCT user_id) AS weekly_active_users
74	FROM events
75	WHERE event_name = 'core_action_completed'
76	AND timestamp >= CURRENT_DATE - INTERVAL '90 days'
77	GROUP BY 1
78	ORDER BY 1;
79	```
80
81	Churned Users (last 30 days):
82	```sql
83	WITH last_seen AS (
84	SELECT user_id, MAX(timestamp) AS last_active
85	FROM events
86	GROUP BY user_id
87	)
88	SELECT
89	user_id,
90	last_active,
91	CURRENT_TIMESTAMP - last_active AS days_since_active
92	FROM last_seen
93	WHERE last_active < CURRENT_TIMESTAMP - INTERVAL '30 days'
94	ORDER BY last_active;
95	```
96
97	Feature Adoption Rate:
98	```sql
99	SELECT
100	DATE_TRUNC('week', e.timestamp) AS week,
101	COUNT(DISTINCT e.user_id) AS feature_users,
102	COUNT(DISTINCT all_users.user_id) AS total_active_users,
103	ROUND(100.0 * COUNT(DISTINCT e.user_id) / COUNT(DISTINCT all_users.user_id), 1) AS adoption_pct
104	FROM events e
105	CROSS JOIN (
106	SELECT DISTINCT user_id FROM events
107	WHERE event_name = 'app_opened' AND timestamp >= CURRENT_DATE - INTERVAL '7 days'
108	) all_users
109	WHERE e.event_name = 'feature_x_used'
110	AND e.timestamp >= CURRENT_DATE - INTERVAL '90 days'
111	GROUP BY 1
112	ORDER BY 1;
113	```
114
115	## Step 4 — Adapt to the User's Request
116
117	Take the specific metric or question the user wants to answer and:
118	1. Select or adapt the nearest template query
119	2. Replace all placeholder values with the user's actual event names, table names, and timeframes
120	3. Add appropriate WHERE clauses for segment filtering if requested
121	4. Add comments explaining what each section does
122
123	## Step 5 — Output
124
125	Provide:
126	- The SQL query, fully adapted to the user's request
127	- Brief explanation of how the query works (especially for complex CTEs)
128	- Assumptions made (which these can be changed)
129	- A validation check the user can run: "If this returns [sanity check result], the query is probably correct"

People who liked this also viewed

6 picks

Type

sql-generation

70stars33forks

View on GitHub

How to install sql-generation?

productfculty-aipm/pm-copilot-by-product-faculty/sql-generation

$npx -y skills add Productfculty-aipm/PM-Copilot-by-Product-Faculty --skill sql-generation

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# SQL Generation for Product Analytics
2
3	You are generating SQL queries for product analytics use cases. The queries are designed to work with standard event-based analytics schemas (similar to Segment, Amplitude, Mixpanel raw exports, or most data warehouse setups).
4
5	## Step 1 — Load Context
6
7	Read `memory/user-profile.md` for analytics tool and any database or schema information the user has provided. Read `context/company/analytics-baseline.md` for metric definitions that should inform the query.
8
9	## Step 2 — Clarify the Schema
10
11	Ask (or infer from context):
12	- What is the main events table called? (e.g., `events`, `tracking_events`, `user_events`)
13	- What are the key columns? (e.g., `user_id`, `event_name`, `timestamp`, `properties`)
14	- Is the properties column a JSON field? (Common in event-based schemas)
15	- Are there separate user-level or session-level tables?
16
17	If the user hasn't specified, use this standard schema and note the assumption:
18	```sql
19	-- Assumed schema
20	-- events(user_id, event_name, timestamp, properties JSONB)
21	-- users(user_id, created_at, plan, segment)
22	```
23
24	## Step 3 — Common Product Analytics Queries
25
26	Generate the appropriate query for the requested use case:
27
28	User Retention Cohort (N-day retention):
29	```sql
30	-- D7 retention: % of users who performed an event 7 days after their first event
31	WITH first_events AS (
32	SELECT user_id, MIN(DATE(timestamp)) AS cohort_date
33	FROM events
34	WHERE event_name = 'app_opened' -- or signup event
35	GROUP BY user_id
36	),
37	day_7_events AS (
38	SELECT DISTINCT e.user_id
39	FROM events e
40	JOIN first_events f ON e.user_id = f.user_id
41	WHERE DATE(e.timestamp) = f.cohort_date + INTERVAL '7 days'
42	AND e.event_name = 'app_opened' -- or core action
43	)
44	SELECT
45	f.cohort_date,
46	COUNT(DISTINCT f.user_id) AS cohort_size,
47	COUNT(DISTINCT d.user_id) AS retained_users,
48	ROUND(100.0 * COUNT(DISTINCT d.user_id) / COUNT(DISTINCT f.user_id), 1) AS d7_retention_pct
49	FROM first_events f
50	LEFT JOIN day_7_events d ON f.user_id = d.user_id
51	GROUP BY f.cohort_date
52	ORDER BY f.cohort_date;
53	```
54
55	Activation Funnel:
56	```sql
57	-- Users completing each step of the activation flow
58	WITH users AS (SELECT DISTINCT user_id FROM events WHERE event_name = 'signed_up')
59	SELECT
60	'Step 1: Signed Up' AS step, COUNT(DISTINCT user_id) AS users FROM users
61	UNION ALL
62	SELECT 'Step 2: Connected Tool', COUNT(DISTINCT e.user_id)
63	FROM events e JOIN users u ON e.user_id = u.user_id WHERE e.event_name = 'connected_tool'
64	UNION ALL
65	SELECT 'Step 3: Completed Core Action', COUNT(DISTINCT e.user_id)
66	FROM events e JOIN users u ON e.user_id = u.user_id WHERE e.event_name = 'core_action_completed';
67	```
68
69	Weekly Active Users (Core Action):
70	```sql
71	SELECT
72	DATE_TRUNC('week', timestamp) AS week,
73	COUNT(DISTINCT user_id) AS weekly_active_users
74	FROM events
75	WHERE event_name = 'core_action_completed'
76	AND timestamp >= CURRENT_DATE - INTERVAL '90 days'
77	GROUP BY 1
78	ORDER BY 1;
79	```
80
81	Churned Users (last 30 days):
82	```sql
83	WITH last_seen AS (
84	SELECT user_id, MAX(timestamp) AS last_active
85	FROM events
86	GROUP BY user_id
87	)
88	SELECT
89	user_id,
90	last_active,
91	CURRENT_TIMESTAMP - last_active AS days_since_active
92	FROM last_seen
93	WHERE last_active < CURRENT_TIMESTAMP - INTERVAL '30 days'
94	ORDER BY last_active;
95	```
96
97	Feature Adoption Rate:
98	```sql
99	SELECT
100	DATE_TRUNC('week', e.timestamp) AS week,
101	COUNT(DISTINCT e.user_id) AS feature_users,
102	COUNT(DISTINCT all_users.user_id) AS total_active_users,
103	ROUND(100.0 * COUNT(DISTINCT e.user_id) / COUNT(DISTINCT all_users.user_id), 1) AS adoption_pct
104	FROM events e
105	CROSS JOIN (
106	SELECT DISTINCT user_id FROM events
107	WHERE event_name = 'app_opened' AND timestamp >= CURRENT_DATE - INTERVAL '7 days'
108	) all_users
109	WHERE e.event_name = 'feature_x_used'
110	AND e.timestamp >= CURRENT_DATE - INTERVAL '90 days'
111	GROUP BY 1
112	ORDER BY 1;
113	```
114
115	## Step 4 — Adapt to the User's Request
116
117	Take the specific metric or question the user wants to answer and:
118	1. Select or adapt the nearest template query
119	2. Replace all placeholder values with the user's actual event names, table names, and timeframes
120	3. Add appropriate WHERE clauses for segment filtering if requested
121	4. Add comments explaining what each section does
122
123	## Step 5 — Output
124
125	Provide:
126	- The SQL query, fully adapted to the user's request
127	- Brief explanation of how the query works (especially for complex CTEs)
128	- Assumptions made (which these can be changed)
129	- A validation check the user can run: "If this returns [sanity check result], the query is probably correct"

People who liked this also viewed

6 picks

Type