FAANG interjú titkok. 5 SQL minta, amivel felvesznek

A technológiai óriások (Facebook, Amazon, Apple, Netflix, Google) felvételi eljárása híresen nehéz. Azonban a technikai interjúk kérdései gyakran ismétlődnek. Ha ezeket az alapvető SQL mintákat ismered és alkalmazni tudod, hatalmas előnyre teszel szert. Ebben a cikkben az öt leggyakoribb feladattípust mutatjuk be PostgreSQL környezetben. Másold ki a kódokat, és gyakorolj.

1. Adatok összesítése GROUP BY használatával

Ez az alapminta kategóriák szerinti metrikák összesítésére szolgál. Gyakran kombinálják időbeli szűréssel.

A feladat: Számoljuk meg felhasználónként a kommenteket, de csak a 2020-02-10 előtti 30 napban.

A megoldás kulcsa a WHERE záradék használata az aggregálás előtt.

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;

Mire használják az üzleti életben?

Napi és havi aktív felhasználók (DAU/MAU) mérése.
Bevételek régiónkénti vagy termékenkénti bontása.
Átlagos munkamenet-hossz vizsgálata.

2. Szűrés alkérdésekkel (Subqueries)

A „korrelált” vagy „skalár” alkérdésekkel bonyolultabb szűréseket végezhetünk.

A feladat: Ajánljunk olyan oldalakat egy felhasználónak, amit ő nem követ, de a barátai igen.

Itt a NOT EXISTS a varázsszó. Kizárjuk azokat az oldalakat, amelyeket a felhasználó már követ.

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);

Ez a logika alapvető minden ajánlórendszerben, legyen szó termékekről vagy ismerősökről.

3. Rangsorolás ablakfüggvényekkel

Az SQL minták közül talán ez a legfontosabb a haladó elemzésekhez. A ROW_NUMBER(), RANK() és DENSE_RANK() függvényekkel sorrendet állíthatunk fel csoportokon belül.

A feladat: Keressük meg a legmagasabb napi rendelési értéket és a vásárló nevét.

Két lépésben (CTE) oldjuk meg: először összesítünk, majd rangsorolunk.

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 customers c ON rdt.cust_id = c.id
WHERE rdt.rnk = 1
ORDER BY rdt.order_date;

A táblázatban látható a különbség a rangsoroló függvények között:

Függvény	Működés
ROW_NUMBER()	Egyedi sorszámot ad, döntetlen esetén is növekszik.
RANK()	Döntetlen esetén azonos rang, majd kihagyás (pl. 1, 1, 3).
DENSE_RANK()	Döntetlen esetén azonos rang, nincs kihagyás (pl. 1, 1, 2).

4. Mozgóátlag és kumulatív összeg

A pénzügyi elemzések elengedhetetlen eszköze.

A feladat: Számoljuk ki a bevételek 3 hónapos mozgóátlagát.

Az ablakfüggvényben a ROWS BETWEEN résszel határozzuk meg a vizsgált időszakot.

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;

Ha nem átlagot, hanem halmozott összeget (running total) szeretnénk, csak a definíciót kell módosítani:

SUM(t.monthly_revenue) OVER(ORDER BY t.month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

5. Feltételes összesítés

Ez a technika lehetővé teszi, hogy egyetlen lekérdezésben több különböző feltétel szerinti metrikát számoljunk. A CASE WHEN szerkezetet az aggregáló függvényen belülre tesszük.

A feladat: Keressük meg azokat a felhasználókat, akik az első vásárlásuk után 1-7 napon belül újra vásároltak.

Ez egy összetett példa, amely több CTE-t és feltételes logikát használ.

WITH daily 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 daily
),

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;

Üzleti felhasználás: