Valmistautuminen ohjelmointihaastatteluihin · Oppitunti

Monivaiheisen suppilon rakentaminen

Kunkin järjestyksessä olevan vaiheen saavuttaneiden käyttäjien laskeminen ja vaihekohtaisten konversioasteiden määrittäminen.

Oppitunti 1/413 vaihetta

Monivaiheisen suppilon rakentaminen on ilmainen Valmistautuminen ohjelmointihaastatteluihin-oppitunti CoddyKitissä. Tämä on oppitunti 1/4. Voit lukea koko oppitunnin alta ilmaiseksi ja harjoitella sen jälkeen käytännössä selaimessa sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla. Oppitunti kuuluu Valmistautuminen ohjelmointihaastatteluihin-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. Valmistautuminen ohjelmointihaastatteluihin-kurssilla on yhteensä 4 oppituntia.

Mitä suppilokysymys todella testaa

Kun haastattelija pyytää "rakentamaan rekisteröitymissuppilon", hän testaa, pystyttekö laskemaan yksilölliset käyttäjät, jotka saavuttavat kunkin järjestyksessä olevan vaiheen, ja ilmaisemaan niiden välisen pudotuksen.

Suppilossa on vaiheita, kuten visit -> signup -> activate -> purchase. Tuloksena on yleensä yksi rivi kutakin vaihetta kohden sekä käyttäjämäärä ja konversioaste.

Laske käyttäjät, älä tapahtumia (jos yksi käyttäjä laukaisee tapahtuman kahdesti, kyseessä on silti yksi käyttäjä).
Vaiheet ovat järjestyksessä; vaiheeseen 3 pääseminen tarkoittaa, että vaiheet 1 ja 2 on läpäisty.

Annettava tapahtumataulu

Lähes jokaisessa suppilokysymyksessä annetaan yksi pitkän muodon events-taulu. Sen rakenne näyttää tältä:

user_id toiminnon suorittanut käyttäjä
event_name tapahtuman nimi, kuten 'visit', 'signup' tai 'purchase'
event_time aikaleima

Yksi rivi kutakin toimintoa kohden. Tehtävänä on muuntaa tämä vaihekohtaisiksi määriksi. Tapahtumien tarkat nimet kannattaa aina varmistaa haastattelijalta ennen SQL:n kirjoittamista.

CREATE TABLE events (
  user_id    INT,
  event_name VARCHAR(50),
  event_time TIMESTAMP
);

Käyttäjien laskeminen yhdessä vaiheessa

Aloita yksinkertaisesti: kuinka monta yksilöllistä käyttäjää saavutti yhden vaiheen? Käytä COUNT(DISTINCT user_id)-funktiota ja suodata tapahtuman nimen perusteella.

Tämä on jokaisen suppilon perusrakenne. Jos yhden vaiheen laskeminen onnistuu selkeästi, kaikkien vaiheiden laskeminen onnistuu samalla tavalla.

SELECT COUNT(DISTINCT user_id) AS users_who_signed_up
FROM events
WHERE event_name = 'signup';

Ehdollinen aggregointi kaikille vaiheille

Selkeä haastatteluvastaus laskee kaikki vaiheet yhdellä läpikäynnillä ehdollisen aggregoinnin avulla: CASE-lause sijoitetaan COUNT(DISTINCT ...)-funktion sisään.

Laske kunkin vaiheen kohdalla niiden yksilöllisten käyttäjien määrä, joiden tapahtuma vastaa kyseistä vaihetta. Yksi taulun läpikäynti tuottaa yhden rivin vaiheiden kokonaismäärille.

SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'visit'    THEN user_id END) AS step1_visit,
  COUNT(DISTINCT CASE WHEN event_name = 'signup'   THEN user_id END) AS step2_signup,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS step3_purchase
FROM events;

Piilovirhe: vaiheet eivät ole järjestyksessä

Juuri nähdyssä kyselyssä on sudenkuoppa, josta haastattelijat pitävät. Se laskee kaikki 'purchase'-tapahtuman laukaisseet, vaikka he eivät olisi koskaan käyneet sivulla tai rekisteröityneet datan perusteella.

Oikeassa suppilossa jokaisen myöhemmän vaiheen on oltava aiemman vaiheen osajoukko. Tapahtumien itsenäinen laskeminen voi tuottaa tilanteen, jossa vaiheessa 3 on enemmän käyttäjiä kuin vaiheessa 2, mikä on suppilon kannalta loogisesti mahdotonta.

Korjaus on sitoa käyttäjän vaiheet yhteen, yleensä tiivistämällä tiedot ensin yhdeksi riviksi käyttäjää kohden.

Yksi rivi käyttäjää kohden lippujen avulla

Luotettava malli on tiivistää tapahtumaloki yhdeksi riviksi käyttäjää kohden ja lisätä jokaiselle vaiheelle totuusarvolippu (0/1), joka kertoo, suorittiko käyttäjä kyseisen vaiheen koskaan. MAX(CASE ...) muuntaa pitkän lokin leveäksi käyttäjäkohtaiseksi yhteenvedoksi.

WITH user_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS did_visit,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS did_signup,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
  FROM events
  GROUP BY user_id
)
SELECT * FROM user_steps;

Vaiheiden järjestyksen varmistaminen

Varmista nyt suppilon sääntö: käyttäjä lasketaan mukaan vaiheeseen N vain, jos hän suoritti myös kaikki sitä edeltävät vaiheet. 'Purchase'-vaiheeseen pääseminen merkitsee jotain vain, jos käyttäjä myös kävi sivulla ja rekisteröityi.

Laske liput yhteen ja yhdistä tarvittavat esiehdot AND-operaatiolla, jotta jokainen vaihe on aidosti edellisen vaiheen osajoukko.

WITH user_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS did_visit,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS did_signup,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
  FROM events
  GROUP BY user_id
)
SELECT
  SUM(did_visit)                                       AS step1_visit,
  SUM(CASE WHEN did_visit = 1 AND did_signup = 1 THEN 1 ELSE 0 END)                       AS step2_signup,
  SUM(CASE WHEN did_visit = 1 AND did_signup = 1 AND did_purchase = 1 THEN 1 ELSE 0 END)  AS step3_purchase
FROM user_steps;

Määrien muuntaminen siistiksi pitkäksi tulokseksi

Haastattelijat suosivat usein yhtä riviä vaihetta kohden yhden leveän rivin sijaan. Muunna leveät kokonaismäärät pitkään muotoon pienellä UNION ALL -kyselyiden yhdistelmällä ja liitä mukaan järjestystä varten vaihenumero.

Tämä helpottaa huomattavasti konversioasteiden laskemista ja kaavioiden laatimista seuraavassa vaiheessa.

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT step_no, step_name, users
FROM funnel
ORDER BY step_no;

Vaiheesta toiseen -konversioaste

Kaksi konversioastetta on tärkeää erottaa toisistaan, ja haastattelija kysyy, kumpaa tarkoitatte:

Vaihekohtainen konversio: tässä vaiheessa olevien käyttäjien määrä jaettuna edellisessä vaiheessa olevien käyttäjien määrällä.
Kokonaiskonversio: tässä vaiheessa olevien käyttäjien määrä jaettuna suppilon ensimmäisen vaiheen käyttäjämäärällä.

Hae LAG-funktiolla edellisen vaiheen määrä vaiheesta toiseen laskettavaa konversioastetta varten. Muunna arvo desimaaliksi, jotta kokonaislukujako ei vääristä tulosta.

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT
  step_name,
  users,
  ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_conv_pct
FROM funnel
ORDER BY step_no;

Kokonaiskonversio aloitusvaiheesta

Kokonaiskonversiota varten jaa jokaisen vaiheen määrä suppilon ensimmäisen vaiheen määrällä. FIRST_VALUE kiinnittää järjestetyn suppilon ensimmäisen arvon jokaiselle riville.

Mainitse haastattelijalle aina, että estit kokonaislukujakoa kertomalla arvon luvulla 100.0.

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT
  step_name,
  users,
  ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step_no), 1) AS overall_pct
FROM funnel
ORDER BY step_no;

Koko suppilon kokoaminen

Tässä on päästä päähän -ratkaisu, jota haastattelijat odottavat: tiivistä tiedot käyttäjäkohtaisiksi lipuiksi, varmista vaiheiden järjestys, muunna tulos pitkään muotoon ja laske lopuksi molemmat konversioasteet. Käy ratkaisu ääneen läpi ja kerro samalla kunkin CTE:n tarkoitus.

Rakenne skaalautuu: uuden vaiheen lisääminen edellyttää yhden lipun ja yhden UNION ALL -rivin lisäämistä.

WITH user_steps AS (
  SELECT user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS s1,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS s2,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS s3
  FROM events GROUP BY user_id
),
totals AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, SUM(s1) AS users FROM user_steps UNION ALL
  SELECT 2, 'signup',   SUM(CASE WHEN s1=1 AND s2=1 THEN 1 ELSE 0 END) FROM user_steps UNION ALL
  SELECT 3, 'purchase', SUM(CASE WHEN s1=1 AND s2=1 AND s3=1 THEN 1 ELSE 0 END) FROM user_steps
)
SELECT step_name, users,
  ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_pct
FROM totals ORDER BY step_no;

Pikatarkistus

Haastattelija huomaa, että suppilossa on 95 käyttäjää 'purchase'-vaiheessa mutta vain 80 'signup'-vaiheessa. Mikä on todennäköisin syy?

Vastaa interaktiivisessa oppitunnissa

Kertaus: monivaiheiset suppilot

Nyt osaat rakentaa suppilon haastatteluissa odotetulla tavalla:

Laske yksilölliset käyttäjät jokaisessa järjestyksessä olevassa vaiheessa, älä koskaan raakatapahtumia.
Tiivistä tapahtumaloki yhdeksi riviksi käyttäjää kohden ja käytä MAX(CASE ...)-lippuja.
Varmista järjestys, jotta jokainen vaihe on edellisen vaiheen osajoukko.
Laske vaiheesta toiseen tapahtuva konversio (LAG) ja kokonaiskonversio (FIRST_VALUE) sekä estä kokonaislukujako.

Seuraavaksi varmistetaan, että vaiheet todella tapahtuivat oikeassa järjestyksessä ja tietyn aikaikkunan sisällä.

Aloita maksutta

Opi Valmistautuminen ohjelmointihaastatteluihin tekoälytuutorin avulla — ilmaiseksi

Kirjoita ja suorita oikeaa koodia selaimessa, saa välitöntä apua tekoälytuutorilta ympäri vuorokauden ja jatka siitä, mihin jäit, verkossa tai sovelluksessa.

Kurssit: 90
Oppitunnit: 360

Aloita opiskelu maksutta Näytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”Monivaiheisen suppilon rakentaminen” ilmainen?

Kyllä – oppitunnin ”Monivaiheisen suppilon rakentaminen” koko tekstin voi lukea täällä verkossa ilmaiseksi. Jos haluat harjoitella interaktiivisesti sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla sekä avata koko Valmistautuminen ohjelmointihaastatteluihin-kurssin, päivitä CoddyKit PROhon. Valmistautuminen ohjelmointihaastatteluihin-kurssilla on yhteensä 4 oppituntia.

Mitä opin oppitunnilla ”Monivaiheisen suppilon rakentaminen”?

Kunkin järjestyksessä olevan vaiheen saavuttaneiden käyttäjien laskeminen ja vaihekohtaisten konversioasteiden määrittäminen. Harjoittelet Valmistautuminen ohjelmointihaastatteluihin-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.

Tarvitsenko kokemusta aloittaakseni Valmistautuminen ohjelmointihaastatteluihin-opiskelun?

Aiempi kokemus ei ole tarpeen. CoddyKitin Valmistautuminen ohjelmointihaastatteluihin-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 1/4.

Kuinka kauan ”Monivaiheisen suppilon rakentaminen”-oppitunnin suorittaminen kestää?

Useimmat CoddyKitin oppitunnit kestävät noin 5–10 minuuttia. Jokainen oppitunti on lyhyt ja interaktiivinen, joten edistyt tasaisesti ja voit jatkaa siitä, mihin jäit – sekä verkossa että sovelluksessa.

Voinko kirjoittaa ja suorittaa koodia tällä Valmistautuminen ohjelmointihaastatteluihin-oppitunnilla?

Kyllä. Jokainen Valmistautuminen ohjelmointihaastatteluihin-oppitunti sisältää sisäänrakennetun koodieditorin, joten voit kirjoittaa ja suorittaa oikeaa koodia suoraan selaimessa ja saada välitöntä palautetta tekoälyltä – paikallista asennusta ei tarvita.

Kaikki tämän kurssin oppitunnit

Monivaiheisen suppilon rakentaminen
Järjestetyt tapahtumat ja aikaikkunat
A/B-testin jako ja mittarit
Lift, merkitsevyys ja suojarajat SQL:ssä

← Takaisin: Valmistautuminen ohjelmointihaastatteluihin