Miscellaneous

Miscellaneous

%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'

Numbers of orders where payment value is less than 10

%%sql
SELECT COUNT(order_id) AS orders_payment_value_less_than_10
FROM orders
WHERE EXISTS(SELECT order_id
             FROM order_payments  
             WHERE orders.order_id= order_payments.order_id 
             AND payment_value < 10)

 * postgresql://postgres:***@localhost/olist
1 rows affected.

orders_payment_value_less_than_10
958

Payment method with there frequency

%%sql
SELECT ROW_NUMBER() OVER(ORDER BY frequency DESC),
       payment_type,
       frequency
FROM(SELECT payment_type,
            COUNT(order_id) AS frequency
FROM order_payments
GROUP BY payment_type) AS freq
LIMIT 10

 * postgresql://postgres:***@localhost/olist
5 rows affected.

row_number	payment_type	frequency
1	credit_card	76795
2	boleto	19784
3	voucher	5775
4	debit_card	1529
5	not_defined	3

Total number of ids of customers and sellers

%%sql
SELECT COUNT(*) AS total_ids
FROM (SELECT customer_unique_id AS idn FROM customers 
      UNION
      SELECT seller_id FROM sellers) AS ids 
LIMIT 10

 * postgresql://postgres:***@localhost/olist
1 rows affected.

total_ids
99191

Analysing freight value

%%sql
WITH freight AS(SELECT order_id,
                       price,
                       freight_value,
                       CASE WHEN freight_value = 0.0 THEN 'FREE'
                            WHEN freight_value BETWEEN 0.1 AND 10.0 THEN 'VERY LOW'
                            WHEN freight_value BETWEEN 10.0 AND 100.0 THEN 'LOW'
                            WHEN freight_value BETWEEN 100.1 AND 200.0 THEN 'MEDIUM'
                            ELSE 'HIGH'
                       END AS freight_cost
                FROM order_items)
SELECT freight_cost,
       COUNT(freight_cost) AS freq
FROM freight
GROUP BY freight_cost
ORDER BY freq DESC
LIMIT 10

 * postgresql://postgres:***@localhost/olist
5 rows affected.

freight_cost	freq
LOW	96081
VERY LOW	15456
MEDIUM	601
FREE	383
HIGH	129

Product Translation

%%sql
WITH prod_cat AS(SELECT DISTINCT(product_category)
                 FROM products)
SELECT product_category, category_translation
FROM prod_cat AS pc
LEFT JOIN product_translation AS pt
ON pc.product_category = pt.category

 * postgresql://postgres:***@localhost/olist
74 rows affected.

product_category	category_translation
beleza_saude	health_beauty
informatica_acessorios	computers_accessories
automotivo	auto
cama_mesa_banho	bed_bath_table
moveis_decoracao	furniture_decor
esporte_lazer	sports_leisure
perfumaria	perfumery
utilidades_domesticas	housewares
telefonia	telephony
relogios_presentes	watches_gifts
alimentos_bebidas	food_drink
bebes	baby
papelaria	stationery
tablets_impressao_imagem	tablets_printing_image
brinquedos	toys
telefonia_fixa	fixed_telephony
ferramentas_jardim	garden_tools
fashion_bolsas_e_acessorios	fashion_bags_accessories
eletroportateis	small_appliances
consoles_games	consoles_games
audio	audio
fashion_calcados	fashion_shoes
cool_stuff	cool_stuff
malas_acessorios	luggage_accessories
climatizacao	air_conditioning
construcao_ferramentas_construcao	construction_tools_construction
moveis_cozinha_area_de_servico_jantar_e_jardim	kitchen_dining_laundry_garden_furniture
construcao_ferramentas_jardim	costruction_tools_garden
fashion_roupa_masculina	fashion_male_clothing
pet_shop	pet_shop
moveis_escritorio	office_furniture
market_place	market_place
eletronicos	electronics
eletrodomesticos	home_appliances
artigos_de_festas	party_supplies
casa_conforto	home_confort
construcao_ferramentas_ferramentas	costruction_tools_tools
agro_industria_e_comercio	agro_industry_and_commerce
moveis_colchao_e_estofado	furniture_mattress_and_upholstery
livros_tecnicos	books_technical
casa_construcao	home_construction
instrumentos_musicais	musical_instruments
moveis_sala	furniture_living_room
construcao_ferramentas_iluminacao	construction_tools_lights
industria_comercio_e_negocios	industry_commerce_and_business
alimentos	food
artes	art
moveis_quarto	furniture_bedroom
livros_interesse_geral	books_general_interest
construcao_ferramentas_seguranca	construction_tools_safety
fashion_underwear_e_moda_praia	fashion_underwear_beach
fashion_esporte	fashion_sport
sinalizacao_e_seguranca	signaling_and_security
pcs	computers
artigos_de_natal	christmas_supplies
fashion_roupa_feminina	fashio_female_clothing
eletrodomesticos_2	home_appliances_2
livros_importados	books_imported
bebidas	drinks
cine_foto	cine_photo
la_cuisine	la_cuisine
musica	music
casa_conforto_2	home_comfort_2
portateis_casa_forno_e_cafe	small_appliances_home_oven_and_coffee
cds_dvds_musicais	cds_dvds_musicals
dvds_blu_ray	dvds_blu_ray
flores	flowers
artes_e_artesanato	arts_and_craftmanship
fraldas_higiene	diapers_and_hygiene
fashion_roupa_infanto_juvenil	fashion_childrens_clothes
seguros_e_servicos	security_and_services
pc_gamer	pc_gamer
None	None
portateis_cozinha_e_preparadores_de_alimentos	None