SQL_3.sql

/* PROJET 5

CREATION DE NOUVELLES FEATURES

*/


/* 

Définir une table RFM qui en plus référence :
- les catégories de produits
- les vendeurs
- les scores de satisfaction
- la localisation des consommateurs 

*/

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

table_customers as (
    select 
        customer_id,
        customer_state,
        max (order_purchase_timestamp) over (partition by customer_id) as last_sale,
        order_id
    from orders
    left join customers
    using (customer_id)

),

table_commandes as(
    select 
        customer_id,
        customer_state,
        
        last_sale,
        order_id, 
        review_score
    from table_customers
    left join order_reviews
    using (order_id)
  ),

rfm_table as(
    select 
        customer_id,
        customer_state,
        avg(review_score) as score_moy,
        count(order_id) as nb_commandes,
        sum(price) as total_commandes,
        (select julianday (last_date) from time_table) - julianday (max (last_sale)) as recence_commande,
        product_id,
        seller_id
    from table_commandes
    join order_items
    using (order_id)
    group by customer_id
),

table_finale as(
    select 
        customer_id,
        customer_state,
        score_moy,
        nb_commandes,
        total_commandes,
        recence_commande,
        ntile(5) over (order by recence_commande asc) as R_score,
        ntile(5) over (order by nb_commandes asc) as F_score,
        ntile(5) over (order by total_commandes asc) as M_score,
        product_category_name,
        seller_id
    from rfm_table
    join products
    using (product_id)
)
  
select *
from table_finale