📘 End-to-End Data Pipeline

📘 E-Commerce Analytics

E-Commerce analytics involves analyzing customer behavior, sales performance, and business metrics using SQL. This chapter covers key concepts, queries, and best practices for analyzing e-commerce data.

💡 Key Performance Indicators (KPIs)

• Total Revenue: Sum of all sales
• Average Order Value (AOV): Total revenue / Number of orders
• Customer Lifetime Value (CLTV): Average revenue per customer over time
• Churn Rate: Percentage of customers who stop purchasing

💡 Common SQL Queries for E-Commerce Analytics

/* Calculate Total Revenue */
SELECT SUM(order_total) AS total_revenue
FROM orders;

/* Calculate Average Order Value */
SELECT AVG(order_total) AS aov
FROM orders;

💡 Churn Analysis Queries

/* Identify Churned Customers */
WITH last_order AS (
    SELECT customer_id, MAX(order_date) AS last_order_date
    FROM orders
    GROUP BY customer_id
)
SELECT COUNT(*) AS churned_customers
FROM last_order
WHERE last_order_date < CURRENT_DATE - INTERVAL '30 days';

💡 Retention Analysis Queries

/* Calculate Customer Retention Rate */
WITH monthly_orders AS (
    SELECT customer_id, DATE_TRUNC('month', order_date) AS order_month
    FROM orders
)
SELECT order_month,
       COUNT(DISTINCT customer_id) AS total_customers,
       COUNT(*) / COUNT(DISTINCT customer_id) AS retention_rate
FROM monthly_orders
GROUP BY order_month;

✅ Best Practices for E-Commerce Analytics Queries

• Use CTEs (Common Table Expressions) for complex queries
• Optimize performance with proper indexing
• Regularly update and validate your KPI calculations
• Document all assumptions and limitations in your analysis

❌ Common Mistakes to Avoid

• Don't use COUNT(*) when counting distinct entities - use COUNT(DISTINCT)
• Avoid using raw timestamps without proper date truncation
• Don't forget to handle NULL values in your calculations
• Never assume data is clean - always validate and preprocess

💡 Advanced Analytical Techniques

For more advanced analysis, consider implementing these techniques:

• Segment customers using RFM (Recency, Frequency, Monetary) analysis
• Use window functions for time-based aggregations
• Implement machine learning models to predict churn and customer lifetime value

📘 Fraud Detection

Pattern detection in SQL is crucial for identifying suspicious activities in large datasets. This chapter will teach you how to write effective SQL queries to detect potential fraudulent transactions, identify异常行为, and implement monitoring systems.

💡 Key Patterns to Look For

• Multiple failed login attempts from the same IP address within a short time frame
• Unusual transaction amounts that deviate significantly from historical averages
• Spikes in transaction frequency during off-hours
• Duplicate transactions with identical amounts and timestamps
• Transactions from users with suspicious account names or email domains

✅ Basic Pattern Detection Queries

SELECT COUNT(*) as failed_logins
FROM login_attempts
WHERE success = FALSE
AND ip_address = '192.168.1.1'
AND attempt_time >= NOW() - INTERVAL '15 minutes';

💡 Advanced Detection Techniques

• Using regular expressions to detect suspicious patterns in text fields
• Implementing moving averages for anomaly detection
• Using window functions to analyze trends over time
• Correlating data across multiple tables (e.g., user activity, transactions)
• Setting up thresholds based on historical data

✅ Example: Detecting Unusual Transaction Amounts

WITH transaction_stats AS (
  SELECT 
    user_id,
    amount,
    AVG(amount) OVER (PARTITION BY user_id ORDER BY timestamp ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as rolling_avg
  FROM transactions
)
SELECT *
FROM transaction_stats
WHERE amount > 3 * rolling_avg;

✅ Best Practices for Fraud Detection Queries

• Always include timestamps in your analysis
• Use CTEs (Common Table Expressions) for complex logic
• Index frequently queried columns
• Monitor query performance regularly
• Implement versioning for fraud detection rules

❌ Common Pitfalls to Avoid

• Don't rely solely on simple thresholds - consider trends and patterns
• Avoid overly complex queries that are hard to maintain
• Don't ignore the possibility of false positives
• Avoid using scalar functions in WHERE clauses
• Don't forget to test your queries against historical data

📘 Finance and Ledger Systems

In finance and ledger systems, **reconciliation** is the process of ensuring that financial records are accurate and consistent. This involves verifying that all transactions have been properly recorded, matched, and settled.

💡 Key Concepts in Reconciliation

• Matching transactions between different systems
• Identifying discrepancies
• Resolving exceptions and mismatches
• Ensuring transactional integrity

SELECT transaction_id, amount, status
FROM ledger_transactions
WHERE reconciliation_status = 'unreconciled';

Transactional integrity ensures that all database operations are completed successfully or not at all. In SQL, this is achieved through **transactions** and **ACID properties (Atomicity, Consistency, Isolation, Durability).

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A123';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B456';

COMMIT;

💡 Best Practices for Financial Data Modeling

• Use normalized forms to reduce redundancy
• Implement referential integrity constraints
• Use appropriate data types (e.g., DECIMAL for monetary values)
• Partition large tables for better performance

CREATE TABLE financial_transactions (
    transaction_id UUID PRIMARY KEY,
    account_id VARCHAR(50) NOT NULL REFERENCES accounts(account_id),
    amount DECIMAL(12, 2) NOT NULL,
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'completed', 'failed') NOT NULL
);

❌ Common Mistakes in Finance Systems

• Not using proper transaction boundaries
• Storing monetary values as floating-point numbers
• Ignoring foreign key constraints
• Failing to handle currency conversions properly
• Not implementing proper audit trails

📘 Interview Problem Bank

Welcome to the SQL Interview Problem Bank! This chapter contains over 50 expert-level SQL problems designed to prepare you for real-world technical interviews. Each problem includes a detailed description, solution approach, and optimized query.

💡 What to Expect

• Problems from leading companies like Google, Amazon, and Uber
• Focus on advanced SQL concepts including window functions, CTEs, joins, and aggregations
• Real-world applications in analytics, reporting, and data engineering
• Detailed explanations of solution trade-offs

✅ How to Approach These Problems

• Understand the problem requirements before writing code
• Break down complex queries into smaller parts
• Optimize for performance and readability
• Test with sample data to validate results

❌ Common Mistakes to Avoid

• Overcomplicating solutions (keep it simple when possible)
• Ignoring NULL values in calculations
• Not considering edge cases in the data
• Writing unreadable or unoptimized queries

SELECT 
    employee_id,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

💡 Best Practices for Writing SQL in Interviews

• Start with a clear mental model of the data
• Use CTEs to break down complex logic
• Optimize for performance without sacrificing clarity
• Document your assumptions and edge cases