SQL for Data Analytics
Use advanced SQL techniques for data analysis - window functions, CTEs, and cohort analysis.
0 of 5 concepts completed (0%)
What You'll Learn
Tutorial Contents
1.Window Functions
-- RANK, DENSE_RANK, ROW_NUMBER
SELECT
name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
DENSE_RANK() OVER (ORDER BY salary DESC) AS overall_dense_rank,
ROW_NUMBER() OVER (ORDER BY created_at) AS row_num-- Running totals and moving averages SELECT date, revenue, SUM(revenue) OVER (ORDER BY date) AS running_total, AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d_avg FROM daily_revenue;
-- Lead and Lag SELECT month, revenue, LAG(revenue, 1) OVER (ORDER BY month) AS prev_month, LEAD(revenue, 1) OVER (ORDER BY month) AS next_month, revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change FROM monthly_revenue; ```
2.Cohort Analysis
-- User cohort retention analysis
WITH cohorts AS (
SELECT
user_id,
DATE_TRUNC('month', MIN(created_at)) AS cohort_month
FROM orders
GROUP BY user_id
),
activity AS (
SELECT
o.user_id,
c.cohort_month,
DATE_TRUNC('month', o.created_at) AS activity_month,
EXTRACT(MONTH FROM AGE(
DATE_TRUNC('month', o.created_at),
c.cohort_month
)) AS months_since_cohort
FROM orders o
JOIN cohorts c ON o.user_id = c.user_id
)
SELECT
cohort_month,
months_since_cohort,
COUNT(DISTINCT user_id) AS retained_users
FROM activity
GROUP BY 1, 2
ORDER BY 1, 2;Code Example
-- Monthly revenue growth with CTE
WITH monthly AS (
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY 1
),
growth AS (
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM monthly
)
SELECT
month,
revenue,
ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS growth_pct
FROM growth
WHERE prev_revenue IS NOT NULL;Pro Tips
- 1CTEs (Common Table Expressions) make complex queries readable break them into named steps instead of nested subqueries.
- 2Window functions operate over a set of rows related to the current row they don't collapse rows like GROUP BY does.
- 3Index your WHERE, JOIN, and ORDER BY columns. Use EXPLAIN ANALYZE to verify the planner uses them.
- 4DATE_TRUNC is your friend for time-series analysis it rounds timestamps to a given precision (month, week, day).
Topics Covered
Continue Learning
Explore the official documentation and deepen your knowledge with real-world practice.
Explore the Full Portfolio
This is the complete portfolio of Sufi Khan Sulaiman, a technology leader specialising in B2B commerce and digital automation. Start from the Home page for the overview, then move through two decades of career experience across FLIR Systems, Lorex Technology, and 1c Platform, and the full catalogue of project case studies spanning headless commerce migrations, AI recommendation engines, and multi-channel fulfilment systems.
The skills and certifications page maps the technical and leadership capabilities behind the work, while the articles and the knowledge base break down the thinking into actionable frameworks. For hands-on learning, the tutorials and applications sections cover practical builds from front-end fundamentals to full-stack web apps.
For consulting engagement, the expertise page outlines service offerings, the ecommerce hub covers platform architecture and automation strategy, and the ecommerce guide (PDF) is a downloadable 55-page field manual. When you are ready to talk, the contact page is the direct line.