Tasks

Cell 1
-- 1. Import Customers
CREATE TABLE olist_customers AS 
SELECT * FROM read_csv_auto('olist_customers_dataset.csv');

-- 2. Import Sellers
CREATE TABLE olist_sellers AS 
SELECT * FROM read_csv_auto('olist_sellers_dataset.csv');

-- 3. Import Geolocation
CREATE TABLE olist_geolocation AS 
SELECT * FROM read_csv_auto('olist_geolocation_dataset.csv');

-- 4. Import Products
CREATE TABLE olist_products AS 
SELECT * FROM read_csv_auto('olist_products_dataset.csv');

-- 5. Import Product Category Translation
CREATE TABLE olist_product_category_translation AS 
SELECT * FROM read_csv_auto('product_category_name_translation.csv');

-- 6. Import Orders
CREATE TABLE olist_orders AS 
SELECT * FROM read_csv_auto('olist_orders_dataset.csv');

-- 7. Import Order Items
CREATE TABLE olist_order_items AS 
SELECT * FROM read_csv_auto('olist_order_items_dataset.csv');

-- 8. Import Order Payments
CREATE TABLE olist_order_payments AS 
SELECT * FROM read_csv_auto('olist_order_payments_dataset.csv');

-- 9. Import Order Reviews
CREATE TABLE olist_order_reviews AS 
SELECT * FROM read_csv_auto('olist_order_reviews_dataset.csv');
    
Status: ok · Statements: 9

Count
99224
Cell 2
-- Get the number of customers for each state
select customer_state, count(*)
from olist_customers
group by customer_state
order by count(*) desc
Status: ok · Statements: 1

customer_state count_star()
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
Cell 3
-- List all the products the customer 'f6a9bfc46722d91dc2306b08a0b8e11a' bought
-- Output category & price

select p.product_category_name, i.price
from olist_products p, olist_orders o, olist_order_items i
where o.customer_id = 'f6a9bfc46722d91dc2306b08a0b8e11a'
and o.order_id = i.order_id and i.product_id = p.product_id
Status: ok · Statements: 1

product_category_name price
utilidades_domesticas 28.99
relogios_presentes 89.00
moveis_decoracao 79.90
Cell 4
-- get the average review score for the reviews each customer wrote
select o.customer_id, avg(r.review_score) as avg_score
from olist_orders o, olist_order_reviews r
where o.order_id = r.order_id
group by o.customer_id
order by avg_score desc
Status: warn · Statements: 1

Showing first 200 rows (truncated).
customer_id avg_score
79990624398c7f07be8365fe30da10a1 5.0
fde680ede5f8b0a4ebaa0fcd7da97f07 5.0
eb15b32fe7190330e096562eac5308f5 5.0
f145cc75412500f2dbe71a57c2d4dc9f 5.0
bf4fe999a2d7bc0465cbd817fac3bbee 5.0
3740f4d0edef80f567eca55830ec9e8f 5.0
346b61007a25104a5a1b25e1e68b16b0 5.0
a5d6b2f5be2eca026d50b24773ef1113 5.0
5ad5c913900581e3ebd6c20ba68c5760 5.0
660673ec54a8b7fe75dcaea60f3f3e0d 5.0
299f7b5125c8fbe1761a1b320c34fc7d 5.0
8ad4ecbaa69e3b0119cadae300bd88f9 5.0
535ad20edf8f32252bb8c7786f18df1e 5.0
6ad62864f75d24638cab4e7bcdd9972a 5.0
e6ed40b69433d15314244f3ef50bcd29 5.0
1b9506de3da8fd318853195b13fb2dda 5.0
df9432f68e31773dc4b20fb916f2f647 5.0
10953b2bea8e8eb31821c2d41e85c4dc 5.0
fa8d8a26ce081c2669b4c79639a98e08 5.0
5364f18dbadca7f50016ad6da7a39436 5.0
3833a2cc43f6ee57cc7cccf7d7d53d18 5.0
09d9f64ba169a1b0e2c8d9690ef483c2 5.0
2364ed5a865ac9d255eac0cac6366ea0 5.0
c4073e465adf088386a637bf76ff1bdf 5.0
f0ae81a403ac013c63bbea4035de8083 5.0
ee2ae4200c019e2d79caa3433262d63b 5.0
2da3c88ac9284b3bf9bea4cb79fd0e47 5.0
ce23f51b42b3258700b4aeedcf53a96d 5.0
30c0d1803692aa5d5f70dfb0740175c1 5.0
d09200136a8aec3f7a9831754d554b5c 5.0
8f1a32257a013ab264c572e0b3ed01c4 5.0
ecbe0bf872587f3cb5633f2b5d9b9fcf 5.0
318c79ecbef169cf1961191ee2e2b547 5.0
7126ec8fd6c81f9f0b3a50f337bcde4e 5.0
3d467c5762bb6d35042b28517d723219 5.0
969d8a95fd033aa0d536264554d3a31b 5.0
f1c76162ea81ad7889daffd690950c5b 5.0
73d765b0b1b2c7649280b69676c337a8 5.0
ba668ea30c47e2012890846c77978d03 5.0
109752cc7796656c8d504f634e66aead 5.0
94c2f45091985c47b4c4fb301113f0a4 5.0
38d1cd89306128348ffdf4cc23f3a50a 5.0
2b2d00cc7790247c69d95d823c8d9c6e 5.0
78b3ea1b4db59453eb9231249c2f0d67 5.0
abcbd85506e144160bee308811967a35 5.0
6eecb45e1c147bc42841894ff03b9e0f 5.0
b660db37d14a783ec1dd53491cdcfca3 5.0
5ae9750560eff787bef03f127c5db246 5.0
4b18fb110efbfff3ebecb22c9127f816 5.0
ab106a7ee6bdc144f6c299da92e94a42 5.0
f7ac2fea33182bef4f8d0abd7257baac 5.0
d4fd73b5eb04da7927786173ddbbea0a 5.0
fba07e232b15cccc3eacff2e597905e9 5.0
412ef0bba96a560e2811cda36c9cc55e 5.0
38e584a08f477bf894151834aad7cdc6 5.0
4d73737f27246fd7094751652d0ee963 5.0
e314b1b0829d37e817b515fd2a5e194b 5.0
e72be6eccbec3edc6e5dc148a9794efc 5.0
5d3f357bcae87e7099d31a769c2e4cc9 5.0
a0ae88a1f9e295befb3436f377b0746f 5.0
6a06ba0de1437770f6b5709314369ce5 5.0
1d44df4d54c74b1b8944e7c8816c1ec5 5.0
3eba378718d805d744a2f507aa9dd2e2 5.0
ceb884f66ec83a3d90578d492c89a137 5.0
bb57625bfea720d058af92852a0bb303 5.0
4898298d72e64f88fc72c75881bb26d6 5.0
7812fcebfc5e8065d31e1bb5f0017dae 5.0
051eca5824715d378df187a46e1005e5 5.0
caa66860ae0cc53f31d70c304a4d630a 5.0
7277b6486e43ef6b8756caa0be68fc6b 5.0
2a9383dc7b74510d78fe5208adbc3fd9 5.0
4b98719a17e513d97fe6d782d6e93f25 5.0
e91d6e2c6d965a4d07ff782ce5bde6db 5.0
b71f77353b8fbd3465a46d282ad28c29 5.0
c5ad44e41c6bb1195a7cbe476af25449 5.0
b41fd3073c40d1445c9bd861f4d2a57c 5.0
5ec25f4ab37a355fab56c7f7b2fbbf55 5.0
4fb7cbd1728fe3e7909ca36668074cdd 5.0
44ffbbe638352715960ef277769549e2 5.0
69a7c2c293e83fd36e2fd81a7bcb74b3 5.0
cd1822306d5e43d356c576efdc0eadf1 5.0
d828aa60b62d1ef598c2f7c75e7dfe0b 5.0
ba6c4b82b2df4cff54c92b0c474e2473 5.0
2458476c6c4f01b4ce4fdf4c006c599b 5.0
ba30b0a551ddef6057b752431dc4abeb 5.0
372c809b0a64a795234e537135754dc6 5.0
87e3b43896edfc6786a0d8ab36a14202 5.0
08420efd8b1c0be27393032e21a993ee 5.0
5f0da02ddc5814e24c420575d2280df1 5.0
ada4af8902da278c18ba8834a1c1faf4 5.0
ca0448b0e38ea07c04f29cc6e1705a3e 5.0
5a5ba707171db1cfa955d1cafe4a347c 5.0
d38ab5feaeb102e5990e4a27c69c2da2 5.0
a4b296c2ec5ff873f8c83fa0d92c24c4 5.0
7659b77c8e8536224e0f03176075ebb5 5.0
93fceba85d3875c64e2f3c742ee72e06 5.0
11fa2de1bb5510a94dc0f632cda6dcd8 5.0
e9b6f1e10bd93558fd9891138fd98158 5.0
d77394ad560e9f49d8951a6a3546fed9 5.0
83ced71889769885f9a06748fc0364fd 5.0
98b71ff28f7ea89054def4aba7f8225b 5.0
aff2ea260ea09d68dc1ff576fa3738fa 5.0
986f579e98bbb0ae992a7263a1196d46 5.0
2ca5a7b2dd62178ff3dda2b928a08f59 5.0
dc568c24cf5f6ba6691a0514893967af 5.0
3c473ed8031b6f4e249ae5f13399bacf 5.0
8db0d5efc870373f918d092930f8e762 5.0
c4e7077f422f07fc99fcf2e215004ad3 5.0
9b609409c2f5529b2611bf351bea54ac 5.0
751cfe7493f5c120bdee116e34ec8fc5 5.0
871bbb7876617cfe857ea6d2ca7c8f4f 5.0
db913a930f7150edd2c9d735b27df1b1 5.0
5a5ea6a8215f199cf696944f1540ddf1 5.0
83daf1e45e755edc47296c7974660c47 5.0
ff3c583d80f698805bcda1e3059cb352 5.0
37d273154d2b2213fb174d4ac3ba3afb 5.0
08e4aec0cb39467c0d89548c9e44e28b 5.0
51be975a0c75d5215c87ab06d71faf21 5.0
e70c671c60cdf3f27f162c296e5d7e0c 5.0
ff6b440b2465bfaef05c675efb48cd15 5.0
1adcc89514838ce6dab7b1d17e3a721d 5.0
96394ffdc6e79741b4b319e7651c4434 5.0
8891c02b39e08c241fd6c4372c689d60 5.0
b453edb9fb0a8f0fa061a9f7b28f0ea4 5.0
0bd683b7ceca26b5bba4e327682275c5 5.0
760119a92dd09c116c57f97d1d1bf346 5.0
15a5a319296eccdc3073c103ab791b84 5.0
deb908d63b07cd65667ac2e526243abf 5.0
8820a10be480ab58c6cccaaeb887c15b 5.0
cd72c28d6c68a8535de92733f591b331 5.0
26233a1c81079a34ea562cc3af21c754 5.0
1f22b24610582cb3c91d898e137f9ee3 5.0
d2aa6bef2582c7482ab992fa89f965bd 5.0
a964e30ab4f1b606fba4cd1f15806048 5.0
05a76b868de36a1723b93d8879251186 5.0
d00cfef8ff754413ba08a56924d883e8 5.0
04065ecc8bc4cfd1228121cd5426e8db 5.0
bb8d51f8ac80fa7cfa96dbe23914a31a 5.0
ae5b535b75ca9bfa696cbd865be253ec 5.0
ae71b6eabceb10fc65bd4c7edb0b24a1 5.0
d95597cf8fd578a1e0e09891c81afbd6 5.0
e30bdf332b6c38f5f22d50dec12282cb 5.0
7c7378d8b1eabfe7416a90059bda99f2 5.0
96f7a9ae1b47e794058d0591ce2477bd 5.0
93ce9a6cacc23c3350af92246db9ec67 5.0
d05b620139275e5bc703e935d448d598 5.0
e3c41a81d6fb46980a44244fb90e8e84 5.0
5976aae2978512e829135e2df9024756 5.0
be00488d26c4fe225097e1756a6a663e 5.0
a7086654d09a3fadfc0ed962acba11d6 5.0
b209a1643829776c9340b2fab705c225 5.0
a28730bf79d0b8a7bba0da57b026bf78 5.0
a5dbb47a11f753332945a2acdf6e84c8 5.0
28290f0535157d1ffb43629e3a790c4c 5.0
b0d33167a84b75a35b3921e04f2a3a91 5.0
5c2ceac1bbf7f59ea08a70dd69186752 5.0
b686164fb97b8faf0c6acb1556838ed1 5.0
e3a8ed67bfc463e93bc727920b63a3f3 5.0
e797451a7e6ac3ef0dc569b73e144aa0 5.0
86242b64468c9c6370a38afab37a986a 5.0
f8a1cb0051331f57beaf3523f2c17b63 5.0
20211f3cfcf1818d8e0bd151f7007309 5.0
09925cbe133c99a8fc4512393f569b3b 5.0
3b074f1344638ce0b5d88cd3ff364376 5.0
40502efcc32e098593b0f0d13a009b7b 5.0
b7b87d3282571fd06f494f2e5d7218bf 5.0
18c36d92cc77f076f56a7f5c5c084603 5.0
33414f5059c66942804592502e4b1881 5.0
460120cb864985d447516e2a69e244c0 5.0
a347ddd4888c231851a58e905d8bb8ee 5.0
003eab528664e1bd677218ef4be086ac 5.0
af8818dabc1d82f7a6d1e792f56cd4c8 5.0
905e35fa8c58f52fd1443aa882e99587 5.0
d212b0357f43fcec95b63166e2b49bca 5.0
a11a96019906d49f5d01edda79022941 5.0
465fd465dfc7d83630b4c1ca4b51deeb 5.0
5c0834ac354424abbbaf87883d8d61ac 5.0
7f04bae0255c12792d4f1e345db66077 5.0
ffc18c67e76fe18432856ef05b0b5edd 5.0
0fb55518436111358f550b10bfe4e180 5.0
24f8c06607e71be236bfd3f6752a7435 5.0
171345b7d45c10d2ff1a8cfc52c163db 5.0
12c50c22a6f04953fceda99928b56847 5.0
2173c184771a8a19f6fc87d8b3dea9e0 5.0
c2c6de553b9ae7dc0ca347bbabfa376b 5.0
2dc0c12730d20a4eb7d97b60c47c5fa4 5.0
beeca8af57f7cb7b42b1877824a941c2 5.0
6b07820975c0b42e74d62d809d42881e 5.0
9e630b2d4070b5cbcce64dc50dd12f1f 5.0
82f2353693ff81e3fae421f5fad9784e 5.0
fb203e8695c629379d79a400387c8a42 5.0
1a38585b7f867f8bb2823aef378b9445 5.0
c88e6265903acbcc184a0d99abe6b987 5.0
95149169752f33705244ee124d213204 5.0
4f1bbce28fa7e3c6ba327eb99bea5b62 5.0
7784b523efa600a47de73bd1e9f7f022 5.0
f0fbeb9756b77e7f9fab29a9eab784a6 5.0
4d3efa9f79cbe7f9df1a06786b3415ce 5.0
e92c0de5b9a170f19572d6ec0cd67785 5.0
325b358f012b5d274e0a3ef34a351f55 5.0
Cell 5
-- Some customers do not like to pay there orders, find them
-- (i.e., find orders where  a payment record does not exist)
select *
from olist_orders o
where not exists(
select *
from olist_order_payments p
where p.order_id = o.order_id
)
Status: ok · Statements: 1

order_id customer_id order_status order_purchase_timestamp order_approved_at order_delivered_carrier_date order_delivered_customer_date order_estimated_delivery_date
bfbd0f9bdef84302105ad712db648a6c 86dc2ffce2dfff336de2f386a786e574 delivered 2016-09-15 12:16:38 2016-09-15 12:16:38 2016-11-07 17:11:53 2016-11-09 07:47:38 2016-10-04
Cell 6
select *
from olist_orders o
where o.order_id not in (select p.order_id from olist_order_payments p)
Status: ok · Statements: 1

order_id customer_id order_status order_purchase_timestamp order_approved_at order_delivered_carrier_date order_delivered_customer_date order_estimated_delivery_date
bfbd0f9bdef84302105ad712db648a6c 86dc2ffce2dfff336de2f386a786e574 delivered 2016-09-15 12:16:38 2016-09-15 12:16:38 2016-11-07 17:11:53 2016-11-09 07:47:38 2016-10-04
Cell 7
-- For each product category: get the average review score 
-- (associated with orders containing this product)
-- Output: category_name, avg score (sorted by average score)

select p.product_category_name, avg(review_score) as avg_score
from olist_order_items i, olist_products p, olist_order_reviews r
where i.order_id = r.order_id and i.product_id = p.product_id
group by p.product_category_name 
order by avg_score desc
Status: ok · Statements: 1

product_category_name avg_score
cds_dvds_musicais 4.642857
fashion_roupa_infanto_juvenil 4.500000
livros_interesse_geral 4.446266
construcao_ferramentas_ferramentas 4.444444
flores 4.419355
livros_importados 4.400000
livros_tecnicos 4.368421
alimentos_bebidas 4.315412
malas_acessorios 4.315257
portateis_casa_forno_e_cafe 4.302632
fashion_esporte 4.258065
fashion_calcados 4.233716
alimentos 4.218182
musica 4.210526
cine_foto 4.205479
papelaria 4.193857
pet_shop 4.185147
pcs 4.175000
eletrodomesticos 4.172457
perfumaria 4.161941
brinquedos 4.158641
instrumentos_musicais 4.152593
eletroportateis 4.149188
cool_stuff 4.146341
fashion_bolsas_e_acessorios 4.144679
eletrodomesticos_2 4.142857
beleza_saude 4.142768
artes_e_artesanato 4.125000
tablets_impressao_imagem 4.123457
moveis_quarto 4.118182
esporte_lazer 4.107986
industria_comercio_e_negocios 4.101504
sinalizacao_e_seguranca 4.086294
dvds_blu_ray 4.079365
automotivo 4.065512
utilidades_domesticas 4.055019
construcao_ferramentas_jardim 4.054167
construcao_ferramentas_iluminacao 4.054054
construcao_ferramentas_construcao 4.052916
bebidas 4.047745
ferramentas_jardim 4.042735
eletronicos 4.037468
consoles_games 4.023070
artigos_de_natal 4.020548
market_place 4.019417
relogios_presentes 4.019160
bebes 4.011811
la_cuisine 4.000000
agro_industria_e_comercio 4.000000
fashion_underwear_e_moda_praia 3.976923
climatizacao 3.969178
moveis_cozinha_area_de_servico_jantar_e_jardim 3.964286
telefonia 3.946867
casa_construcao 3.940000
artes 3.937198
informatica_acessorios 3.930819
moveis_sala 3.904382
moveis_decoracao 3.903493
cama_mesa_banho 3.895663
construcao_ferramentas_seguranca 3.844560
None 3.841051
casa_conforto 3.829885
audio 3.825485
moveis_colchao_e_estofado 3.815789
fashion_roupa_feminina 3.780000
artigos_de_festas 3.767442
telefonia_fixa 3.683206
fashion_roupa_masculina 3.641221
casa_conforto_2 3.629630
moveis_escritorio 3.493183
pc_gamer 3.333333
portateis_cozinha_e_preparadores_de_alimentos 3.266667
fraldas_higiene 3.256410
seguros_e_servicos 2.500000
Cell 8
-- Get all rich customers (already spent more than 5000$)
-- customer_id, total_spent

select o.customer_id, sum(i.price) as spent
from olist_orders o, olist_order_items i
where o.order_id = i.order_id
group by o.customer_id
having sum(i.price)>5000
Status: ok · Statements: 1

customer_id spent
1617b1357756262bfa56ab541c47bc16 13440.0
ec5b2ba62e574342386871631fafd3fc 7160.0
c6e2731c5b391845f6800c97401a43a9 6735.0
05455dfa7cd02f13d132aa7a6a9729c6 5934.6
f48d464a0baaea338cb25f816991ab1f 6729.0
3fd6777bbce08a352fddd04e4a7cc8f6 6499.0