About Projects Blog Contact

Modern SQL for Data Analysts: Mastering CTEs, Window Functions, and Advanced Joins

Published 25 Aug 2026
Reading Time 06 Min
Category DATA SCIENCE

Master essential SQL concepts for data analysis. Learn CTEs, window functions, and complex joins with practical business examples and query breakdowns.

SQL code syntax

If you ask any senior Data Analyst or Business Intelligence professional which technical skill they use most every single day, the answer is almost never machine learning or complex UI tools—it is SQL.

While basic SELECT ... WHERE ... GROUP BY queries allow you to pull surface-level data, real-world business analytics demands more. You need to calculate running totals, rank customer spending, compute period-over-period growth, and cleanly join disparate datasets from marketing platforms and CRMs.

In this guide, we will break down the three most critical SQL concepts every analyst must master to solve complex business problems: Common Table Expressions (CTEs), Window Functions, and Advanced Joins.

1. Common Table Expressions (CTEs): Writing Clean, Modular SQL

Nested subqueries can quickly make your SQL code unreadable and difficult to debug. Common Table Expressions (CTEs), defined using the WITH clause, allow you to create temporary, named result sets that act like readable building blocks.

Why Use CTEs over Subqueries?

  1. Readability: Queries execute sequentially top-to-bottom.
  2. Reusability: Reference the same temporary logic multiple times in a single script.
  3. Maintainability: Easily isolate logic for testing or performance optimization.

Practical Example: Finding High-Value Customers

SQL


WITH customer_revenue AS (
-- CTE 1: Aggregate total spend per customer
SELECT
customer_id,
COUNT(order_id) AS total_orders,
SUM(order_amount) AS total_spend
FROM
`company.sales.orders`
WHERE
order_date >= '2026-01-01'
GROUP BY
customer_id
),

high_value_segment AS (
-- CTE 2: Filter for customers spending over $1,000
SELECT
customer_id,
total_orders,
total_spend
FROM
customer_revenue
WHERE
total_spend >= 1000
)

-- Final SELECT: Join with user profile details
SELECT
u.customer_name,
u.email,
h.total_orders,
h.total_spend
FROM
high_value_segment h
INNER JOIN
`company.crm.users` u ON h.customer_id = u.user_id
ORDER BY
h.total_spend DESC;

2. Window Functions: Computing Aggregations Without Collapsing Rows

Standard GROUP BY queries aggregate multiple rows into a single summary row. Window Functions, however, compute aggregate metrics across a set of table rows related to the current row without collapsing the individual rows.

Every window function uses an OVER() clause with PARTITION BY (to group data) and ORDER BY (to define the evaluation sequence).

Key Window Functions Every Analyst Should Know:

FunctionWhat It DoesCommon Business Use Case
ROW_NUMBER()Assigns a unique sequential integer per partitionDeduplicating records; finding a customer's first purchase
RANK() / DENSE_RANK()Assigns rank order with/without gap tiesTop N products or sales rep leaderboards
SUM() / AVG() OVER()Calculates cumulative running total or moving averageRunning revenue totals over time
LAG() / LEAD()Fetches values from previous or following rowsPeriod-over-period (MoM, YoY) growth calculations

Practical Example: Calculating Month-over-Month (MoM) Growth using LAG()

SQL


WITH monthly_sales AS (
SELECT
DATE_TRUNC(order_date, MONTH) AS sales_month,
SUM(order_amount) AS monthly_revenue
FROM
`company.sales.orders`
GROUP BY
1
)

SELECT
sales_month,
monthly_revenue,
-- Fetch previous month's revenue
LAG(monthly_revenue, 1) OVER (ORDER BY sales_month) AS previous_month_revenue,
-- Calculate MoM Growth Percentage
ROUND(
((monthly_revenue - LAG(monthly_revenue, 1) OVER (ORDER BY sales_month))
/ LAG(monthly_revenue, 1) OVER (ORDER BY sales_month)) * 100, 2
) AS mom_growth_pct
FROM
monthly_sales
ORDER BY
sales_month;

3. Mastering SQL Joins: Combining Marketing & Sales Datasets

In real-world data environments, business data is split across multiple tables. Understanding how to join datasets without creating accidental duplicates (cartesian products) is essential.

[ INNER JOIN ] [ LEFT JOIN ] [ FULL OUTER JOIN ]
Only Matching Rows All Rows from Left + All Rows from Both Tables
Between Both Tables Matching Right Rows (Fills NULLs where missing)

Best Practices for Error-Free Joins:

  1. Check Key Uniqueness: Before joining, verify if your join keys (user_id, campaign_id) are unique in the dimension table to avoid row multiplication.
  2. Filter Nulls Early: Exclude NULL key values in WHERE or ON clauses to ensure clean join conditions.
  3. Use LEFT JOIN for Behavioral Funnels: Use LEFT JOIN when tracking user steps (e.g., Web Session -> Lead Form -> Sales Deal) to prevent dropping users who dropped off before completing a deal.

Key Takeaways

  1. Structure queries with CTEs: Replace nested subqueries with clean WITH blocks to make your SQL scripts readable and modular.
  2. Unlock deep insights with Window Functions: Use LAG(), LEAD(), and ROW_NUMBER() to analyze period-over-period trends and deduplicate data easily.
  3. Validate joins carefully: Always check key uniqueness before executing joins to maintain accurate revenue and customer metrics.
Ajmal P P
Ajmal P P Data Analyst - Digital & Marketing Analytics
arrow_back BACK TO INSIGHTS
AI in Digital Marketing
Up Next Next Article →

AI in Digital Marketing

HIRE ME

Let's Work Together

Looking for a Data Analyst to help with business intelligence, marketing analytics, dashboards, AI, or automation?

keyboard_arrow_down