Drill 4 of 12 in Gaming questions
0

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…