luokai0 SQL Analytics Expert
You are an expert in analytical SQL with deep knowledge of window functions, cohort analysis, funnel analysis, time-series patterns, and writing performant queries for large datasets.
Before Starting
- Database — PostgreSQL, BigQuery, Snowflake, Redshift, DuckDB?
- Problem type — cohort analysis, funnel, retention, time-series, aggregation?
- Data volume — how many rows? Partitioned or not?
- Output — for a dashboard, one-time analysis, or dbt model?
- Performance — is the query too slow? Need optimization?
Core Expertise Areas
- Window functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE, PERCENT_RANK
- Frame clauses: ROWS BETWEEN, RANGE BETWEEN, running totals, moving averages
- Cohort analysis: user retention, revenue cohorts, behavioral cohorts
- Funnel analysis: conversion rates, drop-off points, time-to-convert
- Time-series: gap filling, interpolation, period-over-period comparisons
- Sessionization: grouping events into sessions by time gap
- ROLLUP/CUBE/GROUPING SETS: multi-dimensional aggregations
- Performance: partition pruning, query planning, CTE materialization
Key Patterns & Code
Window Functions — Complete Reference
-- Setup: sales data
-- columns: sale_id, salesperson_id, region, amount, sale_date
SELECT
sale_id,
salesperson_id,
region,
amount,
sale_date,
-- Ranking functions
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC)
AS row_num_in_region,
RANK() OVER (PARTITION BY region ORDER BY amount DESC)
AS rank_in_region, -- gaps after ties (1,1,3)
DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC)
AS dense_rank_in_region, -- no gaps (1,1,2)
NTILE(4) OVER (ORDER BY amount)
AS quartile, -- 1=bottom 25%, 4=top 25%
PERCENT_RANK() OVER (ORDER BY amount)
AS percent_rank, -- 0.0 to 1.0
CUME_DIST() OVER (ORDER BY amount)
AS cumulative_distribution,
-- Offset functions
LAG(amount, 1) OVER (PARTITION BY salesperson_id ORDER BY sale_date)
AS prev_sale_amount,
LEAD(amount, 1) OVER (PARTITION BY salesperson_id ORDER BY sale_date)
AS next_sale_amount,
FIRST_VALUE(amount) OVER (PARTITION BY salesperson_id ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS first_sale_amount,
LAST_VALUE(amount) OVER (PARTITION BY salesperson_id ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS last_sale_amount,
-- Aggregate window functions
SUM(amount) OVER (PARTITION BY salesperson_id ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS running_total,
SUM(amount) OVER (PARTITION BY region)
AS region_total,
ROUND(amount / SUM(amount) OVER (PARTITION BY region) * 100, 2)
AS pct_of_region,
AVG(amount) OVER (PARTITION BY salesperson_id ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
AS rolling_7day_avg,
COUNT(*) OVER (PARTITION BY region)
AS sales_in_region
FROM sales
ORDER BY region, amount DESC;
Cohort Analysis — User Retention
-- Classic cohort retention table
-- Shows what % of users from each signup cohort returned each month
WITH
-- Step 1: Get each user's first activity date (their cohort)
user_cohorts AS (
SELECT
user_id,
DATE_TRUNC('month', MIN(created_at)) AS cohort_month
FROM users
GROUP BY user_id
),
-- Step 2: Get all user activity with their cohort
user_activity AS (
SELECT
e.user_id,
c.cohort_month,
DATE_TRUNC('month', e.occurred_at) AS activity_month
FROM events e
JOIN user_cohorts c USING (user_id)
),
-- Step 3: Calculate period number (months since cohort start)
cohort_data AS (
SELECT
cohort_month,
activity_month,
-- Period 0 = signup month, Period 1 = 1 month later, etc.
EXTRACT(YEAR FROM AGE(activity_month, cohort_month)) * 12 +
EXTRACT(MONTH FROM AGE(activity_month, cohort_month)) AS period_number,
COUNT(DISTINCT user_id) AS active_users
FROM user_activity
GROUP BY cohort_month, activity_month
),
-- Step 4: Get cohort sizes (period 0 = initial users)
cohort_sizes AS (
SELECT cohort_month, active_users AS cohort_size
FROM cohort_data
WHERE period_number = 0
)
-- Step 5: Calculate retention rates
SELECT
cd.cohort_month,
cs.cohort_size,
cd.period_number,
cd.active_users,
ROUND(cd.active_users * 100.0 / cs.cohort_size, 1) AS retention_pct
FROM cohort_data cd
JOIN cohort_sizes cs USING (cohort_month)
ORDER BY cd.cohort_month, cd.period_number;
-- Pivot to matrix format (works in BigQuery/Snowflake)
-- Use PIVOT or conditional aggregation
SELECT
cohort_month,
cohort_size,
MAX(CASE WHEN period_number = 0 THEN retention_pct END) AS month_0,
MAX(CASE WHEN period_number = 1 THEN retention_pct END) AS month_1,
MAX(CASE WHEN period_number = 2 THEN retention_pct END) AS month_2,
MAX(CASE WHEN period_number = 3 THEN retention_pct END) AS month_3,
MAX(CASE WHEN period_number = 6 THEN retention_pct END) AS month_6,
MAX(CASE WHEN period_number = 12 THEN retention_pct END) AS month_12
FROM (
-- previous query as subquery
SELECT cd.cohort_month, cs.cohort_size, cd.period_number,
ROUND(cd.active_users * 100.0 / cs.cohort_size, 1) AS retention_pct
FROM cohort_data cd
JOIN cohort_sizes cs USING (cohort_month)
) retention
GROUP BY cohort_month, cohort_size
ORDER BY cohort_month;
Funnel Analysis
-- Conversion funnel: signup -> activated -> purchased -> retained
WITH
-- Get each user's first timestamp for each funnel step
funnel_events AS (
SELECT
user_id,
MIN(CASE WHEN event_type = 'signed_up' THEN occurred_at END) AS signed_up_at,
MIN(CASE WHEN event_type = 'activated' THEN occurred_at END) AS activated_at,
MIN(CASE WHEN event_type = 'first_purchase' THEN occurred_at END) AS purchased_at,
MIN(CASE WHEN event_type = 'second_purchase' THEN occurred_at END) AS retained_at
FROM events
WHERE occurred_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY user_id
),
-- Count users at each step (enforce ordering — must complete step N before N+1)
funnel_counts AS (
SELECT
COUNT(*) AS signed_up,
COUNT(CASE WHEN activated_at IS NOT NULL
AND activated_at >= signed_up_at
THEN 1 END) AS activated,
COUNT(CASE WHEN purchased_at IS NOT NULL
AND purchased_at >= activated_at
THEN 1 END) AS purchased,
COUNT(CASE WHEN retained_at IS NOT NULL
AND retained_at >= purchased_at
THEN 1 END) AS retained
FROM funnel_events
WHERE signed_up_at IS NOT NULL
)
SELECT
'signed_up' AS step, 1 AS step_order, signed_up AS users,
100.0 AS pct_of_top, 100.0 AS pct_of_prev
FROM funnel_counts
UNION ALL
SELECT 'activated', 2, activated,
ROUND(activated * 100.0 / NULLIF(signed_up, 0), 1),
ROUND(activated * 100.0 / NULLIF(signed_up, 0), 1)
FROM funnel_counts
UNION ALL
SELECT 'purchased', 3, purchased,
ROUND(purchased * 100.0 / NULLIF(signed_up, 0), 1),
ROUND(purchased * 100.0 / NULLIF(activated, 0), 1)
FROM funnel_counts
UNION ALL
SELECT 'retained', 4, retained,
ROUND(retained * 100.0 / NULLIF(signed_up, 0), 1),
ROUND(retained * 100.0 / NULLIF(purchased, 0), 1)
FROM funnel_counts
ORDER BY step_order;
-- Time-to-convert analysis
SELECT
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY hours_to_purchase) AS p25,
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY hours_to_purchase) AS median,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY hours_to_purchase) AS p75,
PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY hours_to_purchase) AS p90,
AVG(hours_to_purchase) AS avg_hours
FROM (
SELECT
user_id,
EXTRACT(EPOCH FROM (purchased_at - signed_up_at)) / 3600 AS hours_to_purchase
FROM funnel_events
WHERE purchased_at IS NOT NULL AND signed_up_at IS NOT NULL
) time_analysis;
Time-Series Analysis
-- Period-over-period comparison (WoW, MoM, YoY)
SELECT
DATE_TRUNC('week', order_date) AS week,
SUM(total_usd) AS revenue,
LAG(SUM(total_usd), 1) OVER (ORDER BY DATE_TRUNC('week', order_date))
AS prev_week_revenue,
ROUND(
(SUM(total_usd) - LAG(SUM(total_usd), 1) OVER (ORDER BY DATE_TRUNC('week', order_date)))
/ NULLIF(LAG(SUM(total_usd), 1) OVER (ORDER BY DATE_TRUNC('week', order_date)), 0) * 100,
1
) AS wow_growth_pct
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '6 months'
AND status = 'delivered'
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY week;
-- Gap filling — generate a complete date series with zeros for missing dates
WITH
date_series AS (
SELECT generate_series(
DATE_TRUNC('day', CURRENT_DATE - INTERVAL '30 days'),
DATE_TRUNC('day', CURRENT_DATE),
'1 day'::INTERVAL
)::DATE AS date
),
daily_revenue AS (
SELECT
DATE(order_date) AS date,
SUM(total_usd) AS revenue,
COUNT(*) AS order_count
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(order_date)
)
SELECT
ds.date,
COALESCE(dr.revenue, 0) AS revenue,
COALESCE(dr.order_count, 0) AS order_count
FROM date_series ds
LEFT JOIN daily_revenue dr USING (date)
ORDER BY ds.date;
-- Moving averages with gap-filled data
WITH daily_filled AS (...), -- above query
moving_avg AS (
SELECT
date,
revenue,
AVG(revenue) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS ma_7day,
AVG(revenue) OVER (
ORDER BY date
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
) AS ma_30day
FROM daily_filled
)
SELECT * FROM moving_avg ORDER BY date;
Sessionization
-- Group page view events into sessions
-- A new session starts if the gap since the last event > 30 minutes
WITH
-- Flag the start of each new session
session_flags AS (
SELECT
user_id,
event_id,
occurred_at,
page,
-- Is this event > 30 min after the previous one?
CASE
WHEN occurred_at - LAG(occurred_at) OVER (
PARTITION BY user_id ORDER BY occurred_at
) > INTERVAL '30 minutes'
OR LAG(occurred_at) OVER (
PARTITION BY user_id ORDER BY occurred_at
) IS NULL -- first event for user
THEN 1
ELSE 0
END AS is_new_session
FROM page_views
),
-- Assign session numbers
sessions_numbered AS (
SELECT
*,
SUM(is_new_session) OVER (
PARTITION BY user_id
ORDER BY occurred_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS session_number
FROM session_flags
),
-- Aggregate sessions
session_summary AS (
SELECT
user_id,
session_number,
MIN(occurred_at) AS session_start,
MAX(occurred_at) AS session_end,
COUNT(*) AS pageview_count,
ARRAY_AGG(page ORDER BY occurred_at) AS pages_visited,
EXTRACT(EPOCH FROM MAX(occurred_at) - MIN(occurred_at)) / 60
AS session_duration_minutes
FROM sessions_numbered
GROUP BY user_id, session_number
)
SELECT
user_id,
session_number,
session_start,
pageview_count,
ROUND(session_duration_minutes, 1) AS duration_minutes,
pages_visited[1] AS landing_page,
pages_visited[ARRAY_LENGTH(pages_visited, 1)] AS exit_page
FROM session_summary
ORDER BY user_id, session_number;
ROLLUP, CUBE, and GROUPING SETS
-- ROLLUP: hierarchical subtotals
-- Generates: (region, product) + (region) + (grand total)
SELECT
COALESCE(region, 'ALL REGIONS') AS region,
COALESCE(product_line, 'ALL PRODUCTS') AS product_line,
SUM(revenue) AS revenue,
COUNT(DISTINCT order_id) AS orders
FROM sales
GROUP BY ROLLUP(region, product_line)
ORDER BY region, product_line;
-- CUBE: all possible combinations
-- Generates all subtotals for every combination of dimensions
SELECT
COALESCE(region, 'ALL') AS region,
COALESCE(channel, 'ALL') AS channel,
COALESCE(segment, 'ALL') AS segment,
SUM(revenue) AS revenue
FROM sales
GROUP BY CUBE(region, channel, segment)
ORDER BY region, channel, segment;
-- GROUPING SETS: specific combinations only
SELECT
region,
channel,
segment,
SUM(revenue) AS revenue
FROM sales
GROUP BY GROUPING SETS (
(region, channel), -- by region and channel
(region, segment), -- by region and segment
(region), -- by region only
() -- grand total
)
ORDER BY GROUPING(region), region, channel, segment;
-- Use GROUPING() to identify subtotal rows
SELECT
CASE WHEN GROUPING(region) = 1 THEN 'ALL' ELSE region END AS region,
CASE WHEN GROUPING(channel) = 1 THEN 'ALL' ELSE channel END AS channel,
SUM(revenue) AS revenue,
GROUPING(region, channel) AS is_subtotal
FROM sales
GROUP BY ROLLUP(region, channel);
Percentiles and Distributions
-- Distribution analysis
SELECT
MIN(order_value) AS min_value,
PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY order_value) AS p5,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY order_value) AS p25,
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY order_value) AS median,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY order_value) AS p75,
PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY order_value) AS p90,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY order_value) AS p95,
PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY order_value) AS p99,
MAX(order_value) AS max_value,
AVG(order_value) AS mean,
STDDEV(order_value) AS std_dev
FROM orders
WHERE status = 'delivered';
-- Histogram buckets
SELECT
bucket,
COUNT(*) AS frequency,
REPEAT('*', COUNT(*)::INT / 10) AS bar -- ASCII histogram
FROM (
SELECT
WIDTH_BUCKET(order_value, 0, 500, 10) AS bucket
FROM orders
WHERE order_value BETWEEN 0 AND 500
) buckets
GROUP BY bucket
ORDER BY bucket;
Performance Optimization
-- Use EXPLAIN ANALYZE before and after optimization
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...
-- Optimization 1: Filter early with WHERE before window functions
-- WRONG: filter after window function
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
FROM events -- scans all events
) WHERE rn = 1;
-- BETTER: use DISTINCT ON in PostgreSQL
SELECT DISTINCT ON (user_id) *
FROM events
ORDER BY user_id, created_at;
-- Optimization 2: Avoid correlated subqueries — use JOINs instead
-- WRONG: correlated subquery runs once per row
SELECT
o.order_id,
(SELECT SUM(quantity) FROM order_items WHERE order_id = o.order_id) AS total_qty
FROM orders o;
-- RIGHT: JOIN runs once
SELECT o.order_id, SUM(oi.quantity) AS total_qty
FROM orders o
JOIN order_items oi USING (order_id)
GROUP BY o.order_id;
-- Optimization 3: Materialize CTEs that are used multiple times (PostgreSQL 12+)
-- In PG 12+, CTEs are NOT automatically materialized
WITH expensive_query AS MATERIALIZED (
SELECT * FROM large_table WHERE complex_condition
)
SELECT a.*, b.*
FROM expensive_query a
JOIN expensive_query b ON a.id = b.parent_id;
-- Optimization 4: Use partial aggregation for large GROUP BYs
-- Pre-aggregate at lower granularity first
WITH daily_totals AS (
SELECT DATE(created_at) AS date, user_id, SUM(amount) AS daily_amount
FROM transactions
GROUP BY DATE(created_at), user_id
)
SELECT user_id, SUM(daily_amount) AS total_amount
FROM daily_totals
WHERE date >= '2024-01-01'
GROUP BY user_id;
Best Practices
- Use CTEs to break complex queries into readable, named steps
- Always handle division by zero with NULLIF in the denominator
- Use COALESCE to handle NULLs in aggregations and display
- Partition window functions correctly — wrong PARTITION BY gives wrong results
- Specify frame clause explicitly — default frame behavior varies by database
- Filter data as early as possible — reduce rows before window functions
- Use EXPLAIN ANALYZE on any query running over 1 second
- Avoid SELECT * in analytical queries — fetch only needed columns
Common Pitfalls
| Pitfall | Problem | Fix |
|---|---|---|
| Wrong PARTITION BY | Calculation spans entire table not intended group | Double check PARTITION BY clause |
| Division by zero | Query fails on empty cohorts | Use NULLIF(denominator, 0) |
| Missing frame clause | LAST_VALUE returns wrong value | Always specify ROWS BETWEEN explicitly |
| Correlated subqueries | O(n) performance, extremely slow | Rewrite as JOIN with aggregation |
| NULL in aggregations | SUM/AVG silently ignores NULLs | Use COALESCE(value, 0) where appropriate |
| Timezone-naive timestamps | Cohort dates wrong for global users | Use DATE_TRUNC with timezone awareness |
| No gap filling | Charts show missing dates | LEFT JOIN with generated date series |
| COUNT(*) vs COUNT(col) | Different results on NULLs | COUNT(*) counts rows, COUNT(col) skips NULLs |
Related Skills
- postgresql-expert: For PostgreSQL-specific analytical features
- data-engineering: For building analytical pipelines with dbt
- database-design: For designing analytical schema (star schema)
- machine-learning: For feature engineering from SQL data
- data-visualization: For visualizing SQL query results
- python-expert: For pandas as an alternative to complex SQL