Integrated N CO

SELECT 
    p.product_id,
    p.sku,
    p.product_name,
    c.category_name,
    SUM(i.quantity_on_hand) as total_inventory,
    AVG(s.units_sold_30d) as avg_monthly_sales,
    CASE 
        WHEN AVG(s.units_sold_30d) > 0 
        THEN SUM(i.quantity_on_hand) / AVG(s.units_sold_30d)
        ELSE 999 
    END as months_of_inventory
FROM products p
JOIN categories c ON p.category_id = c.category_id
JOIN inventory i ON p.product_id = i.product_id
JOIN sales_metrics s ON p.product_id = s.product_id
WHERE c.category_name = 'Apparel'
GROUP BY p.product_id, p.sku, p.product_name, c.category_name
HAVING months_of_inventory > 6
ORDER BY months_of_inventory DESC;

import pandas as pd
import numpy as np
from datetime import datetime, timedelta

def analyze_order_profitability(orders_df):
    """Analyze e-commerce order profitability by channel"""
    
    # Calculate profit margins
    orders_df['profit_margin'] = (
        (orders_df['revenue'] - orders_df['cogs'] - 
         orders_df['shipping_cost'] - orders_df['marketplace_fees']) 
        / orders_df['revenue']
    ) * 100
    
    # Group by sales channel
    channel_analysis = orders_df.groupby('sales_channel').agg({
        'order_id': 'count',
        'revenue': 'sum',
        'profit_margin': 'mean',
        'customer_acquisition_cost': 'mean'
    }).round(2)
    
    # Identify top performing channels
    channel_analysis['roi'] = (
        channel_analysis['revenue'] / 
        (channel_analysis['customer_acquisition_cost'] * 
         channel_analysis['order_id'])
    ) * 100
    
    return channel_analysis.sort_values('roi', ascending=False)