Forberedelse til SQL-interview · Lektion

Opbygning af en tragt med flere trin

Optælling af brugere, der når hvert trin i den rigtige rækkefølge, samt beregning af konverteringsrater pr. trin.

Lektion 1 af 413 trin

Opbygning af en tragt med flere trin er en gratis Forberedelse til SQL-interview-lektion på CoddyKit. Dette er lektion 1 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.

Hvad et spørgsmål om en tragt egentlig tester

Når en interviewer siger "byg en tilmeldingstragt", tester vedkommende, om du kan tælle unikke brugere, der når hvert trin i den rigtige rækkefølge, og udtrykke frafaldet mellem dem.

En tragt har trin som visit -> signup -> activate -> purchase. Leverancen er normalt én række pr. trin med et antal brugere og en konverteringsrate.

Tæl brugere, ikke hændelser (hvis én bruger udløser en hændelse to gange, tæller det stadig som én bruger).
Trinene er ordnede; at nå trin 3 betyder, at brugeren også nåede trin 1 og 2.

Den hændelsestabel, du får udleveret

Næsten alle spørgsmål om tragte giver dig en enkelt tabel med hændelser i langt format. Forestil dig denne struktur:

user_id den bruger, der udførte handlingen
event_name såsom 'visit', 'signup', 'purchase'
event_time et tidsstempel

Én række pr. handling. Din opgave er at omforme dette til en optælling trin for trin. Bekræft altid de præcise hændelsesnavne med intervieweren, før du skriver SQL.

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

Sådan tæller du brugere på ét trin

Begynd enkelt: Hvor mange unikke brugere nåede et enkelt trin? Brug COUNT(DISTINCT user_id) med et filter på hændelsesnavnet.

Dette er grundstenen i enhver tragt. Hvis du kan tælle ét trin på en enkel måde, kan du tælle dem alle.

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

Betinget aggregering for alle trin

Det klare svar i en teknisk samtale tæller hvert trin i én gennemgang ved hjælp af betinget aggregering: en CASE inde i COUNT(DISTINCT ...).

For hvert trin tæller du de unikke brugere, hvis hændelse svarer til trinnet. Én scanning, én række med trintotaler.

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;

Den skjulte fejl: Trinene er ikke ordnede

Forespørgslen, du lige så, har en fælde, som interviewere elsker. Den tæller alle, der udløste 'purchase', selvom de aldrig besøgte eller tilmeldte sig i dataene.

En rigtig tragt kræver, at hvert senere trin er en delmængde af det tidligere trin. Hvis du tæller hændelser uafhængigt, kan trin 3 blive større end trin 2, hvilket er logisk umuligt for en tragt.

Løsningen er at binde brugerens trin sammen, normalt ved først at samle dataene til én række pr. bruger.

Én række pr. bruger med markeringer

Det robuste mønster er at samle hændelsesloggen til én række pr. bruger med en boolsk markering (som 0/1) for, om brugeren nogensinde udførte hvert trin. MAX(CASE ...) omdanner den lange log til en bred oversigt pr. bruger.

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;

Sådan håndhæver du trinrækkefølgen

Nu håndhæver du tragtreglen: En bruger tæller kun med på trin N, hvis vedkommende også udførte alle tidligere trin. At nå 'purchase' er kun relevant, hvis brugeren også besøgte siden og tilmeldte sig.

Summér markeringerne med forudsætningsbetingelserne forbundet med AND, så hvert trin reelt er en delmængde af det foregående.

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;

Sådan omdanner du antal til et overskueligt langt resultat

Interviewere foretrækker ofte én række pr. trin frem for én bred række. Omdan de brede totaler til langt format med en lille UNION ALL, og tilføj et trinnummer til sortering.

Det gør beregningen af konverteringsrater og visualisering meget nemmere i næste trin.

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;

Konverteringsrate fra trin til trin

To rater er vigtige, og interviewere vil spørge, hvilken du mener:

Konvertering for trinnet: antallet af brugere på dette trin divideret med antallet af brugere på det foregående trin.
Samlet konvertering: antallet af brugere på dette trin divideret med antallet på tragtens første trin.

Brug LAG til at hente antallet for det foregående trin til raten fra trin til trin. Konvertér til et decimaltal, så du ikke får heltalsdivision.

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;

Samlet konvertering fra toppen

Ved samlet konvertering dividerer du antallet på hvert trin med antallet på det første trin. FIRST_VALUE over den sorterede tragt fastholder dette top-tal på hver række.

Nævn altid over for intervieweren, at du har beskyttet mod heltalsdivision ved at gange med 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;

Sådan samler du hele tragten

Her er det komplette svar fra ende til anden, som interviewere forventer: Saml dataene til markeringer pr. bruger, håndhæv rækkefølgen, omdan til langt format, og beregn derefter begge rater. Gennemgå det højt, og angiv formålet med hver CTE.

Strukturen kan skaleres: Tilføj et trin ved at tilføje én markering og én række med UNION ALL.

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;

Hurtigt tjek

En interviewer opdager, at din tragt viser 95 brugere på 'purchase', men kun 80 på 'signup'. Hvad er den mest sandsynlige årsag?

Besvar den i den interaktive lektion

Opsummering: Tragter med flere trin

Du kan nu opbygge en tragt på den måde, som tekniske samtaler forventer:

Tæl unikke brugere pr. ordnet trin, aldrig rå hændelser.
Saml hændelsesloggen til én række pr. bruger med MAX(CASE ...)-markeringer.
Håndhæv rækkefølgen, så hvert trin er en delmængde af det foregående.
Beregn konvertering fra trin til trin (LAG) og samlet konvertering (FIRST_VALUE), og beskyt mod heltalsdivision.

Næste emne: Sørg for, at trinene faktisk fandt sted i den rigtige rækkefølge og inden for et tidsvindue.

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 “Opbygning af en tragt med flere trin” gratis?

Ja — hele teksten til “Opbygning af en tragt med flere trin” 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 “Opbygning af en tragt med flere trin”?

Optælling af brugere, der når hvert trin i den rigtige rækkefølge, samt beregning af konverteringsrater pr. trin. 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 1 af 4.

Hvor lang tid tager lektionen “Opbygning af en tragt med flere trin”?

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