Interview Question Bank · generate_series · LEFT JOIN · COUNT(DISTINCT)
4. Campaign completion funnel
MediumGamingQuestion #148
Concept: generate_series · LEFT JOIN · COUNT(DISTINCT)show
A funnel needs one row per step, even steps nobody has reached, and grouping the event table can only produce steps that have events. Generate the steps with generate_series, LEFT JOIN the events onto them, and count a column from the events side so empty steps show 0. Use COUNT(DISTINCT person) because people repeat steps, and divide by the whole population, not by step one.
Example (different data):
SELECT s.step, COUNT(DISTINCT v.visitor_id) AS visitors FROM generate_series(1, 4) AS s(step) LEFT JOIN checkout_steps v ON v.step = s.step GROUP BY s.step ORDER BY s.step;
Your task
Skyrift Arena's campaign has levels 1 to 5, and replaying a level logs another completion. The product lead wants the completion funnel as a share of everyone who installed the game.
For each level from 1 to 5, return level, players (distinct players who completed it at least once) and pct_of_installs (players as a % of all players in gm_players, rounded to 1 decimal). Every level must appear, with 0 if nobody completed it. Sort by level.
Starting Postgres in your browser…
Checking your access…