Forberedelse til SQL-interview · Lektion

Længste serie pr. bruger

Beregning af den maksimale længde på en sammenhængende serie i hver gruppe.

Lektion 2 af 413 trin

Længste serie pr. bruger er en gratis Forberedelse til SQL-interview-lektion på CoddyKit. Dette er lektion 2 af 4. Du kan læse hele lektionen gratis nedenfor — og derefter øve dig praktisk i browseren med en indbygget kodeeditor og en AI-vejleder, der er tilgængelig døgnet rundt. Den er en del af læringsforløbet i Forberedelse til SQL-interview, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. Forberedelse til SQL-interview-kurset indeholder 4 lektioner i alt.

Spørgsmålet

Et hyppigt opfølgende spørgsmål til registrering af sammenhængende dage er: "Hvad er den længste serie af sammenhængende aktive dage for hver bruger?" Produkt- og vækstafdelinger stiller konstant dette spørgsmål for at måle brugerengagement.

Du ved allerede, hvordan du identificerer hver serie. Det nye trin er at finde den maksimale serielængde pr. bruger og ofte også returnere datoerne for den bedste serie. Denne lektion bygger direkte videre på grundstrukturen for huller og øer.

Husk, hvordan øerne bygges

Fra den foregående lektion bruger grupperingen pr. serie login_date - ROW_NUMBER() som øens forankring. Hver bruger kan have flere øer; først beregner vi én række pr. ø, og derefter reducerer vi resultatet til én række pr. bruger.

Husk denne plan i to lag: Byg først øerne, og aggregér derefter øerne.

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;

Én række pr. ø

Saml hver ø til en enkelt opsummerende række med dens længde og datointerval. Gruppér efter bruger og forankring, og beregn nøgletallene.

Vi kalder denne CTE islands, så det næste lag nemt kan læse fra den.

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;

Enkelt svar: maksimal længde

Hvis intervieweren kun vil have længden, er det sidste trin en enkelt linje: Gruppér øerne efter bruger, og vælg den maksimale længde.

Dette er det enkleste svar, når begyndelses- og slutdatoer ikke er nødvendige.

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

Returnér også datoerne

Ofte tilføjer intervieweren: "og vis, hvornår den serie fandt sted." En simpel MAX kan ikke fortælle dig, hvilken ø der vandt. Du skal rangere øerne inden for hver bruger og beholde rang 1.

Brug ROW_NUMBER sorteret efter længde i faldende rækkefølge, så hver brugers bedste serie får rang 1. Tilføj et ekstra sorteringskriterium, så lige resultater afgøres entydigt.

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

Rangér og filtrér

Indlejr rangeringen i en CTE, og filtrér derefter til rnk = 1. Du kan ikke filtrere direkte på en vinduesfunktion i WHERE, så det ekstra lag er påkrævet.

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 kontra ROW_NUMBER ved lige resultater

Hvad nu, hvis en bruger har to serier med samme maksimale længde, og intervieweren vil have begge med? Erstat ROW_NUMBER med RANK, og behold rnk = 1.

ROW_NUMBER – præcis én vinder pr. bruger (vilkårlig ved lige resultater, medmindre du tilføjer et ekstra sorteringskriterium).
RANK – alle lige lange længste serier får rang 1 og beholdes.

Afklar, hvilken adfærd de ønsker; det viser, at du er opmærksom på kanttilfælde.

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

Gennemgået eksempel

Antag, at bruger 7 loggede ind 1.-4. januar, derefter 10.-11. januar og til sidst 20.-23. januar. Tre øer med længderne 4, 2 og 4. Den længste længde er 4, og der er lige resultater.

Med ROW_NUMBER + tie-breaker streak_start: returneres kun serien fra 1.-4. januar.
Med RANK: returneres både serien fra 1.-4. januar og serien fra 20.-23. januar.

Hvis du siger dette højt, viser du, at du har tænkt over dubletter.

Håndtering af brugere uden login

Den, der interviewer dig, kan spørge: "Hvad med brugere, der aldrig loggede ind?" Disse brugere har ingen rækker i logins, så de forsvinder fra resultatet. Hvis de skal vises med en serie på 0, skal du bruge LEFT JOIN på hele users-tabellen og COALESCE.

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;

Bemærkninger om ydeevne

Dette mønster laver én sorteret gennemgang af dataene plus en gruppering. For at holde det hurtigt:

Sørg for et indeks på (user_id, login_date), så vinduets ORDER BY undgår sortering.
Fjern dubletter tidligt, hvis kilden har flere hændelser pr. dag.
Undgå at omslutte login_date med funktioner i ORDER BY, hvilket kan blokere brugen af indekset.

For meget store tabeller er dette markant hurtigere end enhver tilgang med selvjoin.

Det komplette interviewsvar

Her er den komplette, gennemarbejdede forespørgsel, der returnerer hver brugers længste serie samt datoerne for den — den version, du skal skrive på tavlen.

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;

Hurtigt tjek

Vælg det rigtige værktøj til kravet.

Besvar den i den interaktive lektion

Opsummering

Sådan beregner du den længste serie pr. bruger:

Byg øer med ankeret login_date - ROW_NUMBER().
Reducer hver ø til længde samt et datointerval.
Hvis du kun skal bruge længden, så brug MAX(streak_len) grupperet efter bruger.
Hvis du også skal bruge datoerne, så rangér øerne pr. bruger, og behold rang 1 — brug RANK for at medtage lige resultater og ROW_NUMBER for at vælge én vinder.
Brug LEFT JOIN på users for at få brugere med en serie på 0 med.

Næste emne: registrering af N sammenhængende rækker, der opfylder en betingelse.

Gratis at komme i gang

Lær SQL med en AI-underviser — gratis

Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.

Kurser: 30
Lektioner: 120

Start gratis Se hele kurset

Ofte stillede spørgsmål

Er lektionen “Længste serie pr. bruger” gratis?

Ja — hele teksten til “Længste serie pr. bruger” kan læses gratis her på nettet. Hvis du vil øve dig interaktivt med en indbygget kodeeditor og en AI-vejleder døgnet rundt og få adgang til resten af Forberedelse til SQL-interview-kurset, skal du opgradere til CoddyKit PRO. Forberedelse til SQL-interview-kurset indeholder 4 lektioner i alt.

Hvad lærer jeg i “Længste serie pr. bruger”?

Beregning af den maksimale længde på en sammenhængende serie i hver gruppe. Du øver dig i Forberedelse til SQL-interview med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.

Skal jeg have erfaring for at begynde på Forberedelse til SQL-interview?

Der kræves ingen tidligere erfaring. Forberedelse til SQL-interview på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 2 af 4.

Hvor lang tid tager lektionen “Længste serie pr. bruger”?

De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.

Kan jeg skrive og køre kode i denne Forberedelse til SQL-interview-lektion?

Ja. Alle Forberedelse til SQL-interview-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.

Alle lektioner i dette kursus

Registrering af fortløbende kalenderdage
Længste serie pr. bruger
N sammenhængende rækker, der opfylder en betingelse
Aktuel aktiv serie pr. i dag

← Tilbage til Forberedelse til SQL-interview