Data AnalyticsIntermediate

SQL for Data Analytics

Use advanced SQL techniques for data analysis - window functions, CTEs, and cohort analysis.

4 hours
5 key concepts
2 in-depth sections
Official Docs

0 of 5 concepts completed (0%)

What You'll Learn

Tutorial Contents

  1. 1.Window Functions
  2. 2.Cohort Analysis

1.Window Functions

sql
-- 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

sql
-- 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

SQLAnalyticsWindow FunctionsCTEs

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.