データアナリスト面接対策2026年版：SQL・Python・分析スキル完全ガイド

SELECT 
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 / 
        NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2
    ) AS growth_rate_pct
FROM monthly_sales
ORDER BY month;

WITH customer_metrics AS (
    SELECT 
        customer_id,
        DATEDIFF(CURRENT_DATE, MAX(order_date)) AS recency,
        COUNT(DISTINCT order_id) AS frequency,
        SUM(amount) AS monetary
    FROM orders
    WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
    GROUP BY customer_id
),
rfm_scores AS (
    SELECT 
        customer_id,
        NTILE(5) OVER (ORDER BY recency DESC) AS r_score,
        NTILE(5) OVER (ORDER BY frequency) AS f_score,
        NTILE(5) OVER (ORDER BY monetary) AS m_score
    FROM customer_metrics
)
SELECT 
    customer_id,
    r_score,
    f_score,
    m_score,
    CONCAT(r_score, f_score, m_score) AS rfm_segment
FROM rfm_scores
ORDER BY monetary DESC;

import pandas as pd
import numpy as np
from sklearn.impute import SimpleImputer

def handle_missing_data(df: pd.DataFrame) -> pd.DataFrame:
    """Handle missing values with appropriate strategies."""
    df_clean = df.copy()
    
    # Numeric columns: fill with median
    numeric_cols = df_clean.select_dtypes(include=[np.number]).columns
    for col in numeric_cols:
        median_val = df_clean[col].median()
        df_clean[col] = df_clean[col].fillna(median_val)
    
    # Categorical columns: fill with mode
    categorical_cols = df_clean.select_dtypes(include=['object']).columns
    for col in categorical_cols:
        mode_val = df_clean[col].mode()[0]
        df_clean[col] = df_clean[col].fillna(mode_val)
    
    return df_clean

# Feature engineering example
def create_date_features(df: pd.DataFrame, date_col: str) -> pd.DataFrame:
    """Extract useful features from datetime column."""
    df[date_col] = pd.to_datetime(df[date_col])
    df['year'] = df[date_col].dt.year
    df['month'] = df[date_col].dt.month
    df['day_of_week'] = df[date_col].dt.dayofweek
    df['is_weekend'] = df['day_of_week'].isin([5, 6]).astype(int)
    df['quarter'] = df[date_col].dt.quarter
    
    return df

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

def cohort_analysis(df: pd.DataFrame) -> pd.DataFrame:
    """Perform cohort analysis for customer retention."""
    # Get the first purchase month for each customer
    df['order_month'] = pd.to_datetime(df['order_date']).dt.to_period('M')
    cohort = df.groupby('customer_id')['order_month'].min().reset_index()
    cohort.columns = ['customer_id', 'cohort_month']
    
    df = df.merge(cohort, on='customer_id')
    
    # Calculate cohort index (months since first purchase)
    df['cohort_index'] = (
        df['order_month'].astype(int) - df['cohort_month'].astype(int)
    )
    
    # Create cohort table
    cohort_data = df.groupby(['cohort_month', 'cohort_index']).agg(
        n_customers=('customer_id', 'nunique')
    ).reset_index()
    
    cohort_pivot = cohort_data.pivot(
        index='cohort_month',
        columns='cohort_index',
        values='n_customers'
    )
    
    # Calculate retention rates
    cohort_size = cohort_pivot.iloc[:, 0]
    retention = cohort_pivot.divide(cohort_size, axis=0) * 100
    
    return retention

def plot_cohort_heatmap(retention: pd.DataFrame) -> None:
    """Visualize cohort retention as heatmap."""
    plt.figure(figsize=(12, 8))
    sns.heatmap(
        retention,
        annot=True,
        fmt='.1f',
        cmap='YlGnBu',
        cbar_kws={'label': 'Retention Rate (%)'}
    )
    plt.title('Customer Retention by Cohort')
    plt.xlabel('Months Since First Purchase')
    plt.ylabel('Cohort Month')
    plt.tight_layout()
    plt.show()

import scipy.stats as stats
import numpy as np

def calculate_sample_size(
    baseline_rate: float,
    mde: float,
    alpha: float = 0.05,
    power: float = 0.8
) -> int:
    """Calculate required sample size for A/B test."""
    effect_size = mde / np.sqrt(baseline_rate * (1 - baseline_rate))
    
    z_alpha = stats.norm.ppf(1 - alpha / 2)
    z_beta = stats.norm.ppf(power)
    
    n = 2 * ((z_alpha + z_beta) / effect_size) ** 2
    
    return int(np.ceil(n))

def analyze_ab_test(
    control_conversions: int,
    control_visitors: int,
    treatment_conversions: int,
    treatment_visitors: int
) -> dict:
    """Analyze A/B test results with statistical significance."""
    control_rate = control_conversions / control_visitors
    treatment_rate = treatment_conversions / treatment_visitors
    
    # Pooled proportion
    pooled = (control_conversions + treatment_conversions) / \
             (control_visitors + treatment_visitors)
    
    # Standard error
    se = np.sqrt(pooled * (1 - pooled) * 
                 (1/control_visitors + 1/treatment_visitors))
    
    # Z-score and p-value
    z_score = (treatment_rate - control_rate) / se
    p_value = 2 * (1 - stats.norm.cdf(abs(z_score)))
    
    # Confidence interval
    ci_95 = 1.96 * se
    lift = (treatment_rate - control_rate) / control_rate * 100
    
    return {
        'control_rate': round(control_rate, 4),
        'treatment_rate': round(treatment_rate, 4),
        'lift_pct': round(lift, 2),
        'p_value': round(p_value, 4),
        'is_significant': p_value < 0.05,
        'confidence_interval': (round(-ci_95, 4), round(ci_95, 4))
    }

import pandas as pd
import numpy as np
from scipy import stats

def correlation_analysis(df: pd.DataFrame, target_col: str) -> pd.DataFrame:
    """Analyze correlations between features and target."""
    numeric_df = df.select_dtypes(include=[np.number])
    
    correlations = []
    for col in numeric_df.columns:
        if col != target_col:
            corr, p_value = stats.pearsonr(
                numeric_df[col].dropna(),
                numeric_df[target_col].dropna()
            )
            correlations.append({
                'feature': col,
                'correlation': round(corr, 4),
                'p_value': round(p_value, 4),
                'abs_correlation': abs(corr)
            })
    
    result = pd.DataFrame(correlations)
    result = result.sort_values('abs_correlation', ascending=False)
    
    return result

def check_multicollinearity(df: pd.DataFrame) -> pd.DataFrame:
    """Check for multicollinearity using VIF."""
    from statsmodels.stats.outliers_influence import variance_inflation_factor
    
    numeric_df = df.select_dtypes(include=[np.number]).dropna()
    
    vif_data = pd.DataFrame()
    vif_data['feature'] = numeric_df.columns
    vif_data['VIF'] = [
        variance_inflation_factor(numeric_df.values, i)
        for i in range(numeric_df.shape[1])
    ]
    
    return vif_data.sort_values('VIF', ascending=False)

import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import StandardScaler
from sklearn.ensemble import RandomForestClassifier
from sklearn.metrics import classification_report, roc_auc_score

def build_churn_model(df: pd.DataFrame) -> dict:
    """Build and evaluate a churn prediction model."""
    # Feature selection
    features = [
        'tenure_months',
        'monthly_charges',
        'total_charges',
        'num_support_tickets',
        'days_since_last_login',
        'contract_type_encoded',
        'payment_method_encoded'
    ]
    
    X = df[features]
    y = df['churned']
    
    # Split data
    X_train, X_test, y_train, y_test = train_test_split(
        X, y, test_size=0.2, random_state=42, stratify=y
    )
    
    # Scale features
    scaler = StandardScaler()
    X_train_scaled = scaler.fit_transform(X_train)
    X_test_scaled = scaler.transform(X_test)
    
    # Train model
    model = RandomForestClassifier(
        n_estimators=100,
        max_depth=10,
        class_weight='balanced',
        random_state=42
    )
    model.fit(X_train_scaled, y_train)
    
    # Evaluate
    y_pred = model.predict(X_test_scaled)
    y_prob = model.predict_proba(X_test_scaled)[:, 1]
    
    # Feature importance
    importance = pd.DataFrame({
        'feature': features,
        'importance': model.feature_importances_
    }).sort_values('importance', ascending=False)
    
    return {
        'auc_score': roc_auc_score(y_test, y_prob),
        'classification_report': classification_report(y_test, y_pred),
        'feature_importance': importance
    }