Deepening Analytical Querying: Transitioning from Pandas to SQL for Data Analysis

Why Data Scientists Need Strong SQL Foundations

While Pandas is unmatched for exploratory analysis and statistical prototyping in Jupyter Notebooks, production datasets live in enterprise relational databases and data warehouses. Extracting, filtering, and joining terabytes of data before loading into memory requires clean, optimized SQL.

As part of my current upskilling roadmap, I have been practicing advanced SQL on platforms like SQLZoo and Mode Analytics.

Essential Patterns for Data Analytics

Understanding how SQL declarative queries map directly to Pandas DataFrame methods unlocks seamless end-to-end data workflows:

-- Calculating Monthly Running Totals & 3-Month Moving Averages
WITH MonthlySales AS (
  SELECT 
    DATE_TRUNC('month', order_date) AS order_month,
    category,
    SUM(sale_amount) AS total_revenue
  FROM orders
  GROUP BY 1, 2
)
SELECT 
  order_month,
  category,
  total_revenue,
  SUM(total_revenue) OVER (
    PARTITION BY category 
    ORDER BY order_month
  ) AS cumulative_revenue,
  AVG(total_revenue) OVER (
    PARTITION BY category 
    ORDER BY order_month 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3m
FROM MonthlySales
ORDER BY category, order_month;

Combining SQL for extraction and aggregation with Python for statistical modeling and visualization provides the ideal hybrid stack for real-world data science.