Os primeiros 70% do curso SQL parecem bem diretos. As dificuldades começam nos 30% restantes.De 2015 a 2019, passei por quatro entrevistas para os cargos de analista de dados e especialista em análise de dados em mais de uma dúzia de empresas. Depois de outra entrevista malsucedida em 2017 - quando fiquei confusa sobre questões complexas de SQL - comecei a compilar um livro de problemas com perguntas de SQL de média e alta complexidade, a fim de me preparar melhor para as entrevistas. Este guia foi útil na última rodada de entrevistas em 2019. No ano passado, compartilhei este guia com alguns amigos e, graças ao tempo livre extra devido a uma pandemia, eu o aprimorei e compilei este documento.Existem muitos ótimos tutoriais SQL para iniciantes. Meus favoritos sãoCursos interativos de SQL e Select Star SQL da Codecademy de Zi Chung Kao. Mas, na realidade, os primeiros 70% do curso SQL são bastante simples, e as dificuldades reais começam nos 30% restantes, que não são abordados nos guias para iniciantes. Portanto, em entrevistas para analistas de dados e especialistas em análise de dados em empresas de tecnologia, muitas vezes fazem perguntas sobre esses 30%.Surpreendentemente, não encontrei uma fonte exaustiva sobre esses problemas de dificuldade média, por isso compilei este guia.É útil para entrevistas, mas, ao mesmo tempo, aumentará sua eficácia em seus empregos atuais e futuros. Pessoalmente, acredito que alguns dos modelos SQL mencionados também são úteis para sistemas ETL que executam ferramentas de relatório e funções de análise de dados para identificar tendências.
Você precisa entender que, durante entrevistas com analistas de dados e analistas de dados, eles fazem perguntas não apenas sobre SQL. Outros tópicos comuns incluem discussão de projetos anteriores, teste A / B, desenvolvimento de métricas e questões analíticas abertas. Há cerca de três anos, Quora postou dicas sobre entrevistas para uma posição de analista de produtos no Facebook. Lá, este tópico é discutido em mais detalhes. No entanto, se o aprimoramento do seu conhecimento de SQL o ajudar na sua entrevista, então este guia vale a pena.No futuro, posso portar o código deste guia para um site como o Select Star SQLpara facilitar a gravação de instruções SQL - e ver o resultado da execução do código em tempo real. Como opção, adicione perguntas como problemas à plataforma para se preparar para as entrevistas do LeetCode . Enquanto isso, eu só queria publicar este documento para que as pessoas possam se familiarizar com essas informações.Suposições feitas e como usar o manual
Pressupostos sobre o conhecimento da linguagem SQL: Supõe-se que você tenha conhecimento prático de SQL. Você provavelmente o usa frequentemente no trabalho, mas deseja aprimorar suas habilidades em tópicos como auto-associações e funções de janela.Como usar este manual: Como um quadro ou um notebook virtual é frequentemente usado em uma entrevista (sem compilar o código), recomendo pegar um lápis e um papel e escrever soluções para cada problema e, depois de concluir, compare suas anotações com as respostas. Ou elabore suas respostas com um amigo que atuará como entrevistador!- Pequenos erros de sintaxe não importam muito durante uma entrevista com um quadro branco ou bloco de notas. Mas eles podem distrair o entrevistador, então, idealmente, tente reduzir seu número para concentrar toda a atenção na lógica.
- As respostas dadas não são necessariamente a única maneira de resolver cada problema. Sinta-se livre para escrever comentários com soluções adicionais que você pode adicionar a este guia!
Dicas para resolver tarefas complexas em entrevistas SQL
Primeiro, dicas padrão para todas as entrevistas de programação ...- Ouça atentamente a descrição do problema, repita a essência do problema para o entrevistador.
- Formule um caso limite para demonstrar que realmente entende o problema (ou seja, uma linha que não será incluída na consulta SQL final que você escreverá)
- ( ) , — : ,
- SQL, , . , .
Alguns dos problemas listados aqui são adaptados das entradas antigas do blog do Periscope (escritas principalmente por Sean Cook por volta de 2014, embora sua autoria pareça ter sido removida dos materiais após a fusão do SiSense com o Periscope ), bem como das discussões no StackOverflow. Se necessário, as fontes são marcadas no início de cada pergunta.No Select Star SQL, também há uma boa seleção de quebra-cabeças , questões complementares deste documento.Observe que essas perguntas não são cópias literais das perguntas de minhas próprias entrevistas e não foram usadas nas empresas em que trabalhei ou trabalho.Tarefas de auto-associação
No. 1. Variação percentual mês a mês
Contexto: geralmente é útil saber como uma métrica importante muda, por exemplo, o público mensal de usuários ativos, mês a mês. Digamos que temos uma tabela logins
neste formulário:| user_id | data |
| --------- | ------------ |
| 1 | 01/07/2018 |
| 234 02/07/2018 |
| 3 02/07/2018 |
| 1 | 02/07/2018 |
| ... ...
| 234 04-10-2018 |
Objetivo : encontre a alteração percentual mensal no público mensal de usuários ativos (MAU).Solução:(Esta solução, como os outros blocos de código deste documento, contém comentários sobre elementos de sintaxe SQL que podem diferir entre diferentes variantes SQL e outras notas)WITH mau AS
(
SELECT
DATE_TRUNC('month', date) month_timestamp,
COUNT(DISTINCT user_id) mau
FROM
logins
GROUP BY
DATE_TRUNC('month', date)
)
SELECT
a.month_timestamp previous_month,
a.mau previous_mau,
b.month_timestamp current_month,
b.mau current_mau,
ROUND(100.0*(b.mau - a.mau)/a.mau,2) AS percent_change
FROM
mau a
JOIN
mau b ON a.month_timestamp = b.month_timestamp - interval '1 month'
No. 2. Estrutura da árvore de marcação
Contexto: suponha que você tenha uma tabela tree
com duas colunas: a primeira indica os nós e a segunda os nós pais.pai do nó
12
2 5
3 5
4 3
5 NULL
A tarefa: escrever SQL de forma que designemos cada nó como interno (raiz), raiz (raiz) ou nó final / folha (folha), para que, para os valores acima, você obtenha o seguinte:node label
1 Leaf
2 Inner
3 Inner
4 Leaf
5 Root
(Nota: mais informações sobre a terminologia da estrutura de dados em forma de árvore podem ser encontradas aqui . No entanto, não é necessário resolver esse problema!)Solução:Obrigado: esta é uma solução mais generalizada proposta por Fabian Hoffman em 2 de maio de 2020. Obrigado FabianWITH join_table AS
(
SELECT
cur.node,
cur.parent,
COUNT(next.node) AS num_children
FROM
tree cur
LEFT JOIN
tree next ON (next.parent = cur.node)
GROUP BY
cur.node,
cur.parent
)
SELECT
node,
CASE
WHEN parent IS NULL THEN "Root"
WHEN num_children = 0 THEN "Leaf"
ELSE "Inner"
END AS label
FROM
join_table
Solução alternativa, sem conexões explícitas:Reconhecimento: William Chardgin em 02 de maio de 2020 chamou a atenção para a necessidade da condição WHERE parent IS NOT NULL
de que este retorno solução Leaf
vez NULL
. Obrigado William!SELECT
node,
CASE
WHEN parent IS NULL THEN 'Root'
WHEN node NOT IN
(SELECT parent FROM tree WHERE parent IS NOT NULL) THEN 'Leaf'
WHEN node IN (SELECT parent FROM tree) AND parent IS NOT NULL THEN 'Inner'
END AS label
from
tree
No. 3. Retenção de usuários por mês (várias partes)
Agradecimento: Esta tarefa foi adaptada do artigo de blog do SiSense, “Usando auto-associações para calcular retenção, vazão e reativação ” .Parte 1
Contexto: suponha que tenhamos estatísticas sobre a autorização do usuário em um site na tabela logins
:| user_id | data |
| --------- | ------------ |
| 1 | 01/07/2018 |
| 234 02/07/2018 |
| 3 02/07/2018 |
| 1 | 02/07/2018 |
| ... ...
| 234 04-10-2018 |
Tarefa: escreva uma solicitação que receba o número de usuários retidos por mês. No nosso caso, esse parâmetro é definido como o número de usuários que efetuaram login no sistema neste e no mês anterior.Decisão:SELECT
DATE_TRUNC('month', a.date) month_timestamp,
COUNT(DISTINCT a.user_id) retained_users
FROM
logins a
JOIN
logins b ON a.user_id = b.user_id
AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) +
interval '1 month'
GROUP BY
date_trunc('month', a.date)
Agradecimento:Tom Moertel destacou que a pré-duplicação de user_id antes da autoatendimento torna a solução mais eficiente e sugeriu o código abaixo. Obrigado Tom!Solução alternativa:WITH DistinctMonthlyUsers AS (
SELECT DISTINCT
DATE_TRUNC('MONTH', a.date) AS month_timestamp,
user_id
FROM logins
)
SELECT
CurrentMonth.month_timestamp month_timestamp,
COUNT(PriorMonth.user_id) AS retained_user_count
FROM
DistinctMonthlyUsers AS CurrentMonth
LEFT JOIN
DistinctMonthlyUsers AS PriorMonth
ON
CurrentMonth.month_timestamp = PriorMonth.month_timestamp + INTERVAL '1 MONTH'
AND
CurrentMonth.user_id = PriorMonth.user_id
Parte 2
Tarefa: agora assumimos a tarefa anterior de calcular o número de usuários retidos por mês - e a invertemos. Escreveremos uma solicitação para contar os usuários que não retornaram ao site este mês. Ou seja, usuários "perdidos".Decisão:SELECT
DATE_TRUNC('month', a.date) month_timestamp,
COUNT(DISTINCT b.user_id) churned_users
FROM
logins a
FULL OUTER JOIN
logins b ON a.user_id = b.user_id
AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) +
interval '1 month'
WHERE
a.user_id IS NULL
GROUP BY
DATE_TRUNC('month', a.date)
Observe que esse problema também pode ser resolvido com LEFT
ou RIGHT
.Parte 3
Nota: esta é provavelmente uma tarefa mais difícil do que você será oferecido em uma entrevista real. Pense nisso mais como um quebra-cabeça - ou você pode pular e passar para a próxima tarefa.Contexto : fizemos um bom trabalho com dois problemas anteriores. Sob os termos da nova tarefa, agora temos uma tabela de usuários perdidos user_churns
. Se o usuário estiver ativo no mês passado, mas não estiver ativo, ele será inserido na tabela desse mês. Aqui está o que parece user_churns
:| user_id | month_date |
| --------- | ------------ |
| 1 | 01/05/2018 |
| 234 01/05/2018 |
| 3 01/05/2018 |
| 12 01/05/2018 |
| ... ...
| 234 01-10-2018 |
Tarefa : agora você deseja realizar uma análise de coorte, ou seja, uma análise da totalidade dos usuários ativos que foram reativados no passado . Crie uma tabela com esses usuários. Você pode usar tabelas user_churns
e criar uma coorte logins
. No Postgres, o registro de data e hora atual é acessível via current_timestamp
.Decisão:WITH user_login_data AS
(
SELECT
DATE_TRUNC('month', a.date) month_timestamp,
a.user_id,
MAX(b.month_date) as most_recent_churn,
MAX(DATE_TRUNC('month', c.date)) as most_recent_active
FROM
logins a
JOIN
user_churns b
ON a.user_id = b.user_id AND DATE_TRUNC('month', a.date) > b.month_date
JOIN
logins c
ON a.user_id = c.user_id
AND
DATE_TRUNC('month', a.date) > DATE_TRUNC('month', c.date)
WHERE
DATE_TRUNC('month', a.date) = DATE_TRUNC('month', current_timestamp)
GROUP BY
DATE_TRUNC('month', a.date),
a.user_id
HAVING
most_recent_churn > most_recent_active
No. 4. Total crescente
Agradecimento: Esta tarefa foi adaptada do artigo do blog SiSense , Cash Flow Modeling in SQL .Contexto: suponha que tenhamos uma tabela transactions
neste formato:| data | cash_flow |
| ------------ | ----------- |
| 01-01-2018 | -1000
| 02-01-2018 | -100
| 03-01-2018 | 50
| ... ...
Onde cash_flow
está a receita menos os custos de cada dia.Objetivo: escreva uma solicitação para obter um total contínuo de fluxo de caixa todos os dias de forma que, no final, você obtenha uma tabela neste formulário:| data | cumulative_cf |
| ------------ | --------------- |
| 01-01-2018 | -1000
| 02-01-2018 | -1100
| 03-01-2018 | -1050
| ... ...
Decisão:SELECT
a.date date,
SUM(b.cash_flow) as cumulative_cf
FROM
transactions a
JOIN b
transactions b ON a.date >= b.date
GROUP BY
a.date
ORDER BY
date ASC
Uma solução alternativa usando uma função de janela (mais eficiente!):SELECT
date,
SUM(cash_flow) OVER (ORDER BY date ASC) as cumulative_cf
FROM
transactions
ORDER BY
date ASC
No. 5. Média Móvel
Agradecimento: Esta tarefa foi adaptada do artigo do blog SiSense , Médias móveis no MySQL e SQL Server .Nota: a média móvel pode ser calculada de várias maneiras. Aqui usamos a média anterior. Assim, a métrica para o sétimo dia do mês será a média dos seis dias anteriores e dele próprio.Contexto : suponha que tenhamos uma tabela signups
neste formato:| data | inscrições |
| ------------ | ---------- |
| 01-01-2018 | 10
| 02-01-2018 | 20
| 03-01-2018 | 50
| ... ...
| 01-10-2018 | 35
Tarefa : escreva uma solicitação para obter uma média móvel de 7 dias dos registros diários.Decisão:SELECT
a.date,
AVG(b.sign_ups) average_sign_ups
FROM
signups a
JOIN
signups b ON a.date <= b.date + interval '6 days' AND a.date >= b.date
GROUP BY
a.date
No. 6. Várias condições de conexão
Agradecimento: Esta tarefa foi adaptada do artigo de blog do SiSense, “Analisando Seu Email Usando SQL” .Contexto: digamos que nossa tabela emails
contenha e-mails enviados pelo endereço zach@g.com
e recebidos nele:| id | assunto | de | para | timestamp |
| ---- | ---------- | -------------- | -------------- | --- ------------------ |
| 1 | Yosemite zach@g.com | thomas@g.com | 2018-01-02 12:45:03
| 2 Big Sur sarah@g.com | thomas@g.com | 2018-01-02 16:30:01 |
| 3 Yosemite thomas@g.com | zach@g.com | 2018-01-02 16:35:04 |
| 4 Correndo | jill@g.com | zach@g.com | 2018-01-03 08:12:45 |
| 5 Yosemite zach@g.com | thomas@g.com | 2018-01-03 14:02:01 |
| 6 Yosemite thomas@g.com | zach@g.com | 2018-01-03 15:01:05 |
| .. .. .. .. ..
Tarefa: escreva uma solicitação para obter tempo de resposta para cada letra ( id
) enviada para zach@g.com
. Não inclua cartas para outros endereços. Suponha que cada segmento tenha um tema único. Lembre-se de que o segmento pode ter várias cartas de ida e volta entre zach@g.com
e outros destinatários.Decisão:SELECT
a.id,
MIN(b.timestamp) - a.timestamp as time_to_respond
FROM
emails a
JOIN
emails b
ON
b.subject = a.subject
AND
a.to = b.from
AND
a.from = b.to
AND
a.timestamp < b.timestamp
WHERE
a.to = 'zach@g.com'
GROUP BY
a.id
Tarefas para funções da janela
Não. 1. Encontre o identificador com o valor máximo
Contexto: suponha que tenhamos uma tabela salaries
com dados sobre departamentos e salários dos funcionários no seguinte formato: depname | empno | salário |
----------- + ------- + -------- +
desenvolver | 11 5200
desenvolver | 7 4200
desenvolver | 9 4500
desenvolver | 8 6000
desenvolver | 10 5200
pessoal | 5 3500
pessoal | 2 3900
vendas | 3 4800
vendas | 1 | 5000
vendas | 4 4800
Tarefa : escreva uma solicitação para obter empno
o salário mais alto. Verifique se a sua solução lida com casos de salários iguais!Decisão:WITH max_salary AS (
SELECT
MAX(salary) max_salary
FROM
salaries
)
SELECT
s.empno
FROM
salaries s
JOIN
max_salary ms ON s.salary = ms.max_salary
Solução alternativa usando RANK()
:WITH sal_rank AS
(SELECT
empno,
RANK() OVER(ORDER BY salary DESC) rnk
FROM
salaries)
SELECT
empno
FROM
sal_rank
WHERE
rnk = 1;
No. 2. Valor médio e classificação com uma função de janela (várias partes)
Parte 1
Contexto : suponha que tenhamos uma tabela salaries
neste formato: depname | empno | salário |
----------- + ------- + -------- +
desenvolver | 11 5200
desenvolver | 7 4200
desenvolver | 9 4500
desenvolver | 8 6000
desenvolver | 10 5200
pessoal | 5 3500
pessoal | 2 3900
vendas | 3 4800
vendas | 1 | 5000
vendas | 4 4800
Tarefa: escreva uma consulta que retorne a mesma tabela, mas com uma nova coluna que mostre o salário médio para o departamento. Esperamos uma tabela como esta: depname | empno | salário | avg_salary |
----------- + ------- + -------- + ------------ +
desenvolver | 11 5200 5020
desenvolver | 7 4200 5020
desenvolver | 9 4500 5020
desenvolver | 8 6000 5020
desenvolver | 10 5200 5020
pessoal | 5 3500 3700
pessoal | 2 3900 3700
vendas | 3 4800 4867
vendas | 1 | 5000 4867
vendas | 4 4800 4867
Decisão:SELECT
*,
ROUND(AVG(salary),0) OVER (PARTITION BY depname) avg_salary
FROM
salaries
Parte 2
Tarefa: escreva uma consulta que inclua uma coluna com a posição de cada funcionário no quadro de horários com base no salário em seu departamento, onde o funcionário com o salário mais alto obtém a posição 1. Esperaríamos uma tabela neste formato: depname | empno | salário | salário_ranco |
----------- + ------- + -------- + ------------- +
desenvolver | 11 5200 2
desenvolver | 7 4200 5
desenvolver | 9 4500 4
desenvolver | 8 6000 1 |
desenvolver | 10 5200 2
pessoal | 5 3500 2
pessoal | 2 3900 1 |
vendas | 3 4800 2
vendas | 1 | 5000 1 |
vendas | 4 4800 2
Decisão:SELECT
*,
RANK() OVER(PARTITION BY depname ORDER BY salary DESC) salary_rank
FROM
salaries
Outras tarefas de dificuldade média e alta
No. 1. Histogramas
Contexto: digamos que temos uma tabela em sessions
que cada linha representa uma sessão de streaming de vídeo com duração em segundos:| session_id | length_seconds |
| ------------ | ---------------- |
| 1 | 23
| 2 453
| 3 27
| .. ..
Tarefa: escreva uma consulta para calcular o número de sessões que caem em intervalos de cinco segundos, ou seja, para o fragmento acima, o resultado será algo como isto:| balde | contar |
| --------- | ------- |
| 20-25 2
| 450-455 1 |
A pontuação máxima conta para os rótulos de linha adequados ("5-10" etc.)Solução:WITH bin_label AS
(SELECT
session_id,
FLOOR(length_seconds/5) as bin_label
FROM
sessions
)
SELECT
CONCATENTATE(STR(bin_label*5), '-', STR(bin_label*5+5)) bucket,
COUNT(DISTINCT session_id) count
GROUP BY
bin_label
ORDER BY
bin_label ASC
No. 2. Conexão cruzada (várias partes)
Parte 1
Contexto: digamos que temos uma tabela em state_streams
que o nome do estado e o número total de horas de streaming da hospedagem de vídeo são indicados em cada linha:| estado | total_streams |
| ------- | --------------- |
| NC 34569
| SC 33999
| CA 98324
| MA 19345
| .. ..
(De fato, tabelas agregadas desse tipo geralmente têm uma coluna de data, mas a excluiremos para esta tarefa)Tarefa: escreva uma consulta para obter pares de estados com um número total de encadeamentos dentro de mil um do outro. Para o snippet acima, gostaríamos de ver algo como:| state_a | state_b
| --------- | --------- |
| NC SC
| SC NC
Decisão:SELECT
a.state as state_a,
b.state as state_b
FROM
state_streams a
CROSS JOIN
state_streams b
WHERE
ABS(a.total_streams - b.total_streams) < 1000
AND
a.state <> b.state
Para obter informações, as junções cruzadas também podem ser gravadas sem especificar explicitamente as junções:SELECT
a.state as state_a,
b.state as state_b
FROM
state_streams a, state_streams b
WHERE
ABS(a.total_streams - b.total_streams) < 1000
AND
a.state <> b.state
Parte 2
Nota: esta é uma questão de bônus, e não um modelo SQL realmente importante. Você pode pular!Tarefa: como posso modificar o SQL de uma solução anterior para remover duplicatas? Por exemplo, o exemplo da mesma tabela, para vapor NC
e SC
houve apenas uma vez, não duas.Decisão:SELECT
a.state as state_a,
b.state as state_b
FROM
state_streams a, state_streams b
WHERE
ABS(a.total_streams - b.total_streams) < 1000
AND
a.state > b.state
No. 3. Cálculos avançados
Agradecimento: Esta tarefa foi adaptada de uma discussão sobre uma pergunta que eu fiz no StackOverflow (meu apelido é zthomas.nc).Nota: esta é provavelmente uma tarefa mais difícil do que você será oferecido em uma entrevista real. Pense nisso mais como um quebra-cabeça - ou você pode pular!Contexto: suponha que tenhamos uma tabela table
desse tipo, onde user
valores diferentes de uma classe podem corresponder ao mesmo usuário class
:| usuário | classe |
| ------ | ------- |
| 1 | a |
| 1 | b
| 1 | b
| 2 b
| 3 a |
Problema: Suponha que haja apenas dois valores possíveis para uma classe. Escreva uma consulta para calcular o número de usuários em cada classe. Nesse caso, os usuários com os dois rótulos a
e b
devem se referir à classe b
.Para nossa amostra, obtemos o seguinte resultado:| classe | contar |
| ------- | ------- |
| a | 1 |
| b 2
Decisão:WITH usr_b_sum AS
(
SELECT
user,
SUM(CASE WHEN class = 'b' THEN 1 ELSE 0 END) num_b
FROM
table
GROUP BY
user
),
usr_class_label AS
(
SELECT
user,
CASE WHEN num_b > 0 THEN 'b' ELSE 'a' END class
FROM
usr_b_sum
)
SELECT
class,
COUNT(DISTINCT user) count
FROM
usr_class_label
GROUP BY
class
ORDER BY
class ASC
Uma solução alternativa usa instruções SELECT
nos operadores SELECT
e UNION
:SELECT
"a" class,
COUNT(DISTINCT user_id) -
(SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count
UNION
SELECT
"b" class,
(SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count