Niveau 5 : Entretiens et Tests Techniques

1-- Exercice : Analyse des Ventes E-commerce
2-- Calcul des métriques de vente par mois pour 2024
3
4SELECT 
5    DATE_FORMAT(o.order_date, '%Y-%m') as month,
6    COUNT(DISTINCT o.order_id) as total_orders,
7    SUM(o.quantity * o.unit_price) as total_revenue,
8    ROUND(SUM(o.quantity * o.unit_price) / COUNT(DISTINCT o.order_id), 2) as avg_order_value,
9    COUNT(DISTINCT o.user_id) as unique_customers,
10    -- Window function pour calculer la croissance mensuelle
11    LAG(SUM(o.quantity * o.unit_price)) OVER (ORDER BY DATE_FORMAT(o.order_date, '%Y-%m')) as prev_month_revenue,
12    ROUND(
13        (SUM(o.quantity * o.unit_price) - LAG(SUM(o.quantity * o.unit_price)) OVER (ORDER BY DATE_FORMAT(o.order_date, '%Y-%m'))) 
14        / LAG(SUM(o.quantity * o.unit_price)) OVER (ORDER BY DATE_FORMAT(o.order_date, '%Y-%m')) * 100, 2
15    ) as revenue_growth_pct
16FROM orders o
17WHERE o.order_date >= '2024-01-01' 
18    AND o.order_date < '2025-01-01'
19    AND o.status = 'completed'
20GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
21ORDER BY month;

1import pandas as pd
2from sqlalchemy import create_engine
3import logging
4from datetime import datetime, timedelta
5from typing import Dict, List, Optional
6
7class DataPipelineOrchestrator:
8    """
9    Pipeline ETL robuste pour traitement de données e-commerce
10    Utilisé dans les entretiens pour tester la conception de systèmes
11    """
12    
13    def __init__(self, source_config: Dict, target_config: Dict):
14        self.source_engine = create_engine(source_config['connection_string'])
15        self.target_engine = create_engine(target_config['connection_string'])
16        self.logger = self._setup_logger()
17        
18    def _setup_logger(self) -> logging.Logger:
19        logger = logging.getLogger(__name__)
20        logger.setLevel(logging.INFO)
21        handler = logging.StreamHandler()
22        formatter = logging.Formatter(
23            '%(asctime)s - %(name)s - %(levelname)s - %(message)s'
24        )
25        handler.setFormatter(formatter)
26        logger.addHandler(handler)
27        return logger
28    
29    def extract_daily_orders(self, date: str) -> pd.DataFrame:
30        """Extract des commandes pour une date donnée"""
31        query = '''
32        SELECT 
33            o.order_id,
34            o.user_id,
35            o.product_id,
36            o.quantity,
37            o.unit_price,
38            o.order_date,
39            o.region,
40            p.category,
41            p.brand,
42            u.user_type
43        FROM orders o
44        JOIN products p ON o.product_id = p.product_id
45        JOIN users u ON o.user_id = u.user_id
46        WHERE DATE(o.order_date) = %s
47            AND o.status = 'completed'
48        '''
49        
50        try:
51            df = pd.read_sql_query(query, self.source_engine, params=[date])
52            self.logger.info(f"Extracted {len(df)} orders for {date}")
53            return df
54        except Exception as e:
55            self.logger.error(f"Failed to extract data for {date}: {e}")
56            raise