Untitled

mail@pastecode.io avatar
unknown
pgsql
a year ago
3.4 kB
2
Indexable
Never
select
    rfm as name,
    count(1) :: int as loc
from
    (
        select
            client_id,
            case
                when rfm_recency || rfm_frequency || rfm_monetary IN ('555', '554', '544', '545', '454', '455', '445')
                    then 'Campeões'
                when rfm_recency || rfm_frequency || rfm_monetary IN ('543', '444', '435', '355', '354', '345', '344', '335')
                    then 'Clientes leais'
                when rfm_recency || rfm_frequency || rfm_monetary IN ('553', '551', '552', '541', '542', '533', '532', '531', '452', '451', '442', '441', '431', '453', '433', '432', '423', '353', '352', '351', '342', '341', '333', '323')
                    then 'Potenciais clientes leais'
                when rfm_recency || rfm_frequency || rfm_monetary IN ('512', '511', '422', '421' '412', '411', '311')
                    then 'Clientes recentes'
                when rfm_recency || rfm_frequency || rfm_monetary IN ('525', '524', '523', '522', '521', '515', '514', '513', '425','424', '413','414','415', '315', '314', '313')
                    then 'Promissores'
                when rfm_recency || rfm_frequency || rfm_monetary IN ('535', '534', '443', '434', '343', '334', '325', '324')
                    then 'Precisam de Atenção'
                when rfm_recency || rfm_frequency || rfm_monetary IN ('331', '321', '312', '221', '213', '231', '241', '251')
                    then 'Prestes a dormir'
                when rfm_recency || rfm_frequency || rfm_monetary IN ('255', '254', '245', '244', '253', '252', '243', '242', '235', '234', '225', '224', '153', '152', '145', '143', '142', '135', '134', '133', '125', '124')
                    then 'Em risco'
                when rfm_recency || rfm_frequency || rfm_monetary IN ('155', '154', '144', '214','215','115', '114', '113')
                    then 'Não pode perdê-los'
                when rfm_recency || rfm_frequency || rfm_monetary IN ('332', '322', '233', '232', '223', '222', '132', '123', '122', '212', '211')
                    then 'Hibernando'
                when rfm_recency || rfm_frequency || rfm_monetary IN ('111', '112', '121', '131','141','151')
                    then 'Perdidos'
            end as rfm
        from
            (
                select
                    client_id,
                    NTILE(5) OVER ( ORDER BY last_order_date ) :: text AS rfm_recency,
                    NTILE(5) OVER ( ORDER BY count_order ) ::text AS rfm_frequency,
                    NTILE(5) OVER ( ORDER BY total_price ) ::text AS rfm_monetary
                from
                    (
                        select
                            pb."clientId" as client_id,
                            max("dataSaida" :: date) as last_order_date,
                            count(0) as count_order,
                            sum(totalvenda) as total_price
                        from
                            pedido_bling pb
                            join cliente_bling cb on cb.id = pb."clientId"
                        where pb."dataSaida" > (CURRENT_DATE - interval '12 months')
                            and pb."dashboardId" = 'clihdi9y60003mo084q15vvav'
                        group by
                            pb."clientId"
                    ) as rfm
            ) as final_rfm
    ) as final
group by rfm