SQL_1.sql

/*

PROJET 5 : SEGMENTEZ DES CLIENTS D'UN SITE e-COMMERCE

PARTIE 1 : REPONDEZ AUX REQUETES SQL

*/

1- Requête 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 ?
*/


with last_date_table as (
    select max (order_purchase_timestamp) as last_date
    from orders
    where order_status = 'delivered'
),

delivered_command as (
    select 
        order_id,
        order_purchase_timestamp,
        order_delivered_customer_date,
        order_estimated_delivery_date
    from orders
    where order_status = 'delivered'  /*(and order_status not like 'canceled)*/
    ),
   
recent_command as(
    select 
        order_id,
        order_delivered_customer_date,
        order_estimated_delivery_date    
    from delivered_command
    where order_purchase_timestamp >=
        (select date(last_date, '-3 months')
        from last_date_table)
    ),

delivery_customer as(
    select 
        order_id
    from recent_command
    where julianday(order_delivered_customer_date) - julianday(order_estimated_delivery_date) >= 3
)
    
select order_id from delivery_customer
;


/*
résultats :  447 commandes
*/



2- Requête 2

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


with delivered_command as (
    select 
        i.seller_id, 
        i.price
    from orders o
    join order_items i
    using (order_id)
    where order_status = 'delivered'
    ),

sales as(    
    select 
        seller_id, 
        sum(price) as total_sales
    from delivered_command
    group by seller_id
)

   
select * 
from  sales
where total_sales > 100000
order by total_sales desc

;

/*
résultats : 17 vendeurs
*/



3- Requête 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 last_date_table as (
    select max (order_purchase_timestamp) as last_date
    from orders
    where order_status = 'delivered'
),

best_sellers as(
    select 
        seller_id, 
        order_id, 
        count(order_id) as nb_sales
    from order_items
    group by seller_id
    having nb_sales>30
),

first_sale as(
    select 
        seller_id, 
        min(order_purchase_timestamp) as first_sale
    from best_sellers
    join orders
    using (order_id)
    group by seller_id
)

select * from first_sale
where first_sale >= 
        (select date(last_date, '-3 months')
        from last_date_table)
order by first_sale 
;


/*
resultat : 133 vendeurs
*/



4- Requête 4

/*
Quels sont les 5 codes postaux, enregistrant plus de 30 reviews, avec le pire review score moyen sur les 12 derniers mois ?
*/


with last_date_table as (
    select max (order_purchase_timestamp) as last_date
    from orders
),

code_customers as(
    select 
        i.customer_zip_code_prefix, 
        j.order_id
    from customers i
    join orders j
    using (customer_id)
    where j.order_purchase_timestamp >=
        (select date(last_date, '-12 months')
        from last_date_table)
),

reviews_table as(
    select 
        n.review_id, 
        m.customer_zip_code_prefix, 
        n.review_score
    from code_customers m
    join order_reviews n
    using (order_id)
)

select 
    customer_zip_code_prefix, 
    count (review_id) as nb_reviews, 
    avg (review_score) as score_moy
from reviews_table
group by customer_zip_code_prefix
having nb_reviews > 30
order by score_moy limit 5

;


/*

résultats : 

22753	47	2.8085106382978724
22770	37	3.135135135135135
22793	90	3.2333333333333334
21321	36	3.2777777777777777
22780	37	3.3513513513513513

*/