2026年イタリアのデータアナリスト面接対策：SQL、Python、ケーススタディの実践ガイド

-- 商品カテゴリ別の日次売上累計を計算
SELECT 
    order_date,
    category,
    daily_revenue,
    SUM(daily_revenue) OVER (
        PARTITION BY category 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue
FROM (
    SELECT 
        DATE(order_timestamp) AS order_date,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS daily_revenue
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    GROUP BY DATE(order_timestamp), p.category
) daily_sales
ORDER BY category, order_date;

-- 四半期ごとに支出が20%以上増加した顧客を特定
WITH quarterly_spending AS (
    SELECT 
        customer_id,
        DATE_TRUNC('quarter', order_date) AS quarter,
        SUM(total_amount) AS quarter_total
    FROM orders
    WHERE order_date >= '2025-01-01'
    GROUP BY customer_id, DATE_TRUNC('quarter', order_date)
),
spending_growth AS (
    SELECT 
        customer_id,
        quarter,
        quarter_total,
        LAG(quarter_total) OVER (
            PARTITION BY customer_id ORDER BY quarter
        ) AS prev_quarter_total
    FROM quarterly_spending
)
SELECT 
    customer_id,
    quarter,
    quarter_total,
    prev_quarter_total,
    ROUND(
        (quarter_total - prev_quarter_total) / prev_quarter_total * 100, 
        2
    ) AS growth_pct
FROM spending_growth
WHERE prev_quarter_total IS NOT NULL
  AND (quarter_total - prev_quarter_total) / prev_quarter_total >= 0.20
ORDER BY growth_pct DESC;

import pandas as pd
import numpy as np

def clean_sales_data(df: pd.DataFrame) -> pd.DataFrame:
    """イタリアのERPエクスポートから生の売上データをクリーニング"""
    cleaned = df.copy()
    
    # イタリアの数値フォーマット: 1.234,56 -> 1234.56
    if cleaned['revenue'].dtype == 'object':
        cleaned['revenue'] = (
            cleaned['revenue']
            .str.replace('.', '', regex=False)
            .str.replace(',', '.', regex=False)
            .astype(float)
        )
    
    # イタリアの日付フォーマットを解析: 15/09/2026
    cleaned['order_date'] = pd.to_datetime(
        cleaned['order_date'], 
        format='%d/%m/%Y',
        errors='coerce'
    )
    
    cleaned['customer_segment'] = cleaned['customer_segment'].fillna('Unknown')
    cleaned['revenue'] = cleaned['revenue'].fillna(0)
    
    cleaned = cleaned.drop_duplicates(
        subset=['order_id'], 
        keep='first'
    )
    
    return cleaned

import pandas as pd

def calculate_customer_metrics(orders_df: pd.DataFrame) -> pd.DataFrame:
    """RFM分析用の顧客レベルメトリクスを計算"""
    reference_date = orders_df['order_date'].max()
    
    metrics = orders_df.groupby('customer_id').agg(
        recency=('order_date', lambda x: (reference_date - x.max()).days),
        frequency=('order_id', 'nunique'),
        monetary=('revenue', 'sum'),
        avg_order_value=('revenue', 'mean'),
        first_order=('order_date', 'min'),
        last_order=('order_date', 'max')
    ).reset_index()
    
    metrics['customer_lifetime'] = (
        metrics['last_order'] - metrics['first_order']
    ).dt.days
    
    return metrics

import pandas as pd

def analyze_funnel(sessions_df: pd.DataFrame) -> pd.DataFrame:
    """デバイスと月別のコンバージョンファネルを計算"""
    sessions_df['month'] = sessions_df['session_start'].dt.to_period('M')
    
    funnel = sessions_df.groupby(['month', 'device_type']).agg(
        sessions=('session_id', 'nunique'),
        product_views=('viewed_product', 'sum'),
        add_to_cart=('added_to_cart', 'sum'),
        checkouts=('started_checkout', 'sum'),
        purchases=('completed_purchase', 'sum')
    ).reset_index()
    
    funnel['view_rate'] = funnel['product_views'] / funnel['sessions']
    funnel['cart_rate'] = funnel['add_to_cart'] / funnel['product_views']
    funnel['checkout_rate'] = funnel['checkouts'] / funnel['add_to_cart']
    funnel['purchase_rate'] = funnel['purchases'] / funnel['checkouts']
    funnel['overall_conversion'] = funnel['purchases'] / funnel['sessions']
    
    return funnel