Skip to content

Files

Latest commit

 

History

History

Customer_analysis

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
 
 
 
 

Customer Analysis**

%load_ext sql
import os
The sql extension is already loaded. To reload it, use:
  %reload_ext sql
host = "localhost"
database = "olist"
user = "postgres"
password = "sql123"
connection_string = f"postgresql://{user}:{password}@{host}/{database}"
%sql $connection_string
'Connected: postgres@olist'

Number of unique customer by state

%%sql
SELECT customer_state,
       COUNT(customer_unique_id) AS no_of_customers
FROM customers
GROUP BY customer_state
ORDER BY no_of_customers DESC
 * postgresql://postgres:***@localhost/olist
27 rows affected.
customer_state no_of_customers
SP 41746
RJ 12852
MG 11635
RS 5466
PR 5045
SC 3637
BA 3380
DF 2140
ES 2033
GO 2020
PE 1652
CE 1336
PA 975
MT 907
MA 747
MS 715
PB 536
PI 495
RN 485
AL 413
SE 350
TO 280
RO 253
AM 148
AC 81
AP 68
RR 46

Top 10 product categories most ordered by customers

%%sql
WITH customer_items AS (SELECT *
                        FROM customers
                        JOIN orders USING(customer_id)
                        JOIN order_items USING(order_id)
                        JOIN products USING(product_id))
SELECT product_category,
       SUM(order_item_id) AS units
FROM customer_items
GROUP BY product_category
ORDER BY units DESC
LIMIT 10
 * postgresql://postgres:***@localhost/olist
10 rows affected.
product_category units
cama_mesa_banho 13665
moveis_decoracao 11540
beleza_saude 11081
esporte_lazer 9932
informatica_acessorios 9874
utilidades_domesticas 9051
relogios_presentes 6594
ferramentas_jardim 5874
telefonia 5161
automotivo 4881

Average customer order price by state

%%sql
WITH order_price AS(SELECT customer_id,
                           customer_state,
                           (order_item_id*price + freight_value)::real AS total_price
                    FROM customers
                    JOIN orders USING(customer_id)
                    JOIN order_items USING(order_id)
                    JOIN products USING(product_id))
SELECT customer_state,
       AVG(total_price)::real
FROM order_price
GROUP BY customer_state
ORDER BY avg DESC
 * postgresql://postgres:***@localhost/olist
27 rows affected.
customer_state avg
PB 254.92203
AC 235.44011
AL 230.23581
AP 224.04231
PI 219.86848
RO 216.66422
PA 216.15749
RR 210.93135
TO 207.69473
CE 206.16673
SE 205.05421
MT 203.74744
RN 202.12102
MA 198.72446
PE 188.99063
AM 183.80533
MS 180.0531
BA 179.83725
GO 178.20496
RJ 164.28613
SC 162.26056
DF 160.20909
ES 159.0866
RS 158.83072
PR 157.74991
MG 155.7808
SP 139.50151

Frequency credit card payment by state

%%sql
WITH payment AS(SELECT customer_state,
                       payment_type
                FROM orders AS od
                JOIN customers AS cu USING(customer_id)
                JOIN order_payments AS pay USING(order_id))
SELECT customer_state,
       payment_type,
       COUNT(*)
FROM payment
WHERE payment_type = 'credit_card'
GROUP BY customer_state, payment_type
ORDER BY count DESC
 * postgresql://postgres:***@localhost/olist
27 rows affected.
customer_state payment_type count
SP credit_card 32168
RJ credit_card 10288
MG credit_card 9070
RS credit_card 3985
PR credit_card 3786
SC credit_card 2713
BA credit_card 2662
DF credit_card 1700
ES credit_card 1573
GO credit_card 1520
PE credit_card 1334
CE credit_card 1091
PA credit_card 728
MT credit_card 659
MA credit_card 535
MS credit_card 519
PB credit_card 428
RN credit_card 394
PI credit_card 389
AL credit_card 341
SE credit_card 264
TO credit_card 197
RO credit_card 186
AM credit_card 124
AC credit_card 61
AP credit_card 47
RR credit_card 33

Proportion of customers paying in more than one installment by state

%%sql
SELECT customer_state,
       count,
       (count/total_order) AS proportion
FROM(WITH payment AS(SELECT customer_state,
                            payment_installments
                     FROM orders AS od
                     JOIN customers AS cu USING(customer_id)
                     JOIN order_payments AS pay USING(order_id)
                     WHERE payment_installments > 1)
    SELECT customer_state,
           COUNT(payment_installments)::real AS count,
           (SELECT COUNT(order_id) FROM orders)::real AS total_order
    FROM payment
    GROUP BY customer_state
    ORDER BY count DESC) AS g2
WHERE count > 1000
ORDER BY proportion DESC
 * postgresql://postgres:***@localhost/olist
11 rows affected.
customer_state count proportion
SP 19970.0 0.20082259
RJ 7055.0 0.07094659
MG 6315.0 0.063504994
RS 2777.0 0.027926108
PR 2500.0 0.025140535
BA 1977.0 0.019881135
SC 1816.0 0.018262085
ES 1110.0 0.011162397
GO 1094.0 0.011001498
DF 1062.0 0.0106796995
PE 1042.0 0.010478575