대부분의 후보자들은 데이터 인터뷰에서 이러한 SQL 개념을 실패시킵니다
저자의 이미지 | 캔버
면접관의 임무는 광고 된 직책에 가장 적합한 후보자를 찾는 것입니다. 그렇게함으로써, 그들은 기꺼이 SQL 인터뷰 질문을하여 그들이 당신을 보호 할 수 있는지 알아볼 것입니다. 후보자가 종종 실패하는 몇 가지 SQL 개념이 있습니다.
바라건대, 당신은 그 운명을 피하는 사람들 중 하나가 될 것입니다. 아래에서 이러한 개념을 자세히 설명하고 특정 문제를 올바르게 해결하는 방법에 대한 예를 작성하십시오.
틀 1. 창 함수
어려운 이유 : 후보자들은 각각을 암기합니다 창 함수 그러나 창 프레임, 파티션 또는 주문이 실제로 어떻게 작동하는지 이해하지 못합니다.
일반적인 실수 : 일반적인 실수는 명시되지 않습니다 ORDER BY 순위에서 창 함수 또는 값 창 함수 (예 : LEAD() 또는 LAG()그리고 쿼리가 작동하거나 결과가 결정적이라고 기대합니다.
예 :이 예에서는 이전 구매 후 7 일 이내에 두 번째 구매를 한 사용자를 찾아야합니다.
이 쿼리를 쓸 수 있습니다.
WITH ordered_tx AS (
SELECT user_id,
created_at::date AS tx_date,
LAG(created_at::DATE) OVER (PARTITION BY user_id) AS prev_tx_date
FROM amazon_transactions
)
SELECT DISTINCT user_id
FROM ordered_tx
WHERE prev_tx_date IS NOT NULL AND tx_date - prev_tx_date 언뜻보기에 모든 것이 옳은 것처럼 보일 수 있습니다. 코드는 정답으로 보일 수있는 것을 출력합니다.
우선, 우리는 코드가 전혀 작동한다는 것이 운이 좋다! 이것은 내가 그것을 쓰기 때문에 단순히 발생합니다 Postgresql. 다른 SQL 맛에서는 오류가 발생합니다. ORDER BY 순위 및 분석 창 함수에 필수적입니다.
둘째, 출력이 잘못되었습니다. 나는 거기에 없어야 할 줄을 강조했다. 그렇다면 왜 그들이 나타나나요?
우리가 지정하지 않았기 때문에 나타납니다 ORDER BY 절의 조항 LAG() 창 함수. 그것 없이는 행 순서가 임의적입니다. 따라서 현재 트랜잭션을 해당 사용자의 임의의 이전 행과 비교하고 있습니다.
이것은 질문이 묻는 것이 아닙니다. 각 트랜잭션을 날짜별로 이전 거래와 비교해야합니다. 다시 말해, 우리는 이것을 ORDER BY 내의 절 LAG() 기능.
WITH ordered_tx AS (
SELECT user_id,
created_at::date AS tx_date,
LAG(created_at::DATE) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_tx_date
FROM amazon_transactions
)
SELECT DISTINCT user_id
FROM ordered_tx
WHERE prev_tx_date IS NOT NULL AND tx_date - prev_tx_date 틀 2. 골재로 필터링 (특히 대 대상)
어려운 이유 : 사람들은 종종 SQL의 실행 순서를 이해하지 못합니다. FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. 이 순서는 그것을 의미합니다 WHERE 집계 전에 행을 필터링하고 HAVING 이후 필터. 또한 논리적으로는 집계 함수를 사용할 수 없다는 것을 의미합니다. WHERE 절.
일반적인 실수 : 집계 함수를 사용하려고합니다 WHERE 그룹화 된 쿼리에서 오류가 발생합니다.
예 :이 인터뷰 질문은 각 와이너리의 총 수익을 찾도록 요청합니다. 90이 품종에 대해 가장 낮은 포인트 인 와이너리 만 고려해야합니다.
많은 사람들이 이것을 쉬운 질문으로 보고이 쿼리를 서둘러 씁니다.
SELECT winery,
variety,
SUM(price) AS total_revenue
FROM winemag_p1
WHERE MIN(points) >= 90
GROUP BY winery, variety
ORDER BY winery, total_revenue DESC;그러나이 코드는 집계 함수가 허용되지 않는다는 오류가 발생합니다. WHERE 절. 이것은 거의 모든 것을 설명합니다. 솔루션? 필터링 조건을 옮깁니다 WHERE 에게 HAVING.
SELECT winery,
variety,
SUM(price) AS total_revenue
FROM winemag_p1
GROUP BY winery, variety
HAVING MIN(points) >= 90
ORDER BY winery, total_revenue DESC;틀 3. 시간 기반 또는 이벤트 기반 비교를위한 자체 조인
어려운 이유 : 아이디어 자체와 함께 테이블에 합류합니다 직관적이지 않기 때문에 후보자들은 종종 그것이 옵션이라는 것을 잊어 버립니다.
일반적인 실수 : 하위 쿼리를 사용하고 테이블 자체로 결합 할 때 쿼리를 복잡하게 사용하는 것은 특히 날짜 나 이벤트로 필터링 할 때 더 간단하고 빠릅니다.
예 : 2020 년 1 월 1 일부터 2020 년 7 월 1 일 사이에 모든 통화 환율의 변경을 보여줄 것을 요구하는 질문이 있습니다.
7 월 1 일 환율을 가져 오는 외부 상관 하위 쿼리를 작성하여이를 해결 한 다음 내부 하위 퀘스트에서 나오는 1 월 1 일 환율을 빼냅니다.
SELECT jan_rates.source_currency,
(SELECT exchange_rate
FROM sf_exchange_rate
WHERE source_currency = jan_rates.source_currency AND date="2020-07-01") - jan_rates.exchange_rate AS difference
FROM (SELECT source_currency, exchange_rate
FROM sf_exchange_rate
WHERE date="2020-01-01"
) AS jan_rates;이것은 올바른 출력을 반환하지만 이러한 솔루션은 불필요하게 복잡합니다. 코드 줄이 적은 훨씬 간단한 솔루션은 테이블 자체가 자체 합류 한 다음 두 개의 날짜 필터링 조건을 WHERE 절.
SELECT jan.source_currency,
jul.exchange_rate - jan.exchange_rate AS difference
FROM sf_exchange_rate jan
JOIN sf_exchange_rate jul ON jan.source_currency = jul.source_currency
WHERE jan.date="2020-01-01" AND jul.date="2020-07-01";틀 4. 하위 쿼리 대 공통 테이블 표현식 (CTES)
어려운 이유 : 사람들은 종종 공통 테이블 표현식 (CTE) 전에 배우고 계층 로직이있는 쿼리에 계속 사용하기 때문에 하위 쿼리에 갇히게됩니다. 그러나 하위 쿼리는 매우 빨리 지저분해질 수 있습니다.
일반적인 실수 : 깊게 중첩 된 사용 SELECT CTE가 훨씬 간단 할 때의 진술.
예 : Google과 Netflix의 인터뷰 질문에서 가장 자주 나타나는 장르 내에서 평균 영화 등급을 기반으로 최고 배우를 찾아야합니다.
CTE를 사용하는 솔루션은 다음과 같습니다.
WITH genre_stats AS
(SELECT actor_name,
genre,
COUNT(*) AS movie_count,
AVG(movie_rating) AS avg_rating
FROM top_actors_rating
GROUP BY actor_name,
genre),
max_genre_count AS
(SELECT actor_name,
MAX(movie_count) AS max_count
FROM genre_stats
GROUP BY actor_name),
top_genres AS
(SELECT gs.*
FROM genre_stats gs
JOIN max_genre_count mgc ON gs.actor_name = mgc.actor_name
AND gs.movie_count = mgc.max_count),
top_genre_avg AS
(SELECT actor_name,
MAX(avg_rating) AS max_avg_rating
FROM top_genres
GROUP BY actor_name),
filtered_top_genres AS
(SELECT tg.*
FROM top_genres tg
JOIN top_genre_avg tga ON tg.actor_name = tga.actor_name
AND tg.avg_rating = tga.max_avg_rating),
ranked_actors AS
(SELECT *,
DENSE_RANK() OVER (
ORDER BY avg_rating DESC) AS rank
FROM filtered_top_genres),
final_selection AS
(SELECT MAX(rank) AS max_rank
FROM ranked_actors
WHERE rank 상대적으로 복잡하지만 여전히 6 개의 명확한 CTE로 구성되며 코드의 가독성은 명확한 별칭으로 향상되었습니다.
같은 솔루션이 하위 쿼리 만 사용하는 모습이 궁금하십니까? 여기 있습니다.
SELECT ra.actor_name,
ra.genre,
ra.avg_rating
FROM (
SELECT *,
DENSE_RANK() OVER (ORDER BY avg_rating DESC) AS rank
FROM (
SELECT tg.*
FROM (
SELECT gs.*
FROM (
SELECT actor_name,
genre,
COUNT(*) AS movie_count,
AVG(movie_rating) AS avg_rating
FROM top_actors_rating
GROUP BY actor_name, genre
) AS gs
JOIN (
SELECT actor_name,
MAX(movie_count) AS max_count
FROM (
SELECT actor_name,
genre,
COUNT(*) AS movie_count,
AVG(movie_rating) AS avg_rating
FROM top_actors_rating
GROUP BY actor_name, genre
) AS genre_stats
GROUP BY actor_name
) AS mgc
ON gs.actor_name = mgc.actor_name AND gs.movie_count = mgc.max_count
) AS tg
JOIN (
SELECT actor_name,
MAX(avg_rating) AS max_avg_rating
FROM (
SELECT gs.*
FROM (
SELECT actor_name,
genre,
COUNT(*) AS movie_count,
AVG(movie_rating) AS avg_rating
FROM top_actors_rating
GROUP BY actor_name, genre
) AS gs
JOIN (
SELECT actor_name,
MAX(movie_count) AS max_count
FROM (
SELECT actor_name,
genre,
COUNT(*) AS movie_count,
AVG(movie_rating) AS avg_rating
FROM top_actors_rating
GROUP BY actor_name, genre
) AS genre_stats
GROUP BY actor_name
) AS mgc
ON gs.actor_name = mgc.actor_name AND gs.movie_count = mgc.max_count
) AS top_genres
GROUP BY actor_name
) AS tga
ON tg.actor_name = tga.actor_name AND tg.avg_rating = tga.max_avg_rating
) AS filtered_top_genres
) AS ra
WHERE ra.rank 하위 쿼리에 중복 논리가 반복됩니다. 얼마나 많은 하위 쿼리입니까? 나는 모른다. 코드는 유지 관리가 불가능합니다. 방금 작성했지만 내일 무언가를 바꾸고 싶다면 여전히 반나절이 필요합니다. 또한 완전히 의미없는 하위 쿼리 별명은 도움이되지 않습니다.
틀 5. 논리에서 널을 처리합니다
어려운 이유 : 후보자들은 종종 그렇게 생각합니다 NULL 무언가와 동일합니다. 그렇지 않습니다. NULL 그 자체도 아닙니다. 관련 논리 NULLs는 실제 값과 관련된 논리와 다르게 행동합니다.
일반적인 실수 : 사용 = NULL 대신 IS NULL 필터링 또는 출력 행 누락에서 NULLs 조건 논리를 깨뜨립니다.
예 : IBM의 인터뷰 질문이 있습니다.이 질문은 총 상호 작용 수와 각 고객에 대해 생성 된 총 컨텐츠 수를 계산하도록 요청합니다.
너무 까다로워지기 때문에 두 개의 CTE 로이 솔루션을 작성할 수 있습니다. 여기서 하나의 CTE는 고객 당 상호 작용 수를 계산하고 다른 CTE는 고객이 생성 한 콘텐츠 항목 수를 계산합니다. 결승에서 SELECT너 FULL OUTER JOIN 두 개의 CTE와 솔루션이 있습니다. 오른쪽?
WITH interactions_summary AS
(SELECT customer_id,
COUNT(*) AS total_interactions
FROM customer_interactions
GROUP BY customer_id),
content_summary AS
(SELECT customer_id,
COUNT(*) AS total_content_items
FROM user_content
GROUP BY customer_id)
SELECT i.customer_id,
i.total_interactions,
c.total_content_items
FROM interactions_summary AS i
FULL OUTER JOIN content_summary AS c ON i.customer_id = c.customer_id
ORDER BY customer_id;거의 옳습니다. 출력은 다음과 같습니다. (그건 그렇고, 당신은 NULL. 그것이 Stratascratch UI가 표시하는 방식이지만, 엔진은 여전히 그들이 무엇인지 처리합니다. NULL 값).
강조 표시된 행에는 포함됩니다 NULL에스. 이렇게하면 출력이 잘못되었습니다. 에이 NULL 가치는 고객 ID 나 상호 작용 및 내용의 수가 아니며, 질문이 명시 적으로 표시하도록 요청합니다.
위의 솔루션에서 우리가 놓친 것은입니다 COALESCE() 처리하려면 NULL결승에서 s SELECT. 이제 상호 작용이없는 모든 고객은 content_summary CTE. 또한 상호 작용이나 콘텐츠가없는 고객의 경우 이제 교체하겠습니다. NULL 0으로 유효한 숫자입니다.
WITH interactions_summary AS
(SELECT customer_id,
COUNT(*) AS total_interactions
FROM customer_interactions
GROUP BY customer_id),
content_summary AS
(SELECT customer_id,
COUNT(*) AS total_content_items
FROM user_content
GROUP BY customer_id)
SELECT COALESCE(i.customer_id, c.customer_id) AS customer_id,
COALESCE(i.total_interactions, 0) AS total_interactions,
COALESCE(c.total_content_items, 0) AS total_content_items
FROM interactions_summary AS i
FULL OUTER JOIN content_summary AS c ON i.customer_id = c.customer_id
ORDER BY customer_id;틀 6. 그룹 기반 중복 제거
어려운 이유 : 그룹 기반 중복 제거는 그룹 당 하나의 행 (예 :“가장 최근”,“가장 높은 점수”등을 선택한다는 것을 의미합니다. 처음에는 사용자 당 하나의 행만 선택하면됩니다. 그러나 당신은 사용할 수 없습니다 GROUP BY 집계하지 않는 한. 반면에, 당신은 종종 집계와 단일 값이 아니라 전체 행이 필요합니다. GROUP BY 반품.
일반적인 실수 : 사용 GROUP BY + LIMIT 1 (또는 뚜렷한이것은 대신 postgresql-specific입니다 ROW_NUMBER() 또는 RANK()유대가 포함되기를 원한다면 후자.
예 :이 질문은 매월 베스트셀러 품목을 식별하도록 요청하며 해마다 몇 달을 분리 할 필요가 없습니다. 베스트셀러 아이템은 다음과 같이 계산됩니다 unitprice * quantity.
순진한 접근법은 이것입니다. 먼저, 판매 월을 추출하십시오 invoicedate선택하다 description합산하여 총 판매량을 찾으십시오 unitprice * quantity. 그런 다음 월별 판매 및 제품 설명을 얻으려면 간단히 GROUP BY 이 두 열. 마지막으로, 우리 는만 사용하면됩니다 ORDER BY 출력을 최상의 제품에서 최악의 판매 제품으로 정렬하고 사용합니다. LIMIT 1 첫 번째 행, 즉 베스트셀러 아이템 만 출력합니다.
SELECT DATE_PART('MONTH', invoicedate) AS sale_month,
description,
SUM(unitprice * quantity) AS total_paid
FROM online_retail
GROUP BY sale_month, description
ORDER BY total_paid DESC
LIMIT 1;내가 말했듯이, 이것은 순진합니다. 출력은 우리가 필요로하는 것과 다소 비슷하지만, 우리는 단지 하나가 아니라 매월이 필요합니다.
올바른 접근법 중 하나는 사용하는 것입니다 RANK() 창 함수. 이 접근법을 통해 이전 코드와 비슷한 방법을 따릅니다. 차이점은 이제 쿼리가 FROM 절. 또한 우리는 사용합니다 RANK() 매월 데이터를 분할 한 다음 각 파티션 내에서 행을 순위 (즉, 매달 별도로) 베스트 판매에서 최악의 판매 항목으로 순위를 매 깁니다.
그런 다음 기본 쿼리에서 필요한 열을 선택하고 순위가 1 인 경우 출력 만 사용합니다. WHERE 절.
SELECT month,
description,
total_paid
FROM
(SELECT DATE_PART('month', invoicedate) AS month,
description,
SUM(unitprice * quantity) AS total_paid,
RANK() OVER (PARTITION BY DATE_PART('month', invoicedate) ORDER BY SUM(unitprice * quantity) DESC) AS rnk
FROM online_retail
GROUP BY month, description) AS tmp
WHERE rnk = 1;틀 결론
우리가 다룬 6 가지 개념은 일반적으로 SQL 코딩 인터뷰 질문에 나타납니다. 그들에게주의를 기울이고, 이러한 개념과 관련된 인터뷰 질문을 연습하고, 올바른 접근법을 배우고, 인터뷰에서 기회를 크게 향상시킬 것입니다.
네이트 로시디 데이터 과학자이자 제품 전략입니다. 그는 또한 겸임 교수 교육 분석이며, 데이터 과학자들이 최고 회사의 실제 인터뷰 질문과의 인터뷰를 준비하는 플랫폼 인 Stratascratch의 창립자입니다. Nate는 커리어 시장의 최신 트렌드에 대해 글을 쓰고, 인터뷰 조언을 제공하고, 데이터 과학 프로젝트를 공유하며, 모든 SQL을 다룹니다.
Post Comment