SQL-työhaastatteluun valmistautuminen · Oppitunti

Käyttäjän pisin putki

Kunkin ryhmän pisimmän peräkkäisen jakson laskeminen.

Oppitunti 2/413 vaihetta

Käyttäjän pisin putki on ilmainen SQL-työhaastatteluun valmistautuminen-oppitunti CoddyKitissä. Tämä on oppitunti 2/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 SQL-työhaastatteluun valmistautuminen-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. SQL-työhaastatteluun valmistautuminen-kurssilla on yhteensä 4 oppituntia.

Kysymys

Peräkkäisten päivien tunnistamisen yleinen jatkokysymys on: “Mikä on kunkin käyttäjän pisin peräkkäisten aktiivisten päivien jakso?” Tuote- ja kasvutiimit kysyvät tätä jatkuvasti sitoutumisen mittaamiseksi.

Osaatte jo tunnistaa jokaisen jakson. Uusi vaihe on löytää pisimmän jakson pituus kullekin käyttäjälle ja usein palauttaa myös kyseisen parhaan putken päivämäärät. Tämä oppitunti rakentuu suoraan aukkojen ja saarekkeiden rungon päälle.

Saarekkeiden muodostamisen kertaus

Edellisessä oppitunnissa ryhmittely tehtiin saarekkeen ankkurin login_date - ROW_NUMBER() avulla. Jokaisella käyttäjällä voi olla useita saarekkeita; laskemme ensin yhden rivin saareketta kohti ja tiivistämme tuloksen sen jälkeen yhdeksi riviksi käyttäjää kohti.

Pidä tämä kaksitasoinen suunnitelma mielessä: muodosta ensin saarekkeet ja kokoa saarekkeet vasta sen jälkeen.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
)
SELECT user_id, login_date - rn AS grp
FROM numbered;

Yksi rivi saareketta kohti

Tiivistä jokainen saareke yhdeksi yhteenvetoriviksi, joka sisältää sen pituuden ja päivämäärävälin. Ryhmittele käyttäjän ja ankkurin mukaan ja laske tarvittavat tunnusluvut.

Nimeämme tämän CTE:n islands, jotta seuraava taso voi lukea siitä selkeästi.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
)
SELECT * FROM islands;

Yksinkertainen vastaus: MAX-pituus

Jos haastattelija haluaa vain pituuden, viimeinen vaihe on yhden rivin ratkaisu: ryhmittele saarekkeet käyttäjän mukaan ja ota pituuden maksimiarvo.

Tämä on selkein vastaus, kun alku- ja loppupäivämääriä ei tarvita.

-- ...numbered and islands CTEs as before...
SELECT
  user_id,
  MAX(streak_len) AS longest_streak
FROM islands
GROUP BY user_id
ORDER BY user_id;

Myös päivämäärien palauttaminen

Usein haastattelija lisää kysymyksen: “ja näytä, milloin kyseinen putki esiintyi.” Pelkkä MAX ei kerro, mikä saareke voitti. Saarekkeet täytyy sijoittaa kunkin käyttäjän sisällä ja säilyttää sijalla 1 oleva.

Käytä ROW_NUMBER-funktiota ja järjestä saarekkeet pituuden mukaan laskevasti, jotta kunkin käyttäjän paras putki saa sijan 1. Lisää tasatilanteen ratkaiseva lisäehto, jotta tulos on yksikäsitteinen.

ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC, streak_start ASC
) AS rnk

Sijoitus ja suodatus

Kun sijoitus on laskettu, kääri se CTE:hen ja suodata sitten ehtoihin rnk = 1. Ikkunafunktioon ei voi suoraan viitata WHERE-lauseessa, joten ylimääräinen taso on välttämätön.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1;

RANK vai ROW_NUMBER tasatilanteissa

Entä jos käyttäjällä on kaksi yhtä pitkää pisintä putkea ja haastattelija haluaa palauttaa molemmat? Vaihda ROW_NUMBER funktioon RANK ja säilytä ehto rnk = 1.

ROW_NUMBER — täsmälleen yksi voittaja käyttäjää kohti (tasatilanteessa mielivaltainen, ellet lisää tasatilanteen ratkaisevaa lisäehtoa).
RANK — kaikki pisimmän putken jakavat saavat sijan 1 ja säilytetään.

Varmistakaa, kumpaa toimintaa haastattelija haluaa; tämä osoittaa, että huomioitte reunatapaukset.

RANK() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC
) AS rnk  -- keep all rnk = 1

Esimerkki

Oletetaan, että käyttäjä 7 kirjautui 1.–4. tammikuuta, sitten 10.–11. tammikuuta ja lopuksi 20.–23. tammikuuta. Saarekkeiden pituudet ovat 4, 2 ja 4. Pisin pituus on 4, ja pisimmistä jaksoista syntyy tasatilanne.

Kun käytetään yhdistelmää ROW_NUMBER + tasatilanteen ratkaiseva lisäehto streak_start, palautetaan vain jakso 1.–4. tammikuuta.
Kun käytetään RANK-funktiota, palautetaan sekä jakso 1.–4. tammikuuta että jakso 20.–23. tammikuuta.

Kun sanotte tämän ääneen, osoitatte päätelleenne myös duplikaattitilanteiden vaikutuksen.

Kirjautumattomien käyttäjien käsittely

Haastattelija saattaa kysyä: "Entä käyttäjät, jotka eivät ole koskaan kirjautuneet sisään?" Näillä käyttäjillä ei ole rivejä logins-taulussa, joten he katoavat tuloksista. Jos heidän on näyttävä tuloksissa putken pituudella 0, liittäkää koko users-taulu mukaan LEFT JOIN -liitoksella ja käyttäkää COALESCE-funktiota.

SELECT u.user_id,
  COALESCE(MAX(i.streak_len), 0) AS longest_streak
FROM users u
LEFT JOIN islands i ON i.user_id = u.user_id
GROUP BY u.user_id;

Suorituskykyä koskevia huomioita

Tämä malli tekee datasta yhden järjestetyn läpikäynnin sekä ryhmittelyn. Jotta suorituskyky säilyy hyvänä:

Varmistakaa indeksi muodossa (user_id, login_date), jotta ikkunan ORDER BY välttää lajittelun.
Poistakaa duplikaatit ajoissa, jos lähteessä on useita tapahtumia päivässä.
Välttäkää login_date-sarakkeen käärimistä funktioihin ORDER BY-lausekkeessa, sillä se voi estää indeksin käytön.

Erittäin suurilla tauluilla tämä on selvästi tehokkaampi kuin mikään itseliitokseen perustuva lähestymistapa.

Täydellinen haastatteluvastaus

Tässä on täydellinen ja viimeistelty kysely, joka palauttaa kunkin käyttäjän pisimmän putken päivämäärineen — tämä versio kannattaa kirjoittaa valkotaululle.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1
ORDER BY user_id;

Pikatarkistus

Valitkaa vaatimukseen sopiva työkalu.

Vastaa interaktiivisessa oppitunnissa

Kertaus

Kun lasketaan kunkin käyttäjän pisin putki:

Muodostakaa saarekkeet käyttämällä ankkurina lauseketta login_date - ROW_NUMBER().
Tiivistäkää jokainen saareke pituudeksi ja päivämääräväliksi.
Kun tarvitaan vain pituus, käyttäkää käyttäjäkohtaisesti ryhmiteltyä lauseketta MAX(streak_len).
Kun tarvitaan myös päivämäärät, järjestäkää saarekkeet käyttäjäkohtaisesti ja säilyttäkää sijalla 1 oleva saareke — käyttäkää RANK-funktiota tasatulosten sisällyttämiseen ja ROW_NUMBER-funktiota yhden voittajan valitsemiseen.
Käyttäkää users-taulun LEFT JOIN -liitosta, jotta myös käyttäjät, joiden putken pituus on 0, tulevat mukaan.

Seuraavaksi: ehdon täyttävien N peräkkäisen rivin tunnistaminen.

Aloita maksutta

Opi SQL 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: 30
Oppitunnit: 120

Aloita opiskelu maksutta Näytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”Käyttäjän pisin putki” ilmainen?

Kyllä – oppitunnin ”Käyttäjän pisin putki” 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 SQL-työhaastatteluun valmistautuminen-kurssin, päivitä CoddyKit PROhon. SQL-työhaastatteluun valmistautuminen-kurssilla on yhteensä 4 oppituntia.

Mitä opin oppitunnilla ”Käyttäjän pisin putki”?

Kunkin ryhmän pisimmän peräkkäisen jakson laskeminen. Harjoittelet SQL-työhaastatteluun valmistautuminen-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.

Tarvitsenko kokemusta aloittaakseni SQL-työhaastatteluun valmistautuminen-opiskelun?

Aiempi kokemus ei ole tarpeen. CoddyKitin SQL-työhaastatteluun valmistautuminen-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 2/4.

Kuinka kauan ”Käyttäjän pisin putki”-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ä SQL-työhaastatteluun valmistautuminen-oppitunnilla?

Kyllä. Jokainen SQL-työhaastatteluun valmistautuminen-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

Peräkkäisten kalenteripäivien tunnistaminen
Käyttäjän pisin putki
N peräkkäistä ehtoa täyttävää riviä
Tämänhetkinen aktiivinen putki

← Takaisin: SQL-työhaastatteluun valmistautuminen