Free Python Course for Data Analytics

# Production Analytics Pipeline: SQL Extraction -> Pandas Cleaning -> Cohort Metrics
import pandas as pd
from sqlalchemy import create_engine

# 1. Connect directly to SQL analytics warehouse and extract transaction data
engine = create_engine("postgresql://analyst:readonly@warehouse.topfolio.in:5432/analytics")
query = """
    SELECT 
        order_id,
        customer_id,
        order_date,
        amount_inr,
        order_status
    FROM ecomm.orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
"""
df = pd.read_sql(query, con=engine)

# 2. Production data cleaning and strict datatype enforcement
df['order_date'] = pd.to_datetime(df['order_date'])
df['amount_inr'] = df['amount_inr'].fillna(0.0)
df = df[df['order_status'].isin(['completed', 'delivered'])].copy()

# 3. Exploratory aggregation: 30-day cohort revenue & key business metrics
monthly_summary = df.groupby(pd.Grouper(key='order_date', freq='ME')).agg(
    total_revenue_inr=('amount_inr', 'sum'),
    orders_count=('order_id', 'nunique'),
    unique_customers=('customer_id', 'nunique'),
    average_order_value=('amount_inr', 'mean')
).round(2)

print("Recent 90-Day Cohort Performance:")
print(monthly_summary.tail(3))