Advanced SQL Topics

Advanced SQL techniques for data science.

Table of Contents

• Advanced Window Functions
• Query Optimization
• Indexes
• Transactions
• Common Pitfalls

Advanced Window Functions

Frames

-- Rows between
SELECT 
    first_name,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM employees;

Percentiles

SELECT 
    first_name,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS percentile_rank
FROM employees;

Query Optimization

EXPLAIN

EXPLAIN SELECT * FROM employees WHERE department_id = 1;

Indexes

-- Create index
CREATE INDEX idx_department ON employees(department_id);

-- Composite index
CREATE INDEX idx_name_dept ON employees(first_name, department_id);

NoSQL Databases

Introduction to NoSQL

NoSQL (Not Only SQL) databases are non-relational databases designed for specific data models and have flexible schemas. They're often used for:

• Large-scale applications
• Real-time data
• Unstructured data
• High-performance requirements

When to Use NoSQL vs SQL

Use SQL when:

• Structured data with relationships
• ACID transactions required
• Complex queries and joins
• Data consistency is critical
• Traditional business applications

Use NoSQL when:

• Unstructured or semi-structured data
• High scalability needed
• Fast read/write performance
• Flexible schema requirements
• Big data applications

Types of NoSQL Databases

1. Document Databases (MongoDB)

Store data as documents (JSON-like structures).

MongoDB Example:

from pymongo import MongoClient

# Connect to MongoDB
client = MongoClient('mongodb://localhost:27017/')
db = client['mydatabase']
collection = db['users']

# Insert document
user = {
    'name': 'John Doe',
    'email': '[email protected]',
    'age': 30,
    'address': {
        'street': '123 Main St',
        'city': 'New York'
    }
}
collection.insert_one(user)

# Query documents
users = collection.find({'age': {'$gt': 25}})
for user in users:
    print(user)

# Update document
collection.update_one(
    {'name': 'John Doe'},
    {'$set': {'age': 31}}
)

Use Cases:

• Content management
• User profiles
• Catalogs
• Real-time analytics

2. Key-Value Stores (Redis)

Simple key-value pairs, extremely fast.

Redis Example:

import redis

# Connect to Redis
r = redis.Redis(host='localhost', port=6379, db=0)

# Set value
r.set('user:1', 'John Doe')
r.set('user:1:email', '[email protected]')

# Get value
name = r.get('user:1')
print(name.decode('utf-8'))

# Set with expiration
r.setex('session:abc123', 3600, 'user_data')

# Lists
r.lpush('tasks', 'task1', 'task2', 'task3')
tasks = r.lrange('tasks', 0, -1)

Use Cases:

• Caching
• Session storage
• Real-time leaderboards
• Message queues

3. Column-Family Stores (Cassandra)

Store data in columns grouped by column families.

Cassandra Example:

from cassandra.cluster import Cluster

# Connect to Cassandra
cluster = Cluster(['127.0.0.1'])
session = cluster.connect('mykeyspace')

# Insert data
session.execute(
    "INSERT INTO users (id, name, email) VALUES (?, ?, ?)",
    (1, 'John Doe', '[email protected]')
)

# Query data
rows = session.execute("SELECT * FROM users WHERE id = ?", (1,))
for row in rows:
    print(row.name, row.email)

Use Cases:

• Time-series data
• IoT applications
• High write throughput
• Distributed systems

4. Graph Databases (Neo4j)

Store data as nodes and relationships.

Neo4j Example:

from neo4j import GraphDatabase

# Connect to Neo4j
driver = GraphDatabase.driver("bolt://localhost:7687", auth=("neo4j", "password"))

def create_person(tx, name):
    tx.run("CREATE (p:Person {name: $name})", name=name)

def create_friendship(tx, name1, name2):
    tx.run("""
        MATCH (a:Person {name: $name1})
        MATCH (b:Person {name: $name2})
        CREATE (a)-[:FRIENDS_WITH]->(b)
    """, name1=name1, name2=name2)

with driver.session() as session:
    session.write_transaction(create_person, "Alice")
    session.write_transaction(create_person, "Bob")
    session.write_transaction(create_friendship, "Alice", "Bob")

Use Cases:

• Social networks
• Recommendation systems
• Fraud detection
• Knowledge graphs

NoSQL with Python

MongoDB:

pip install pymongo

Redis:

pip install redis

Cassandra:

pip install cassandra-driver

Neo4j:

pip install neo4j

Comparison Table

Choosing the Right Database

Questions to Ask:

1. What is the data structure?
2. What are the access patterns?
3. What is the scale requirement?
4. What consistency level is needed?
5. What is the query complexity?

Transactions

Transactions ensure your database stays consistent when multiple operations must succeed or fail together.

ACID (what to remember)

• Atomicity: all operations succeed or none do
• Consistency: constraints remain valid
• Isolation: concurrent transactions don’t corrupt each other
• Durability: once committed, data survives crashes

Basic transaction pattern

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

COMMIT;

If something fails in the middle:

ROLLBACK;

Isolation levels (intuition)

Different isolation levels trade off correctness vs performance.

• READ COMMITTED: prevents dirty reads (common default)
• REPEATABLE READ: stable reads within a transaction
• SERIALIZABLE: strongest (can be slower, may retry)

Practical tips

• Keep transactions short (avoid long locks)
• Index the columns you use in WHERE / JOIN for write-heavy systems
• If you hit deadlocks, retry the transaction (application-level retry)

Common Pitfalls

1) Using SELECT * in analytics pipelines

• Pulls unnecessary columns → slower and brittle when schemas change
• Prefer selecting only needed columns

2) Non-sargable filters (indexes can’t help)

Bad (often prevents index usage):

WHERE DATE(created_at) = '2026-01-01'

Better:

WHERE created_at >= '2026-01-01'
  AND created_at <  '2026-01-02'

3) Accidental cross joins

Always use explicit join conditions:

SELECT *
FROM a
JOIN b ON a.id = b.a_id;

4) Misunderstanding NULL

• NULL is “unknown”, so comparisons behave differently
• Use IS NULL / IS NOT NULL

5) Overusing DISTINCT

DISTINCT can hide data issues and be expensive. Prefer fixing joins/keys.

Key Takeaways

1. Optimize Queries: Use indexes and EXPLAIN
2. Window Functions: Powerful for analytics
3. Transactions: Ensure data consistency
4. NoSQL: Choose based on use case and data structure

Try next: Rewrite one analytics query with a CTE. Time it. Then decide if indexes matter.