Prime-SQL-Muster aus FAANG Information Science-Interviews (mit Code)

Bild vom Autor

# Einführung

Das technische Screening für Information-Science-Rollen in FAANG-Unternehmen ist sehr gründlich. Allerdings können selbst sie nicht mit einem endlosen Strom einzigartiger Interviewfragen aufwarten. Sobald Sie die Arbeit oft genug durchgearbeitet haben, bemerken Sie, dass immer wieder einige SQL-Muster auftauchen.

Hier sind die Prime 5, mit Beispielen und Code (PostgreSQL) zum Üben.

Bild vom Autor | Servietten-KI

Wenn Sie diese beherrschen, sind Sie für die meisten SQL-Interviews gerüstet.

# Muster Nr. 1: Daten mit GROUP BY aggregieren

Aggregatfunktionen verwenden mit GROUP BY ermöglicht es Ihnen, Metriken über Kategorien hinweg zu aggregieren.

Dieses Muster wird häufig mit der Datenfilterung kombiniert, was bedeutet, dass eine der beiden Klauseln verwendet wird:

• WHERE: Filtert Daten vor der Aggregation.
• HAVING: Filtert Daten nach der Aggregation.

Beispiel: Dies Meta-Interviewfrage fordert Sie auf, die Gesamtzahl der Kommentare zu ermitteln, die 30 oder weniger Tage vor dem 10.02.2020 professional Benutzer abgegeben wurden. Benutzer ohne Kommentare sollten von der Ausgabe ausgeschlossen werden.

Wir nutzen die SUM() Funktion mit a GROUP BY -Klausel, um die Anzahl der Kommentare professional Benutzer zu summieren. Die Ausgabe der Kommentare nur innerhalb des angegebenen Zeitraums wird dadurch erreicht, dass die Daten vor der Aggregation gefiltert, additionally verwendet werden WHERE. Es besteht keine Notwendigkeit zu berechnen, welches Datum „30 Tage vor dem 10.02.2020“ ist; Wir subtrahieren einfach 30 Tage von diesem Datum, indem wir verwenden INTERVAL Datumsfunktion.

SELECT user_id,
       SUM(number_of_comments) AS number_of_comments
FROM fb_comments_count
WHERE created_at BETWEEN '2020-02-10'::DATE - 30 * INTERVAL '1 day' AND '2020-02-10'::DATE
GROUP BY user_id;

Hier ist die Ausgabe.

Geschäftliche Nutzung:

• Kennzahlen zur Benutzeraktivität: DAU & MAU, Abwanderungsrate.
• Umsatzkennzahlen: Umsatz professional Area/Produkt/Zeitraum.
• Benutzerinteraktion: durchschnittliche Sitzungsdauer, durchschnittliche Klicks professional Benutzer.

# Muster Nr. 2: Filtern mit Unterabfragen

Wenn Sie Unterabfragen zum Filtern verwenden, erstellen Sie eine Datenteilmenge und filtern dann die Hauptabfrage danach.

Die beiden wichtigsten Unterabfragetypen sind:

• Skalare Unterabfragen: Geben einen einzelnen Wert zurück, z. B. den Höchstbetrag.
• Korrelierte Unterabfragen: Verweisen Sie auf das Ergebnis der äußeren Abfrage und hängen Sie davon ab, um die Werte zurückzugeben.

Beispiel: Dies Interviewfrage von Meta fordert Sie auf, ein Empfehlungssystem für Fb zu erstellen. Für jeden Benutzer sollten Sie Seiten finden, denen dieser Benutzer nicht folgt, aber mindestens einer seiner Freunde. Die Ausgabe sollte aus der Benutzer-ID und der ID der Seite bestehen, die diesem Benutzer empfohlen werden soll.

Die äußere Abfrage gibt alle Benutzer-Seiten-Paare zurück, bei denen der Seite mindestens ein Freund folgt.

Dann verwenden wir eine Unterabfrage im WHERE -Klausel, um die Seiten zu entfernen, denen der Benutzer bereits folgt. In der Unterabfrage gibt es zwei Bedingungen: eine, die nur Seiten berücksichtigt, denen dieser bestimmte Benutzer folgt (Prüfung nur für diesen Benutzer), und dann prüft, ob die zur Empfehlung in Betracht gezogene Seite zu den Seiten gehört, denen der Benutzer folgt (Prüfung nur für diese Seite).

Da die Unterabfrage alle Seiten zurückgibt, denen der Benutzer folgt, verwendet er NOT EXISTS In WHERE schließt alle diese Seiten von der Empfehlung aus.

SELECT DISTINCT f.user_id,
                p.page_id
FROM users_friends f
JOIN users_pages p ON f.friend_id = p.user_id
WHERE NOT EXISTS
    (SELECT *
     FROM users_pages pg
     WHERE pg.user_id = f.user_id
       AND pg.page_id = p.page_id);

Hier ist die Ausgabe.

Geschäftliche Nutzung:

• Kundenaktivität: letzte Anmeldung professional Benutzer, letzte Abonnementänderung.
• Verkäufe: höchste Bestellung professional Kunde, höchste Umsatzbestellung professional Area.
• Produktleistung: meistgekauftes Produkt in jeder Kategorie, Produkt mit dem höchsten Umsatz professional Monat.
• Nutzerverhalten: Längste Sitzung professional Nutzer, erster Kauf professional Kunde.
• Bewertungen und Suggestions: Prime-Rezensent, neueste Bewertung für jedes Produkt.
• Betrieb: Aktueller Versandstatus professional Bestellung, schnellste Lieferzeit professional Area.

# Muster Nr. 3: Rating mit Fensterfunktionen

Mithilfe von Fensterfunktionen wie z ROW_NUMBER(), RANK()Und DENSE_RANK() ermöglicht es Ihnen, Zeilen innerhalb von Datenpartitionen zu ordnen und dann die erste, zweite oder zu identifizieren nth aufzeichnen.

Hier ist, was jedes dieser Rating-Fenster zeigt Funktionen tut:

• ROW_NUMBER(): Weist innerhalb jeder Partition eine eindeutige fortlaufende Nummer zu; Gleiche Werte erhalten unterschiedliche Zeilennummern.
• RANK(): Weist gebundenen Werten den gleichen Rang zu und überspringt die nächsten Ränge für den nächsten nicht gebundenen Wert.
• DENSE_RANK(): Das Gleiche wie RANK()nur wird der Rang nach Unentschieden nicht übersprungen.

Beispiel: In einem Amazon-Interviewfragemüssen wir die höchsten täglichen Bestellkosten zwischen dem 01.02.2019 und dem 01.05.2019 ermitteln. Wenn ein Kunde an einem bestimmten Tag mehr als eine Bestellung hat, addieren Sie die Bestellkosten tagesaktuell. Die Ausgabe sollte den Vornamen des Kunden, die Gesamtkosten seiner Bestellung(en) und das Datum der Bestellung enthalten.

Im ersten gemeinsamen Tabellenausdruck (CTE) finden wir die Bestellungen zwischen den angegebenen Daten und summieren die Tagessummen des Kunden für jedes Datum.

Im zweiten CTE verwenden wir RANK() um Kunden nach Bestellkosten für jedes Datum absteigend zu ordnen.

Jetzt verbinden wir zwei CTEs, um die erforderlichen Spalten auszugeben und nur die Aufträge zu filtern, denen der erste Rang, additionally die höchste Ordnung, zugewiesen ist.

WITH customer_daily_totals AS (
  SELECT o.cust_id,
         o.order_date,
         SUM(o.total_order_cost) AS total_daily_cost
  FROM orders o
  WHERE o.order_date BETWEEN '2019-02-01' AND '2019-05-01'
  GROUP BY o.cust_id, o.order_date
),

ranked_daily_totals AS (
  SELECT cust_id,
         order_date,
         total_daily_cost,
         RANK() OVER (PARTITION BY order_date ORDER BY total_daily_cost DESC) AS rnk
  FROM customer_daily_totals
)

SELECT c.first_name,
       rdt.order_date,
       rdt.total_daily_cost AS max_cost
FROM ranked_daily_totals rdt
JOIN clients c ON rdt.cust_id = c.id
WHERE rdt.rnk = 1
ORDER BY rdt.order_date;

Hier ist die Ausgabe.

Geschäftliche Nutzung:

• Benutzeraktivität: „Prime 5 der aktivsten Benutzer im letzten Monat“.
• Umsatz: „Die zweithöchste Umsatzregion“.
• Produktpopularität: „Prime 10 der meistverkauften Produkte“.
• Einkäufe „Der erste Einkauf jedes Kunden“.

# Muster Nr. 4: Berechnen von gleitenden Durchschnitten und kumulierten Summen

Der gleitende (gleitende) Durchschnitt berechnet den Durchschnitt der letzten N Zeilen, normalerweise Monate oder Tage. Es wird anhand der berechnet AVG() Fensterfunktion und Definieren des Fensters als ROWS BETWEEN N PRECEDING AND CURRENT ROW.

Die kumulative Summe (laufende Summe) ist die Summe von der ersten Zeile bis zur aktuellen Zeile, die sich in der Definition des Fensters widerspiegelt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW im SUM() Fensterfunktion.

Beispiel: Die Interviewfrage von Amazon möchte, dass wir den gleitenden 3-Monats-Durchschnitt des Gesamtumsatzes aus Käufen ermitteln. Wir sollten das Jahr-Monat (JJJJ-MM) und den gleitenden 3-Monats-Durchschnitt ausgeben, sortiert vom frühesten zum neuesten Monat.

Auch die Retouren (destructive Einkaufswerte) sollten nicht berücksichtigt werden.

Wir verwenden eine Unterabfrage, um den monatlichen Umsatz zu berechnen SUM() und konvertieren Sie das Kaufdatum mit in ein JJJJ-MM-Format TO_CHAR() Funktion.

Dann verwenden wir AVG() um den gleitenden Durchschnitt zu berechnen. Im OVER() -Klausel ordnen wir die Daten in Partitionen nach Monat und definieren das Fenster als ROWS BETWEEN 2 PRECEDING AND CURRENT ROW; Wir berechnen den gleitenden 3-Monats-Durchschnitt, der den aktuellen und die beiden vorangegangenen Monate berücksichtigt.

SELECT t.month,
       AVG(t.monthly_revenue) OVER(ORDER BY t.month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_revenue
FROM
  (SELECT TO_CHAR(created_at::DATE, 'YYYY-MM') AS month,
          SUM(purchase_amt) AS monthly_revenue
   FROM amazon_purchases
   WHERE purchase_amt > 0
   GROUP BY 1
   ORDER BY 1) AS t
ORDER BY t.month ASC;

Hier ist die Ausgabe.

Um eine kumulative Summe zu berechnen, würden wir es so machen.

SELECT t.month,
       SUM(t.monthly_revenue) OVER(ORDER BY t.month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sum
FROM
  (SELECT TO_CHAR(created_at::DATE, 'YYYY-MM') AS month,
          SUM(purchase_amt) AS monthly_revenue
   FROM amazon_purchases
   WHERE purchase_amt > 0
   GROUP BY 1
   ORDER BY 1) AS t
ORDER BY t.month ASC;

Hier ist die Ausgabe.

Geschäftliche Nutzung:

• Engagement-Metriken: gleitender 7-Tage-Durchschnitt der DAU oder gesendeten Nachrichten, kumulative Stornierungen.
• Finanzielle KPIs: gleitender 30-Tage-Durchschnitt von Kosten/Umsätzen/Aktienkursen, Umsatzberichte (kumuliert seit Jahresbeginn).
• Produktleistung: gleitender Durchschnitt der Anmeldungen professional Benutzer, kumulierte App-Installationen.
• Vorgänge: Kumulierte Bestellungen versandt, Tickets gelöst, Fehler behoben.

# Muster Nr. 5: Anwenden bedingter Aggregationen

Mit der bedingten Aggregation können Sie mehrere segmentierte Metriken in einem Durchgang berechnen, indem Sie Folgendes eingeben CASE WHEN-Anweisung innerhalb von Aggregatfunktionen.

Beispiel: A Frage aus einem Amazon-Interview fordert Sie auf, wiederkehrende aktive Benutzer zu identifizieren, indem Sie Benutzer finden, die innerhalb von 1 bis 7 Tagen nach ihrem ersten Kauf einen zweiten Kauf getätigt haben. Die Ausgabe sollte nur aus den IDs dieser Benutzer bestehen. Die Einkäufe am selben Tag sollten ignoriert werden.

Der erste CTE identifiziert die Benutzer und die Daten ihrer Käufe, wobei Käufe am selben Tag ausgeschlossen sind DISTINCT Stichwort.

Der zweite CTE ordnet die Kaufdaten jedes Benutzers vom ältesten zum neuesten.

Der letzte CTE ermittelt mithilfe der bedingten Aggregation die ersten und zweiten Käufe für jeden Benutzer. Wir verwenden MAX() um den einzelnen Wert ungleich NULL für das erste und zweite Kaufdatum auszuwählen.

Schließlich verwenden wir das Ergebnis des letzten CTE und behalten nur Benutzer bei, die innerhalb von 7 Tagen nach ihrem ersten Kauf einen zweiten Kauf (nicht NULL) getätigt haben.

WITH each day AS (
  SELECT DISTINCT user_id,
         created_at::DATE AS purchase_date
  FROM amazon_transactions
),

ranked AS (
  SELECT user_id,
         purchase_date,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date) AS rn
  FROM each day
),

first_two AS (
  SELECT user_id,
         MAX(CASE WHEN rn = 1 THEN purchase_date END) AS first_date,
         MAX(CASE WHEN rn = 2 THEN purchase_date END) AS second_date
  FROM ranked
  WHERE rn <= 2
  GROUP BY user_id
)

SELECT user_id
FROM first_two
WHERE second_date IS NOT NULL AND (second_date - first_date) BETWEEN 1 AND 7
ORDER BY user_id;

Hier ist die Ausgabe.

Geschäftliche Nutzung:

• Abonnementberichte: bezahlte vs. kostenlose Benutzer, aktive vs. abgewanderte Benutzer nach Planstufe.
• Advertising-Trichter-Dashboards: angemeldete vs. gekaufte Benutzer nach Visitors-Quelle, geöffnete E-Mails vs. angeklickte vs. konvertierte E-Mails.
• E-Commerce: abgeschlossene vs. erstattete vs. stornierte Bestellungen nach Area, neue vs. wiederkehrende Käufer.
• Produktanalyse: iOS vs. Android vs. Webnutzung, übernommene vs. nicht übernommene Funktionen professional Kohorte.
• Finanzen: Umsatz mit Neukunden vs. Bestandskunden, Bruttoumsatz vs. Nettoumsatz.
• A/B-Checks und Experimente: Kontroll- vs. Behandlungsmetriken.

# Abschluss

Wenn Sie einen Job bei FAANG (und auch anderen) Unternehmen suchen, konzentrieren Sie sich bei Vorstellungsgesprächen auf diese fünf SQL-Muster. Natürlich sind dies nicht die einzigen getesteten SQL-Konzepte. Sie werden jedoch am häufigsten getestet. Indem Sie sich darauf konzentrieren, stellen Sie sicher, dass Ihre Interviewvorbereitung für die meisten SQL-Interviews bei FAANG-Unternehmen so effizient wie möglich ist.

Nate Rosidi ist Datenwissenschaftler und in der Produktstrategie tätig. Er ist außerdem außerordentlicher Professor für Analytik und Gründer von StrataScratch, einer Plattform, die Datenwissenschaftlern hilft, sich mit echten Interviewfragen von Prime-Unternehmen auf ihre Interviews vorzubereiten. Nate schreibt über die neuesten Traits auf dem Karrieremarkt, gibt Ratschläge zu Vorstellungsgesprächen, stellt Information-Science-Projekte vor und behandelt alles rund um SQL.