https://insideairbnb.com/get-the-data/

Temas abordados neste ciclo:

• 1️⃣ Introdução ao SQL e primeiros SELECTs

• 2️⃣ Filtros e ordenação

• 3️⃣ Funções e manipulação de colunas

• 4️⃣ Agrupamento e agregação

• 5️⃣ JOINs (união de tabelas)

• 6️⃣ Subconsultas e CTEs

• 7️⃣ Limpeza de dados no SQL

• 8️⃣ SQL no Python (Pandas)

• 9️⃣ Projeto colaborativo – parte 1

• 🔟 Projeto colaborativo – parte 2

• ❇️ Consolidação do aprendizado e compartilhamento de resultados

Passo 1: Preparação do Ambiente No seu VSCode, certifique-se de que você tem as extensões de Python instaladas. Você precisará de duas bibliotecas. No terminal do VSCode, instale:

pip install pandas sqlalchemy

(O sqlite3 já vem nativo no Python, mas o sqlalchemy ajuda o Pandas a conversar melhor com o banco).

Passo 2: O Script de Importação (ETL) Como os arquivos do Inside Airbnb (listings.csv.gz e reviews.csv.gz) são grandes e comprimidos, o Pandas é a melhor ferramenta para ler e jogar para o banco. Ele descompacta automaticamente o .gz.

Execute o arquivo 'import_data.py' ⚠️ Atenção: Coloque os arquivos baixados na mesma pasta desse script.

Passo 3: Visualizar os dados no VSCode Para conferir se deu certo sem sair do VSCode, recomendo instalar a extensão SQLite Viewer (além do SQLTools).

Instale a extensão SQLite Viewer (autoria de Florian Klampfer).

Clique no arquivo projeto_airbnb.db que apareceu na sua barra lateral de arquivos.

Ele vai abrir uma visualização estilo Excel das tabelas.

-- Mostrando o problema do preço (Vem como texto com $)
SELECT id, name, price 
FROM listings 
LIMIT 5;

-- Mostrando que não dá para somar direto (vai dar erro ou zero)
SELECT SUM(price) 
FROM listings;

Solução (a query de limpeza):

-- A solução (Limpeza de Dados)
SELECT 
    id, 
    name, 
    CAST(REPLACE(REPLACE(price, '$', ''), ',', '') AS REAL) as preco_limpo
FROM listings
LIMIT 5;

🔗 Link direto para a seção de dados:http://insideairbnb.com/get-the-data

📍 Passo a Passo para baixar os arquivos corretos:Acesse o link acima.Role a página até encontrar a seção South America > Brazil > Rio de Janeiro.Você verá uma lista de arquivos. Cuidado aqui: existem arquivos "resumidos" (Summary) e arquivos "detalhados". Para o projeto de SQL, precisaremos dos detalhados (que vêm comprimidos em .gz).

Baixe apenas estes 3 arquivos da lista (geralmente os primeiros):

listings.csv.gz : Contém todas as colunas "sujas" (preço com $, amenities, textos longos) necessárias para o projeto. calendar.csv.gz : Contém o preço dia-a-dia e disponibilidade futura (essencial para o JOIN). reviews.csv.gz : Contém os comentários dos usuários (para a parte de Python/Texto).

O arquivo calendar.csv.gz é gigantesco. Quando descompactado na memória RAM, ele fica maior do que o seu computador aguenta carregar de uma vez só (provavelmente vários Gigabytes).

Para resolver isso, precisamos usar uma técnica clássica de Engenharia de Dados chamada Chunking (processamento em lotes).

Em vez de ler o arquivo inteiro, tivemos que ler 50.000 linhas, salvar no banco, limpar a memória, ler as próximas 50.000, e assim por diante.

-> Extensão SQLite Viewer para checar a corretude do ETL.

Visão Geral dos Dados

SELECT COUNT(*) FROM listings;
SELECT COUNT(*) FROM reviews;
SELECT COUNT(*) FROM calendar;

-- 2. Olhando a "cara" dos dados (Tema 1)
SELECT id, name, price, neighbourhood_cleansed 
FROM listings 
LIMIT 5;

Começando

Antes de analisar, precisamos limpar os dados. O preço está como texto e sujo.

Em vez de repetir a fórmula de limpeza toda hora, vamos criar uma VIEW. É a solução mais elegante e também mostra que sabemos moduarizar o código.

CRIANDO UMA VIEW LIMPA (Facilita todas as consultas futuras)

Temas: #3 e #7

DROP VIEW IF EXISTS dados_limpos;

CREATE VIEW dados_limpos AS
SELECT 
   id, 
   name, 
   neighbourhood_cleansed AS bairro, 
   review_scores_rating AS nota,
   CAST(REPLACE(REPLACE(price, '$', ''), ',', '') AS REAL) AS preco
FROM listings
WHERE price IS NOT NULL;

-- Teste rápido para mostrar que funcionou
SELECT * FROM dados_limpos LIMIT 5;

A função REPLACE é usada aqui para limpeza do preço, removendo o $ e vírgula, e depois tudo é convertido para número REAL usando CAST.

O Mapa do Mercado

Temas #2 e #4

• Agora que temos dados limpos, vamos entender o mercado. Quais bairros são mais caros? E quais têm melhor avaliação?

SELECT 
    bairro, 
    ROUND(AVG(preco), 2) AS preco_medio,
    ROUND(AVG(nota), 2) AS nota_media,
    COUNT(*) AS total_imoveis
FROM dados_limpos
GROUP BY bairro
HAVING total_imoveis > 50 -- Filtro importante para evitar bairros com 1 só imóvel distorcendo a média
ORDER BY preco_medio DESC
LIMIT 10;

JOIN

Tema: #5

• O investidor quer saber: o que os clientes reclamam ou elogiam nos imóveis de luxo? Para isso, precisamos cruzar os anúncios com os comentários.

Esta query conecta a tabela que acabamos de limpar com a tabela gigante de reviews.

-- 3. JOIN (Cruzando tabelas)
-- Objetivo: Ler reviews recentes de imóveis caros no Leblon
SELECT 
    l.name AS imovel,
    l.preco,
    r.date AS data_comentario,
    -- Corta o comentário para não ficar gigante na tela
    SUBSTR(r.comments, 1, 100) || '...' AS trecho_comentario
FROM dados_limpos l
INNER JOIN reviews r 
    ON l.id = r.listing_id
WHERE l.bairro = 'Leblon' 
  AND l.preco > 1000
ORDER BY r.date DESC
LIMIT 10;

A "Pergunta de Ouro"

(Tema 6 - Subquery)

• Para finalizar nossa consultoria, encontramos as 'Joias Escondidas'. Imóveis que custam menos que a média do bairro, mas têm nota excelente.

Essa query usa uma Subconsulta (para calcular a média do bairro) dentro do filtro.

-- 4. SUBCONSULTA (Análise Avançada)
-- Encontrando oportunidades: Preço abaixo da média do Leblon, mas nota acima de 4.9

SELECT 
    name, 
    preco, 
    nota
FROM dados_limpos
WHERE bairro = 'Leblon'
  AND nota >= 4.9
  AND preco < (
      -- Aqui está a subconsulta: calcula a média só do Leblon
      SELECT AVG(preco) 
      FROM dados_limpos 
      WHERE bairro = 'Leblon'
  )
ORDER BY preco ASC
LIMIT 5;

Essa query nos traz a lista dos locais com preço abaixo da média no Leblon, mas que tenham notas altas.

Porém isso não nos mostra o quão abaixo da média aquele valor está. Entendemos que seria interessante trazer mais uma coluna, que mostrasse quão mais barato de fato o lugar está.

Resolvendo Problemas

SELECT 
    name, 
    preco, 
    nota, (preco - avg(preco)) / preco * 100 AS diferenca_percentual_preco
FROM dados_limpos
WHERE bairro = 'Leblon'
  AND nota >= 4.9
  AND preco < (  
      SELECT AVG(preco) 
      FROM dados_limpos 
      WHERE bairro = 'Leblon'
  )   -- Aqui está a subconsulta: calcula a média só do Leblon
ORDER BY preco ASC
LIMIT 5;

A ideia dessa query mais 'intuitiva' era trazer uma nova coluna, mostrando o quão distante aquele preço está da média.

Acontece que o resultado diverge totalmente do esperado:

Essa é uma questão mais sofisticada e toca em um ponto onde o SQLite difere de outros bancos de dados (como PostgreSQL ou SQL Server). O que aconteceu foi um fenômeno chamado Agrupamento Implícito.

Vamos analisar a "anatomia" do problema:

1. O comportamento da Query 1 (O Normal)

Nesta query, todas as colunas que pedimos (name, preco, nota) são atributos diretos de cada linha. O SQL olha linha por linha. Filtra quem é do Leblon. Devolve as 5 linhas.

2. O comportamento da Query 2 (O "Erro") Adicionamos desavisadamente AVG(preco) na lista de seleção principal:

SELECT name, ..., (preco - AVG(preco)) ...

Aqui está o conflito: name e preco são dados individuais (variam linha a linha). AVG(preco) é uma Função de Agregação. Ela quer pegar todas as linhas filtradas e transformá-las em um único número (a média). O que a maioria dos bancos faz (Postgres/Oracle): Dão erro! Eles dizem: "Você não pode misturar colunas normais com uma média sem usar um GROUP BY."

O que o SQLite faz (O "Jeitinho"): Ele não dá erro, mas ele "colapsa" o resultado.

Ele calcula a média de todos os imóveis do filtro. Como ele precisa entregar apenas uma linha de resultado para a média, ele pega uma linha aleatória (geralmente a última processada) para preencher os campos name e nota. Resultado: Recebemos apenas 1 linha (ou um resultado bizarro), em vez das 5 que esperávamos.

A Solução: Como corrigir?

Para fazer essa conta (comparar o preço da linha atual com a média geral) sem "colapsar" as linhas, precisaríamos calcular a média de forma isolada.

Como já estudamos Subconsultas (Tema 6), esta é a maneira clássica de resolver:

Devemos colocar o cálculo da média dentro de uma subconsulta no próprio SELECT. Assim, o banco calcula a média "à parte" e usa esse número repetidamente em cada linha, sem tentar agrupar tudo.

Teríamos assim a versão corrigida:

SELECT 
    name, 
    preco, 
    nota,
    -- A MÁGICA: Uma subconsulta que retorna apenas um número (a média do bairro)
    ROUND(
        (preco - (SELECT AVG(preco) FROM dados_limpos WHERE bairro = 'Leblon')) 
        / 
        (SELECT AVG(preco) FROM dados_limpos WHERE bairro = 'Leblon') * 100
    , 2) AS diferenca_percentual
FROM dados_limpos
WHERE bairro = 'Leblon'
  AND nota >= 4.9
  AND preco < (
      SELECT AVG(preco) FROM dados_limpos WHERE bairro = 'Leblon'
  )
ORDER BY preco ASC
LIMIT 5;

O Conceito de "Window Function" (Bônus Data Science)

Existe uma forma mais moderna e limpa de fazer isso, muito usada em Data Science, chamada Window Function (Função de Janela). Ela permite calcular uma agregação (média) sem agrupar as linhas.

A solução tida como mais elegante seria a seguinte:

SELECT 
    name, 
    preco, 
    nota,
    -- "AVG(preco) OVER()" significa: Calcule a média de todo mundo, mas mantenha as linhas separadas
    ROUND((preco - AVG(preco) OVER()) / AVG(preco) OVER() * 100, 2) AS diferenca_percentual
FROM dados_limpos
WHERE bairro = 'Leblon'
  AND nota >= 4.9
  AND preco < (SELECT AVG(preco) FROM dados_limpos WHERE bairro = 'Leblon') -- Mantemos o filtro aqui
ORDER BY preco ASC
LIMIT 5;

Novos problemas

Embora a abordagem pareça correta, tem um pequeno problema:

A diferença percentual calculada mudou!

Essa é uma das dúvidas mais conceituais e importantes do SQL Avançado! Acabamos de tropeçar na Ordem de Execução Lógica do SQL.

As duas queries dão resultados diferentes porque elas estão calculando a "Média" baseada em universos de dados diferentes.

Quem veio primeiro?

Para entender, precisamos ver a ordem em que o banco de dados processa o pedido:

Na Query 2 (Subconsulta):

O SQL calcula a subconsulta: "Qual a média de TODO o Leblon?" (Vimos que era algo do tipo 1011.20). Ele guarda esse número (1011.20). Ele vai linha por linha e aplica a fórmula: (Preço - 1011.20) / 1011.20 * 100. Resultado: Ele compara seu imóvel barato com a média geral do bairro inteiro.

Na Query 3 (Window Function):

O SQL aplica o WHERE primeiro! Ele filtra e joga fora tudo que é caro ou tem nota baixa. Sobraram apenas, digamos, 15 imóveis (os baratos e bons). Aí sim ele roda o AVG(preco) OVER(). O Problema: Ele está calculando a média apenas desses 15 imóveis que sobraram, e não do bairro todo. Resultado: Ele compara o preço do imóvel com a média dos "sobreviventes do filtro", gerando um número distorcido.

A Solução

Para usar Window Functions (Query 3) e obter o mesmo resultado da Query 2 (que é a correta para o nosso objetivo de negócios), precisamos calcular a média antes de filtrar.

No SQL, fazemos isso criando uma Subconsulta (ou CTE) que prepara os dados primeiro, e filtramos depois.

-- SOLUÇÃO CORRETA COM WINDOW FUNCTION
  -- Estratégia: Calcular a média ANTES de filtrar (usando subquery no FROM)
SELECT 
    name,
    preco,
    nota,
    -- Agora a conta é feita usando a média do bairro todo
    ROUND((preco - media_bairro) / media_bairro * 100, 2) AS diferenca_percentual
FROM (
    SELECT 
        name, 
        preco, 
        nota, 
        bairro,
        -- A Window Function roda aqui, olhando para TODOS os dados do banco
        AVG(preco) OVER(PARTITION BY bairro) as media_bairro
    FROM dados_limpos
) tabela_calculada
WHERE bairro = 'Leblon'
  AND nota >= 4.9
  AND preco < media_bairro -- Filtramos só DEPOIS de ter a média calculada
ORDER BY preco ASC
LIMIT 5;