Bild von Autor | Leinwand
Die Aufgabe eines Interviewers ist es, die am besten geeigneten Kandidaten für die beworbene Place zu finden. Dabei stellen sie gerne Fragen zu SQL -Interviews, um zu sehen, ob sie Sie überrascht. Es gibt mehrere SQL -Konzepte, an denen Kandidaten häufig scheitern.
Hoffentlich sind Sie einer von denen, die dieses Schicksal vermeiden, da ich diese Konzepte im Folgenden im Element erläutern werde, um Beispiele für die korrekte Lösung bestimmter Probleme.
# 1. Fensterfunktionen
Warum es schwierig ist: Kandidaten merken sich, was jeder Fensterfunktion Versteht aber nicht wirklich, wie Fensterrahmen, Partitionen oder Bestellung tatsächlich funktionieren.
Häufige Fehler: Ein häufiger Fehler ist nicht ORDER BY in Rating -Fensterfunktionen oder Wertungsfensterfunktionen wie z. LEAD() oder LAG()und erwarten, dass die Anfrage funktioniert oder das Ergebnis deterministisch ist.
Beispiel: in Dieses Beispiel, Sie müssen Benutzer finden, die innerhalb von 7 Tagen nach früheren Kauf einen zweiten Kauf getätigt haben.
Sie können diese Frage schreiben.
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 <= 7;Auf den ersten Blick magazine alles richtig erscheinen. Der Code gibt sogar etwas aus, das möglicherweise eine korrekte Antwort ist.
Zunächst haben wir Glück, dass der Code überhaupt funktioniert! Dies geschieht einfach, weil ich es einschreibe PostgreSQL. In einigen anderen SQL -Geschmacksrichtungen erhalten Sie seitdem einen Fehler ORDER BY ist obligatorisch bei Rating- und Analysefensterfunktionen.
Zweitens ist die Ausgabe falsch; Ich habe einige Reihen hervorgehoben, die nicht da sein sollten. Warum erscheinen sie dann?
Sie erscheinen, weil wir keine angegeben haben ORDER BY Klausel in der LAG() Fensterfunktion. Ohne sie ist die Zeilenreihenfolge willkürlich. Wir vergleichen additionally die aktuelle Transaktion mit einer zufälligen früheren Zeile für diesen Benutzer, nicht mit dem, der unmittelbar vor der Zeit vor ihr auftrat.
Dies ist nicht das, was die Frage stellt. Wir müssen jede Transaktion mit der vorherigen nach Datum vergleichen. Mit anderen Worten müssen wir dies explizit in der angeben ORDER BY Klausel innerhalb der LAG() Funktion.
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 <= 7;# 2. Filterung mit Aggregaten (insbesondere gegen wo)
Warum es schwierig ist: Die Leute verstehen die Ausführungsreihenfolge in SQL oft nicht: nämlich: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. Diese Reihenfolge bedeutet das WHERE Filterreihen vor der Aggregation und HAVING Filter nach. Das bedeutet auch logischerweise, dass Sie keine Aggregatfunktionen in der verwenden können WHERE Klausel.
Häufiger Fehler: Versuch, Aggregatfunktionen in zu verwenden WHERE in einer gruppierten Abfrage und einen Fehler erhalten.
Beispiel: Diese Interviewfrage Bittet Sie, die Gesamtumsätze jedes Weinguts zu finden. Nur Weingüter, bei denen 90 die niedrigste Anzahl von Punkten für eine ihrer Sorten sind, sollten berücksichtigt werden.
Viele werden dies als einfache Frage ansehen und diese Frage hastig schreiben.
SELECT vineyard,
selection,
SUM(value) AS total_revenue
FROM winemag_p1
WHERE MIN(factors) >= 90
GROUP BY vineyard, selection
ORDER BY vineyard, total_revenue DESC;Dieser Code macht jedoch einen Fehler, der besagt, dass die Aggregatfunktionen in der nicht zulässig sind WHERE Klausel. Das erklärt so ziemlich alles. Die Lösung? Bewegen Sie die Filterbedingung aus WHERE Zu HAVING.
SELECT vineyard,
selection,
SUM(value) AS total_revenue
FROM winemag_p1
GROUP BY vineyard, selection
HAVING MIN(factors) >= 90
ORDER BY vineyard, total_revenue DESC;# 3..
Warum es schwer ist: die Idee von Einen Tisch mit sich selbst beitreten ist ziemlich unintuitiv, daher vergessen Kandidaten oft, dass es eine Possibility ist.
Häufiger Fehler: Die Verwendung von Unterabfragen und das Komplizieren der Abfrage beim Beitritt zu einer Tabelle mit sich selbst wäre einfacher und schneller, insbesondere beim Filtern durch Daten oder Ereignisse.
Beispiel: Hier ist a Frage Bitten Sie Sie, die Änderung der Wechselkurs jeder Währung zwischen dem 1. Januar 2020 und dem 1. Juli 2020 zu zeigen.
Sie können dies lösen, indem Sie eine äußere korrelierte Unterabfrage schreiben, die die Wechselkurse vom 1. Juli abreißt, und dann die Wechselkurse vom 1. Januar abziehen, die aus der inneren Unterabfrage stammen.
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 distinction
FROM (SELECT source_currency, exchange_rate
FROM sf_exchange_rate
WHERE date="2020-01-01"
) AS jan_rates;Dies gibt eine korrekte Ausgabe zurück, aber eine solche Lösung ist unnötig kompliziert. Eine viel einfachere Lösung mit weniger Codezeilen beinhaltet die Selbstversorgung einer Tabelle mit sich selbst und anschließend zwei Datumsfilterungsbedingungen in der WHERE Klausel.
SELECT jan.source_currency,
jul.exchange_rate - jan.exchange_rate AS distinction
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. Unterabfragen gegen gemeinsame Tabellenausdrücke (CTEs)
Warum es schwierig ist: Die Menschen stecken oft auf Unterabfragen fest, weil sie sie vor gemeinsamen Tischausdrücken (CTEs) lernen und sie weiterhin für eine Abfrage mit geschichteter Logik verwenden. Unterabfragen können jedoch sehr schnell chaotisch werden.
Häufiger Fehler: tief verschachtelte Verwendung SELECT Aussagen, wenn CTES viel einfacher wären.
Beispiel: In der Interviewfrage Von Google und Netflix müssen Sie die Prime -Schauspieler basierend auf der durchschnittlichen Filmbewertung in dem Style finden, in dem sie am häufigsten erscheinen.
Die Lösung mit CTES ist wie folgt.
WITH genre_stats AS
(SELECT actor_name,
style,
COUNT(*) AS movie_count,
AVG(movie_rating) AS avg_rating
FROM top_actors_rating
GROUP BY actor_name,
style),
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 <= 3)
SELECT actor_name,
style,
avg_rating
FROM ranked_actors
WHERE rank <=
(SELECT max_rank
FROM final_selection);
Es ist relativ kompliziert, besteht jedoch immer noch aus sechs klaren CTEs, wobei die Lesbarkeit des Codes durch klare Aliase verbessert wird.
Neugierig, wie die gleiche Lösung aussehen würde, nur bei Unterabfragen zu verwenden? Hier ist es.
SELECT ra.actor_name,
ra.style,
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,
style,
COUNT(*) AS movie_count,
AVG(movie_rating) AS avg_rating
FROM top_actors_rating
GROUP BY actor_name, style
) AS gs
JOIN (
SELECT actor_name,
MAX(movie_count) AS max_count
FROM (
SELECT actor_name,
style,
COUNT(*) AS movie_count,
AVG(movie_rating) AS avg_rating
FROM top_actors_rating
GROUP BY actor_name, style
) 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,
style,
COUNT(*) AS movie_count,
AVG(movie_rating) AS avg_rating
FROM top_actors_rating
GROUP BY actor_name, style
) AS gs
JOIN (
SELECT actor_name,
MAX(movie_count) AS max_count
FROM (
SELECT actor_name,
style,
COUNT(*) AS movie_count,
AVG(movie_rating) AS avg_rating
FROM top_actors_rating
GROUP BY actor_name, style
) 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 <= (
SELECT MAX(rank)
FROM (
SELECT *,
DENSE_RANK() OVER (ORDER BY avg_rating DESC) AS rank
FROM (
SELECT tg.*
FROM (
SELECT gs.*
FROM (
SELECT actor_name,
style,
COUNT(*) AS movie_count,
AVG(movie_rating) AS avg_rating
FROM top_actors_rating
GROUP BY actor_name, style
) AS gs
JOIN (
SELECT actor_name,
MAX(movie_count) AS max_count
FROM (
SELECT actor_name,
style,
COUNT(*) AS movie_count,
AVG(movie_rating) AS avg_rating
FROM top_actors_rating
GROUP BY actor_name, style
) 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,
style,
COUNT(*) AS movie_count,
AVG(movie_rating) AS avg_rating
FROM top_actors_rating
GROUP BY actor_name, style
) AS gs
JOIN (
SELECT actor_name,
MAX(movie_count) AS max_count
FROM (
SELECT actor_name,
style,
COUNT(*) AS movie_count,
AVG(movie_rating) AS avg_rating
FROM top_actors_rating
GROUP BY actor_name, style
) 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 ranked_actors
WHERE rank <= 3
);Es wird eine redundante Logik über Unterabfragen wiederholt. Wie viele Unterabfragen ist das? Ich habe keine Ahnung. Der Code ist unmöglich zu warten. Obwohl ich es gerade geschrieben habe, würde ich noch einen halben Tag brauchen, um es zu verstehen, wenn ich morgen etwas ändern wollte. Darüber hinaus helfen die völlig bedeutungslosen Unterabfrage -Aliase nicht.
# 5. Umgang mit Nulls in Logik
Warum es schwierig ist: Kandidaten denken oft das NULL ist gleich etwas. Es ist nicht. NULL ist nicht gleich etwas – nicht einmal selbst. Logik mit NULLS verhält sich unterschiedlich als die Logik, die tatsächliche Werte beinhaltet.
Häufiger Fehler: Verwenden = NULL anstatt IS NULL bei Filterung oder fehlenden Ausgabezeilen, weil NULLs Brechen Sie die Bedingungslogik.
Beispiel: Es gibt eine Interviewfrage von IBM Damit werden Sie aufgefordert, die Gesamtzahl der Interaktionen und die Gesamtzahl der für jeden Kunden erstellten Inhalte zu berechnen.
Es klingt nicht zu schwierig, daher können Sie diese Lösung mit zwei CTEs schreiben, wobei ein CTE die Anzahl der Interaktionen professional Kunden zählt, während die andere die Anzahl der von einem Kunden erstellten Inhaltselemente zählt. Im Finale SELECTDu FULL OUTER JOIN Die beiden Ctes, und Sie haben die Lösung. Rechts?
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;Quick richtig. Hier ist die Ausgabe. (Übrigens sehen Sie doppelte Anführungszeichen („“) anstelle von NULL. So zeigt die Stratascratch -Benutzeroberfläche sie an, aber vertrauen Sie mir, der Motor behandelt sie immer noch für das, was sie sind: NULL Werte).
Die hervorgehobenen Reihen enthalten NULLS. Dies macht die Ausgabe falsch. A NULL Wert ist weder die Kunden -ID noch die Anzahl der Interaktionen und Inhalte, die die Frage ausdrücklich auffordert.
Was uns in der obigen Lösung fehlt, ist COALESCE() zu handhaben NULLs im Finale SELECT. Jetzt erhalten alle Kunden ohne Interaktionen ihre IDs von der content_summary CTE. Auch für Kunden, die weder Interaktionen noch Inhalte haben, oder beides, werden wir jetzt ersetzen NULL mit 0, was eine gültige Nummer ist.
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. Gruppenbasierte Deduplizierung
Warum es schwierig ist: Gruppenbasierter Deduplizierung bedeutet, dass Sie eine Zeile professional Gruppe, z. B. „neulichste“, „höchste Punktzahl“ usw. auswählen. Zunächst hört sich es so an, als müssten Sie nur eine Zeile professional Benutzer auswählen. Aber du kannst nicht benutzen GROUP BY es sei denn, Sie aggregieren. Andererseits benötigen Sie oft eine vollständige Zeile, keinen einzigen Wert, den Aggregation und GROUP BY zurückkehren.
Häufiger Fehler: Verwenden GROUP BY + LIMIT 1 (oder Unterschiedlich aufwas postgresql-spezifisch ist) anstelle von ROW_NUMBER() oder RANK()Letzteres, wenn Sie Krawatten enthalten möchten.
Beispiel: Diese Frage Bitten Sie, den meistverkauften Artikel für jeden Monat zu identifizieren, und es besteht keine Notwendigkeit, Monate zu Jahr zu trennen. Der meistverkaufte Ingredient wird berechnet als unitprice * amount.
Der naive Ansatz wäre dies. Extrahieren Sie zuerst den Verkaufsmonat aus invoicedatewählen descriptionund finden Sie den Gesamtumsatz durch Summieren unitprice * amount. Dann, um den Gesamtumsatz nach Monat und Produktbeschreibung zu erhalten, haben wir einfach einfach GROUP BY diese beiden Spalten. Schließlich müssen wir nur verwenden ORDER BY Um die Ausgabe vom Besten zum schlechtesten Sendungsprodukt zu sortieren und zu verwenden LIMIT 1 Um nur die erste Zeile auszugeben, dh das meistverkaufte Ingredient.
SELECT DATE_PART('MONTH', invoicedate) AS sale_month,
description,
SUM(unitprice * amount) AS total_paid
FROM online_retail
GROUP BY sale_month, description
ORDER BY total_paid DESC
LIMIT 1;Wie gesagt, das ist naiv; Die Ausgabe ähnelt etwas, was wir brauchen, aber wir brauchen dies für jeden Monat, nicht nur für einen.
Einer der korrekten Ansätze ist die Verwendung der Verwendung RANK() Fensterfunktion. Mit diesem Ansatz folgen wir einer ähnlichen Methode wie im vorherigen Code. Der Unterschied besteht darin, dass die Abfrage jetzt zu einer Unterabfrage in der FROM Klausel. Außerdem verwenden wir RANK() Um die Daten nach Monat zu partitionieren und dann die Zeilen innerhalb jeder Partition (dh für jeden Monat separat) vom Bestseller bis zum schlimmsten Promoting-Ingredient zu bewerten.
In der Hauptabfrage wählen wir dann einfach die erforderlichen Spalten aus und geben nur Zeilen aus, an denen der Rang 1 verwendet wird WHERE Klausel.
SELECT month,
description,
total_paid
FROM
(SELECT DATE_PART('month', invoicedate) AS month,
description,
SUM(unitprice * amount) AS total_paid,
RANK() OVER (PARTITION BY DATE_PART('month', invoicedate) ORDER BY SUM(unitprice * amount) DESC) AS rnk
FROM online_retail
GROUP BY month, description) AS tmp
WHERE rnk = 1;# Abschluss
Die sechs Konzepte, die wir häufig in SQL -Coding -Interviewfragen behandelt haben. Achten Sie auf sie, praktizieren Sie Interviewfragen, die diese Konzepte beinhalten, die richtigen Ansätze lernen, und Sie werden Ihre Chancen in Ihren Interviews erheblich verbessern.
Nate Rosidi ist Datenwissenschaftler und in Produktstrategie. Er ist außerdem eine zusätzliche Professorin für Lehranalysen und Gründer von Stratascratch, einer Plattform, die Datenwissenschaftlern hilft, sich auf ihre Interviews mit echten Interviewfragen von Prime -Unternehmen vorzubereiten. Nate schreibt über die neuesten Traits auf dem Karrieremarkt, gibt Interviewberatung, teilt Datenwissenschaftsprojekte und deckt alles SQL ab.