Skip to content

Files

Latest commit

 

History

History

Product_analysis

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
 
 
 
 

Product Analysis

%load_ext sql
import os
host = "localhost"
database = "olist"
user = "postgres"
password = "sql123"
connection_string = f"postgresql://{user}:{password}@{host}/{database}"
%sql $connection_string
'Connected: postgres@olist'

Average length of name and description

%%sql
SELECT product_category,
       AVG(product_name_length)::int AS name_length,
       AVG(product_desc_length)::int AS desc_length
FROM products
WHERE product_category IS NOT NULL
GROUP BY product_category
ORDER BY name_length, desc_length;
 * postgresql://postgres:***@localhost/olist
73 rows affected.
product_category name_length desc_length
fashion_roupa_masculina 40 627
fashion_roupa_feminina 40 639
livros_interesse_geral 40 989
livros_tecnicos 42 1352
moveis_colchao_e_estofado 43 1111
artigos_de_natal 44 412
artes_e_artesanato 44 620
moveis_sala 44 625
market_place 44 829
consoles_games 44 850
bebidas 44 1048
fashion_roupa_infanto_juvenil 45 351
fashion_bolsas_e_acessorios 45 513
brinquedos 45 686
perfumaria 45 694
alimentos_bebidas 45 983
fraldas_higiene 46 488
fashion_esporte 46 556
moveis_quarto 46 740
bebes 46 824
dvds_blu_ray 46 1071
alimentos 46 1137
moveis_escritorio 46 1353
cds_dvds_musicais 47 117
livros_importados 47 581
malas_acessorios 47 633
artes 47 678
eletronicos 47 709
moveis_cozinha_area_de_servico_jantar_e_jardim 47 726
audio 47 782
esporte_lazer 47 954
musica 47 1060
beleza_saude 47 1137
utilidades_domesticas 48 674
informatica_acessorios 48 774
eletrodomesticos_2 48 798
sinalizacao_e_seguranca 48 817
cool_stuff 48 876
telefonia_fixa 48 1017
construcao_ferramentas_jardim 48 1135
artigos_de_festas 49 446
construcao_ferramentas_ferramentas 49 708
agro_industria_e_comercio 49 808
ferramentas_jardim 49 815
pet_shop 49 866
eletroportateis 49 934
papelaria 50 439
fashion_calcados 50 458
relogios_presentes 50 612
telefonia 50 755
instrumentos_musicais 50 795
moveis_decoracao 50 799
construcao_ferramentas_construcao 51 789
casa_construcao 51 855
cine_foto 51 939
casa_conforto_2 52 394
la_cuisine 52 422
cama_mesa_banho 52 464
flores 52 468
seguros_e_servicos 52 484
construcao_ferramentas_iluminacao 52 788
automotivo 52 814
climatizacao 52 903
fashion_underwear_e_moda_praia 52 1004
construcao_ferramentas_seguranca 52 1106
casa_conforto 53 320
portateis_casa_forno_e_cafe 53 1046
industria_comercio_e_negocios 53 1103
eletrodomesticos 54 405
portateis_cozinha_e_preparadores_de_alimentos 54 623
tablets_impressao_imagem 54 676
pcs 56 2129
pc_gamer 60 998

Average weight, length, height, width for a product category

%%sql
SELECT product_category,
       (AVG(product_weight_grams)/1000)::real AS avg_weight,
        AVG(product_length_cm)::real AS avg_length,
        AVG(product_height_cm)::real AS avg_height,
        AVG(product_width_cm)::real AS avg_width
FROM products 
WHERE product_category IS NOT NULL
GROUP BY product_category
 * postgresql://postgres:***@localhost/olist
73 rows affected.
product_category avg_weight avg_length avg_height avg_width
climatizacao 4.4599595 36.467743 23.887096 26.088709
livros_importados 0.5967742 29.741936 3.451613 21.225807
artigos_de_natal 1.8498154 28.23077 16.215385 22.830769
livros_tecnicos 1.1078455 27.325203 5.869919 18.463415
ferramentas_jardim 3.103777 30.936255 19.204515 23.504648
cine_foto 0.7957857 27.642857 11.571428 18.178572
dvds_blu_ray 0.3815625 21.270834 4.4166665 14.875
fashion_roupa_feminina 0.57222223 23.296297 11.481482 18.333334
beleza_saude 1.4347938 23.800737 15.712357 17.997545
livros_interesse_geral 0.7466111 23.481482 9.773149 19.328703
tablets_impressao_imagem 0.38144445 28.11111 9.444445 21.555555
papelaria 1.763113 29.062426 19.586573 23.34629
bebes 3.6552014 37.14706 21.617647 28.717865
musica 1.2135185 27.148148 9.925926 18.222221
consoles_games 0.6366593 21.791798 12.640379 18.473186
eletrodomesticos 1.9796541 29.805405 14.8918915 21.859459
cama_mesa_banho 2.4564052 36.77418 14.091119 30.589964
la_cuisine 4.35 67.3 13.8 34.8
seguros_e_servicos 0.8125 26.5 28.5 13.0
artes_e_artesanato 1.1645789 28.368422 9.789474 21.052631
construcao_ferramentas_iluminacao 2.2809615 27.846153 20.423077 25.73077
eletroportateis 4.0123982 29.47186 27.038961 27.30736
malas_acessorios 3.7232838 35.74212 29.584528 30.707737
flores 1.4035715 21.714285 15.571428 16.714285
artigos_de_festas 2.7057693 32.307693 16.5 22.73077
sinalizacao_e_seguranca 2.9698172 30.505377 23.892473 20.817204
alimentos 0.92341465 21.292683 14.682927 17.719513
casa_construcao 3.3957155 29.328888 24.377777 23.457777
telefonia 0.23650618 18.432981 6.8536153 13.248677
esporte_lazer 2.0246708 31.280085 18.10987 20.824207
moveis_quarto 9.997222 59.933334 30.8 34.4
moveis_sala 8.934846 50.73077 22.365385 44.429485
moveis_colchao_e_estofado 13.19 46.3 34.4 41.3
construcao_ferramentas_construcao 3.5500524 29.795 15.895 24.2425
fashion_underwear_e_moda_praia 0.36226416 25.509434 8.849056 18.90566
moveis_decoracao 3.008267 43.894993 17.438087 28.663155
portateis_cozinha_e_preparadores_de_alimentos 2.5175 28.5 25.3 24.6
fashion_esporte 0.34473684 22.105263 12.842105 16.263159
alimentos_bebidas 1.3079327 23.403847 15.951923 18.39423
moveis_cozinha_area_de_servico_jantar_e_jardim 11.598564 47.340427 40.47872 38.68085
pc_gamer 1.4296666 17.666666 20.0 20.0
bebidas 1.7361728 23.91358 22.37037 19.395061
market_place 1.5441058 26.759615 17.625 22.490385
fashion_roupa_masculina 0.55973685 27.326315 14.021052 22.11579
fashion_calcados 1.0445087 26.028902 13.381503 24.947977
casa_conforto_2 1.4676 53.2 15.2 26.2
eletronicos 1.2750851 25.237911 11.736944 18.468084
audio 0.6416379 20.827587 11.5 17.344828
construcao_ferramentas_jardim 2.401034 28.693182 16.65909 20.511364
artes 1.6917636 35.927273 11.8 23.363636
automotivo 2.6546504 34.05 16.24158 23.382105
cool_stuff 2.566579 33.752853 22.988594 24.491762
industria_comercio_e_negocios 5.929191 39.058823 32.514706 29.220589
pcs 7.995333 32.133335 34.333332 40.1
agro_industria_e_comercio 5.2634053 36.527027 28.945946 25.81081
casa_conforto 3.8004506 39.198196 20.045046 33.414413
pet_shop 3.0888567 32.98053 20.126564 26.749653
moveis_escritorio 12.740868 55.62783 41.86408 37.919094
fashion_roupa_infanto_juvenil 0.324 27.4 13.4 20.6
informatica_acessorios 0.89837766 25.039658 12.461257 18.14338
portateis_casa_forno_e_cafe 3.071516 31.32258 22.838709 23.870968
telefonia_fixa 0.6608276 20.646551 10.74138 16.5
utilidades_domesticas 3.0207937 31.859957 22.337902 24.821413
instrumentos_musicais 3.361038 36.276817 16.854671 29.84429
relogios_presentes 0.5092874 19.222723 10.292702 15.268623
construcao_ferramentas_ferramentas 1.0478206 23.461538 15.666667 17.23077
brinquedos 1.8693565 31.754784 20.72289 24.926294
perfumaria 0.52925694 20.32258 13.059908 16.668203
eletrodomesticos_2 9.913333 45.733334 30.666666 38.166668
cds_dvds_musicais 0.55 35.0 15.0 25.0
fashion_bolsas_e_acessorios 0.42676443 19.18139 8.998822 15.572438
construcao_ferramentas_seguranca 0.95076925 23.065933 15.0 18.989012
fraldas_higiene 1.0375 25.0 17.583334 19.25

Average Volume of Box for each Product Category

%%sql
WITH avg_box_volume AS(SELECT product_category,
                              AVG(volume)::real AS avg_volume
                       FROM(SELECT product_category,
                                   (product_length_cm::real * product_height_cm::real * product_width_cm::real)::real AS volume
                            FROM products
                            WHERE product_category IS NOT NULL) AS box_volume
                       GROUP BY product_category)
SELECT product_category,
       avg_volume,
       RANK() OVER(ORDER BY avg_volume DESC)
FROM avg_box_volume
 * postgresql://postgres:***@localhost/olist
73 rows affected.
product_category avg_volume rank
moveis_colchao_e_estofado 77244.3 1
moveis_escritorio 75468.47 2
moveis_cozinha_area_de_servico_jantar_e_jardim 69406.09 3
eletrodomesticos_2 55476.312 4
moveis_sala 54486.13 5
moveis_quarto 51038.844 6
pcs 44635.168 7
agro_industria_e_comercio 37604.23 8
industria_comercio_e_negocios 37372.31 9
malas_acessorios 32950.336 10
bebes 30835.576 11
la_cuisine 30483.4 12
casa_conforto 28148.73 13
climatizacao 27910.533 14
eletroportateis 26913.238 15
instrumentos_musicais 25089.021 16
portateis_casa_forno_e_cafe 23911.613 17
cool_stuff 23246.484 18
pet_shop 22965.066 19
sinalizacao_e_seguranca 22583.795 20
moveis_decoracao 22481.225 21
utilidades_domesticas 21904.168 22
casa_conforto_2 20938.0 23
casa_construcao 20800.236 24
brinquedos 19625.812 25
construcao_ferramentas_iluminacao 18897.256 26
portateis_cozinha_e_preparadores_de_alimentos 18872.7 27
ferramentas_jardim 18615.406 28
cama_mesa_banho 18594.576 29
artigos_de_festas 16942.73 30
automotivo 16603.586 31
papelaria 16240.033 32
construcao_ferramentas_construcao 15455.385 33
esporte_lazer 14725.349 34
artigos_de_natal 13947.046 35
eletrodomesticos 13451.751 36
construcao_ferramentas_jardim 13399.671 37
cds_dvds_musicais 13125.0 38
market_place 12973.48 39
bebidas 11217.803 40
artes 10668.491 41
beleza_saude 10468.159 42
seguros_e_servicos 10233.5 43
fashion_roupa_masculina 8798.421 44
fashion_calcados 8478.208 45
fraldas_higiene 8468.417 46
eletronicos 7962.507 47
alimentos_bebidas 7947.1636 48
fashion_roupa_infanto_juvenil 7590.2 49
construcao_ferramentas_seguranca 7472.055 50
construcao_ferramentas_ferramentas 7357.6665 51
tablets_impressao_imagem 7182.222 52
fashion_roupa_feminina 7165.2593 53
pc_gamer 7074.6665 54
cine_foto 6952.393 55
consoles_games 6898.9053 56
artes_e_artesanato 6895.9473 57
fashion_esporte 6789.8945 58
musica 6727.4814 59
alimentos 6634.122 60
informatica_acessorios 6483.912 61
flores 6334.857 62
perfumaria 5136.2188 63
telefonia_fixa 5018.681 64
audio 4970.1724 65
fashion_underwear_e_moda_praia 4584.132 66
fashion_bolsas_e_acessorios 4480.635 67
livros_interesse_geral 3900.0464 68
relogios_presentes 3470.398 69
livros_tecnicos 2758.992 70
livros_importados 1935.3871 71
telefonia 1865.8413 72
dvds_blu_ray 1746.8541 73

Correlation between freight value and product weight, length, height, volume and price

%%sql
WITH product_freight 
       AS(SELECT oi.freight_value,
       pr.product_weight_grams,
       pr.product_length_cm,
       pr.product_height_cm,
       pr.product_width_cm,
       pr.product_length_cm::real * pr.product_height_cm::real * pr.product_width_cm::real AS volume,
       oi.price
FROM products pr
JOIN order_items oi USING(product_id))
SELECT CORR(freight_value, product_length_cm)::real AS corr_length,
       CORR(freight_value, product_height_cm)::real AS corr_height,
       CORR(freight_value, product_width_cm)::real AS corr_width,
       CORR(freight_value, volume)::real AS corr_volume,
       CORR(freight_value,product_weight_grams)::real AS corr_weight,
       CORR(freight_value, price)::real AS corr_price
FROM product_freight
 * postgresql://postgres:***@localhost/olist
1 rows affected.
corr_length corr_height corr_width corr_volume corr_weight corr_price
0.30908597 0.39183104 0.32377744 0.5872701 0.6104202 0.4142043

Correlation between unit sold and photo quantity

%%sql
WITH unit_photos AS(SELECT product_id,
                           unit_sold,
                           product_photos_qty
                    FROM (SELECT *
                          FROM (SELECT product_id,
                                       SUM(order_item_id) as unit_sold    
                                FROM orders
                                JOIN order_items USING(order_id)
                                WHERE order_status='delivered'
                                GROUP BY product_id) AS unit_sold
                                ORDER BY unit_sold DESC) AS product_units
                          JOIN products USING(product_id))
SELECT CORR(unit_sold, product_photos_qty)::real
FROM unit_photos
LIMIT 10
 * postgresql://postgres:***@localhost/olist
1 rows affected.
corr
-0.006174032

Linear Relationship between freight value and product weight

%%sql
WITH price_freight AS (SELECT product_category, 
                       freight_value,
                       (product_weight_grams/1000)::real AS weight
                       FROM order_items
                       JOIN products using(product_id))
SELECT regr_slope(freight_value,weight)::real AS slope,
       regr_intercept(freight_value,weight)::real AS intercept
FROM price_freight
LIMIT 10
 * postgresql://postgres:***@localhost/olist
1 rows affected.
slope intercept
2.575215 15.649705