SQL for Data Engineering: Joins, Aggregations & Window Functions

The universal language of data. Learn SELECT, WHERE, ORDER BY, LIMIT; joins (INNER, LEFT, RIGHT, FULL, CROSS, self-joins) and join pitfalls (duplicates, NULLs); GROUP BY with aggregates and HAVING; subqueries and CTEs; window functions (ROW_NUMBER, RANK, running totals, LAG/LEAD, moving averages); CASE expressions; NULL semantics; logical query order; deduplication and upserts (MERGE); indexes and query plans — with runnable SQL on SQLite computing revenue by city, running totals and ranks.

SQL is the most important tool of data engineers and analysts: nearly every warehouse, lakehouse and many streaming systems speak it. Mastery of joins, aggregations and window functions solves most transformation tasks.

SQL is declarative — you describe the result you want, and the query engine decides how to compute it efficiently.

An analogy

SQL is like asking a librarian precise questions: 'bring me all books by these authors (JOIN), only after 2020 (WHERE), counted per genre (GROUP BY), ranked by popularity (window)' — you don't tell them which shelves to walk to.

Core query building blocks

SELECT chooses columns and expressions; FROM names tables; WHERE filters rows; ORDER BY sorts; LIMIT restricts output. Aggregates (COUNT, SUM, AVG, MIN, MAX) with GROUP BY summarise groups; HAVING filters groups after aggregation. CASE WHEN creates conditional logic (e.g. bucket ages or flag high-value orders).

Joins

Join types:

CTEs and window functions

WITH clauses name intermediate steps, making complex transformations readable and testable. Window functions compute over a 'window' of related rows without collapsing them: ranking (ROW_NUMBER, RANK, DENSE_RANK), running and moving aggregates (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), comparisons (LAG/LEAD for period-over-period change), and top-N per group. They are central to deduplication, sessionisation and SCD logic.

Correctness and performance

NULL semantics: NULL = NULL is unknown (use IS NULL, COALESCE); COUNT(col) skips NULLs; NOT IN with NULLs returns nothing. Idempotent loads use MERGE/UPSERT keyed on natural or surrogate keys. Performance: read query plans (EXPLAIN), filter early, select only needed columns, partition/cluster tables, use indexes in OLTP, avoid unnecessary DISTINCT and cross joins, and watch data skew in distributed engines.

Formulas

Worked example: Customers without orders

Given: customers, orders

Find: Query.

Answer: Anti-join returns customers with no orders

Worked example: Latest record per key

Given: Table with duplicates by id

Find: Deduplicate.

Answer: One latest row per id

Worked example: Month-over-month growth

Given: Monthly revenue table

Find: Growth %.

Answer: Percentage change each month

Joins, aggregation and window functions (SQLite)

import sqlite3
# SQL for data engineering: joins, aggregation and window functions (SQLite in memory)
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers(id INTEGER PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER, order_date TEXT, amount REAL);
INSERT INTO customers VALUES (1,'Asha','Pune'),(2,'Ravi','Delhi'),(3,'Meera','Pune');
INSERT INTO orders VALUES (1,1,'2026-01-05',1200),(2,1,'2026-01-20',800),(3,2,'2026-01-11',2500),
                          (4,3,'2026-02-02',400),(5,1,'2026-02-14',1500),(6,2,'2026-02-18',300);
""")
q1 = """SELECT c.city, COUNT(*) AS orders, SUM(o.amount) AS revenue
        FROM orders o JOIN customers c ON c.id = o.customer_id
        GROUP BY c.city ORDER BY revenue DESC"""
for row in db.execute(q1):
    print("by city:", row)
q2 = """SELECT c.name, o.order_date, o.amount,
               SUM(o.amount) OVER (PARTITION BY c.id ORDER BY o.order_date) AS running_total,
               RANK() OVER (ORDER BY o.amount DESC) AS amount_rank
        FROM orders o JOIN customers c ON c.id = o.customer_id
        ORDER BY c.name, o.order_date"""
for row in db.execute(q2):
    print("window:", row)
# by city: ('Pune', 4, 3900.0)
# by city: ('Delhi', 2, 2800.0)
# window: ('Asha', '2026-01-05', 1200.0, 1200.0, 3)
# window: ('Asha', '2026-01-20', 800.0, 2000.0, 4)
# window: ('Asha', '2026-02-14', 1500.0, 3500.0, 2)
# window: ('Meera', '2026-02-02', 400.0, 400.0, 5)
# window: ('Ravi', '2026-01-11', 2500.0, 2500.0, 1)
# window: ('Ravi', '2026-02-18', 300.0, 2800.0, 6)

GROUP BY collapses orders into one row per city; window functions keep every row but add computed context — Asha's running total grows with each order, and RANK orders all six orders by amount. The same SQL runs on PostgreSQL, BigQuery, Snowflake or Spark SQL with minor dialect differences.

Common mistakes

Applications

Key takeaways

Going further