RAYMOND_Olivier_1_script_032024.txt

Liste de requêtes SQL pour le dashboard : 
1. En excluant les commandes annulées, quelles sont les commandes récentes de moins de 3 mois que les clients ont reçues avec au moins 3 jours de retard? 

SELECT *
FROM orders
WHERE order_status != 'canceled'
AND order_purchase_timestamp >= (
    SELECT DATE(MAX(order_purchase_timestamp), '-90 day')
    FROM orders
    WHERE order_status != 'canceled'
)
AND julianday(order_delivered_customer_date) - julianday(order_estimated_delivery_date) > 3; 

--> 444 lignes



2. Qui sont les vendeurs ayant généré un chiffre d'affaires de plus de 100 000 Reals sur des commandes livrées via Olist? 

SELECT 
    order_items.seller_id, 
    SUM(order_items.price + order_items.freight_value) AS total_revenue
FROM 
    order_items
INNER JOIN 
    orders ON order_items.order_id = orders.order_id
WHERE 
    orders.order_status = 'delivered'
GROUP BY 
    order_items.seller_id
HAVING 
    total_revenue > 100000;

--> 19 lignes



3. Qui sont les nouveaux vendeurs (moins de 3 mois d'ancienneté) qui sont déjà très engagés avec la plateforme (ayant déjà vendu plus de 30 produits)? 

WITH SellerActivity AS (
    SELECT 
        seller_id,
        MIN(shipping_limit_date) AS first_order_date
    FROM 
        order_items
    GROUP BY 
        seller_id
),
MaxOrderDate AS (
    SELECT MAX(first_order_date) AS max_order_date FROM SellerActivity
)

SELECT 
    SellerActivity.seller_id,
    SellerActivity.first_order_date,
    COUNT(order_items.order_item_id) AS total_products_sold,
    CAST(JULIANDAY(MaxOrderDate.max_order_date) - JULIANDAY(SellerActivity.first_order_date) AS INTEGER) AS active_days
FROM 
    SellerActivity
INNER JOIN 
    order_items ON SellerActivity.seller_id = order_items.seller_id
INNER JOIN 
    MaxOrderDate ON 1=1
WHERE 
    SellerActivity.first_order_date >= DATE(MaxOrderDate.max_order_date, '-3 months')
GROUP BY 
    SellerActivity.seller_id, SellerActivity.first_order_date
HAVING 
    COUNT(order_items.order_item_id) > 30;

--> 10 lignes



4. Quels sont les 5 codes postaux, enregistrant plus de 30 commandes, avec le pire reviewscore moyen sur les 12 derniers mois?

SELECT 
    sellers.seller_zip_code_prefix AS postal_code,
    COUNT(order_items.order_id) AS total_orders,
    AVG(CAST(order_reviews.review_score AS FLOAT)) AS average_review_score
FROM 
    order_items
INNER JOIN 
    sellers ON order_items.seller_id = sellers.seller_id
INNER JOIN
    order_reviews ON order_items.order_id = order_reviews.order_id
WHERE 
    order_reviews.review_creation_date >= DATE((SELECT MAX(review_creation_date) FROM order_reviews), '-12 months')
GROUP BY 
    sellers.seller_zip_code_prefix
HAVING 
    COUNT(order_items.order_id) > 30
ORDER BY 
    average_review_score
LIMIT 5;

--> 5 lignes