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.
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