Forberedelse til SQL-interview · Lektion

Ordrede hændelser og tidsvinduer

Sikring af, at trinene sker i rækkefølge og inden for en tidsgrænse ved hjælp af vinduesfunktioner.

Lektion 2 af 413 trin

Ordrede hændelser og tidsvinduer 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.

Hvorfor rækkefølge og tid betyder noget

Den grundlæggende tragt fra den forrige lektion kontrollerer kun, om en bruger udførte hvert trin. En skarp interviewer spørger: Fandt trinene sted i den rigtige rækkefølge og inden for en rimelig tid?

En bruger, der købte noget mandag og besøgte marketingsiden fredag, konverterede ikke gennem din tragt. Sekvens og tidsmæssig placering gør en naiv tragt baseret på markeringer troværdig.

Idéen om det første tidsstempel pr. bruger

For at kunne vurdere rækkefølgen skal du registrere hver brugers første tidspunkt på hvert trin. Det tidligste besøg, den tidligste signup, det tidligste purchase.

En ren konvertering betyder derefter, at first_signup_time >= first_visit_time og så videre ned gennem kæden. MIN(event_time) grupperet pr. trin giver dig disse holdepunkter.

SELECT
  user_id,
  MIN(CASE WHEN event_name = 'visit'    THEN event_time END) AS first_visit,
  MIN(CASE WHEN event_name = 'signup'   THEN event_time END) AS first_signup,
  MIN(CASE WHEN event_name = 'purchase' THEN event_time END) AS first_purchase
FROM events
GROUP BY user_id;

Sådan kræver du trin i rækkefølge

Når du har det første tidsstempel for hvert trin, håndhæves rækkefølgen ved en sammenligning. En bruger har reelt konverteret til trin 3, kun hvis hvert tidsstempel ikke er NULL og stiger monotont.

Bemærk, hvordan et NULL-tidsstempel (trinnet fandt aldrig sted) naturligt får sammenligningen til at fejle, hvilket er præcis det, du ønsker.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS visit_t,
    MIN(CASE WHEN event_name='signup'   THEN event_time END) AS signup_t,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS purchase_t
  FROM events GROUP BY user_id
)
SELECT COUNT(*) AS converted_in_order
FROM t
WHERE visit_t IS NOT NULL
  AND signup_t  >= visit_t
  AND purchase_t >= signup_t;

Sådan tilføjer du et tidsvindue

De fleste tragte har en frist: "konvertér inden for 7 dage efter første besøg." Tilføj en intervalgrænse mellem det første trin og det sidste trin.

Datoaritmetik varierer mellem dialekter. I Postgres kan du skrive visit_t + INTERVAL '7 days'; i MySQL bruger du DATE_ADD(visit_t, INTERVAL 7 DAY). Angiv altid din SQL-dialekt.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS visit_t,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS purchase_t
  FROM events GROUP BY user_id
)
SELECT COUNT(*) AS purchased_within_7d
FROM t
WHERE purchase_t >= visit_t
  AND purchase_t <  visit_t + INTERVAL '7 days';

Hvorfor det første tidsstempel og ikke et vilkårligt

Et subtilt spørgsmål i en teknisk samtale er, om tidsvinduet skal regnes fra brugerens første besøg eller det seneste besøg før tilmeldingen. Det afhænger af produktspørgsmålet.

Første kontakt-vinduer måler tiden fra den indledende interesse til konvertering.
Sidste kontakt-vinduer måler konverteringsforløbet efter det sidste besøg.

Spørg intervieweren, hvad vedkommende mener; hvis du vælger bevidst, viser det erfaring.

Ordnede hændelser med LEAD

Ved komplekse forløb med flere trin er vinduesfunktioner særligt nyttige. Sortér hver brugers hændelser efter tid, og brug LEAD til at se på den næste hændelse og bekræfte, at det er det forventede næste trin.

Det håndterer forløb, hvor trin flettes sammen med irrelevante hændelser.

SELECT
  user_id,
  event_name,
  event_time,
  LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event,
  LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_time
FROM events;

Match det næste forventede trin

Byg videre på LEAD: Behold rækker, hvor 'visit' umiddelbart efterfølges af 'signup'. Det finder ægte sekventielle overgange, ikke blot samforekomst.

Du kan kæde disse overgangstjek sammen for at validere et helt ordnet forløb trin for trin.

WITH seq AS (
  SELECT user_id, event_name, event_time,
    LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event
  FROM events
)
SELECT COUNT(DISTINCT user_id) AS visit_then_signup
FROM seq
WHERE event_name = 'visit' AND next_event = 'signup';

Tid mellem efterfølgende trin

Interviewere elsker spørgsmålet "Hvor lang tid tager hvert trin?" Brug LEAD på tidsstemplet, og træk det fra. Forskellen mellem efterfølgende hændelser er opholdstiden på det pågældende trin.

Aggregér medianen eller gennemsnittet pr. overgang for at finde det langsomste trin i tragten.

WITH seq AS (
  SELECT user_id, event_name, event_time,
    LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_time
  FROM events
)
SELECT
  event_name,
  AVG(EXTRACT(EPOCH FROM (next_time - event_time)) / 3600.0) AS avg_hours_to_next
FROM seq
WHERE next_time IS NOT NULL
GROUP BY event_name;

Specialtilfældet med samme tidsstempel

Hvad hvis to hændelser har præcis samme event_time? Så er signup_t >= visit_t sand, selv hvis hændelserne sker samtidigt, og rækkefølgen er tvetydig, når du kun sorterer efter tid.

Brug >= eller > bevidst, og forklar hvorfor.
Tilføj en tie-breaker som et hændelsessekvens-id til ORDER BY, så vinduerne bliver deterministiske.

Hvis du selv nævner dette, gør du et godt indtryk på interviewere.

SELECT user_id, event_name,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time, event_id) AS step_seq
FROM events;

Sådan kombinerer du rækkefølge og tidsvindue i én forespørgsel

Her er den komplette tragt, hvor trinene er i rækkefølge og inden for et tidsvindue. Den tager udgangspunkt i det første besøg, kræver, at hvert senere trins første forekomst kommer efter det foregående, og afgrænser hele forløbet til 7 dage.

Det er svaret, der adskiller en kandidat, som forstår tragte, fra en, der kun tæller markeringer.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS v,
    MIN(CASE WHEN event_name='signup'   THEN event_time END) AS s,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS p
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (WHERE v IS NOT NULL)                                   AS visited,
  COUNT(*) FILTER (WHERE s >= v AND s < v + INTERVAL '7 days')            AS signed_up,
  COUNT(*) FILTER (WHERE s >= v AND p >= s AND p < v + INTERVAL '7 days') AS purchased
FROM t;

Noter på tværs af SQL-dialekter

To påmindelser om portabilitet ved livekodning:

FILTER (WHERE ...) på aggregater er standard-SQL og fungerer i Postgres; i MySQL eller ældre databasemotorer kan du falde tilbage til SUM(CASE WHEN ... THEN 1 ELSE 0 END).
Intervalsyntaks varierer: Postgres + INTERVAL '7 days', MySQL DATE_ADD(d, INTERVAL 7 DAY), SQL Server DATEADD(day, 7, d).

Angiv din antagelse, så er intervieweren sjældent optaget af, hvilken dialekt du bruger, men blot af, om du ved, at de er forskellige.

Hurtigt tjek

Du skal tælle brugere, der gennemførte visit -> signup -> purchase i denne rækkefølge inden for 7 dage efter det første besøg. Hvilken tilgang er korrekt?

Besvar den i den interaktive lektion

Opsamling: Hændelser i rækkefølge og tidsvinduer

Vigtigste pointer:

Registrer hver brugers første tidsstempel for hvert trin med MIN(CASE ...).
Håndhæv rækkefølgen ved at kræve, at hvert trins tidspunkt ligger på eller efter det forrige trins tidspunkt.
Begræns forløbet med et interval, og angiv syntaksen for din SQL-dialekt.
Brug LEAD/LAG til overgangstjek og opholdstid mellem trin.
Håndter sammenfaldende tidsstempler med et ekstra sorteringskriterium i ORDER BY.

Næste emne: skiftet fra tragte til eksperimenter og beregning af målinger pr. variant.

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 “Ordrede hændelser og tidsvinduer” gratis?

Ja — hele teksten til “Ordrede hændelser og tidsvinduer” 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 “Ordrede hændelser og tidsvinduer”?

Sikring af, at trinene sker i rækkefølge og inden for en tidsgrænse ved hjælp af vinduesfunktioner. 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 “Ordrede hændelser og tidsvinduer”?

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

Opbygning af en tragt med flere trin
Ordrede hændelser og tidsvinduer
Tildeling og målinger i A/B-test
Effekt, signifikans og sikkerhedstjek i SQL

← Tilbage til Forberedelse til SQL-interview