SQL for Channel Sign-Ups and Growth | Dataford Interview Questions

Your question is SQL for Channel Sign-Ups and Growth. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Turing's Product Growth team needs a weekly view of candidate acquisition performance. Write a PostgreSQL query that reports sign-ups and application conversion by acquisition channel.

A candidate is considered converted when they submit at least one Turing application within seven days after signing up. Include acquisition channels with sign-ups even when no candidate converted.

Requirements

Group sign-ups by the Monday of the signup week and acquisition_channel.
Calculate weekly sign-ups, converted candidates, and conversion rate as a percentage.
Calculate week-over-week sign-up growth for the same acquisition channel. Return NULL when no prior-week comparison exists.
Include the supplied reporting period from January 1 through January 31, 2025, and sort by week followed by acquisition channel.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Turing candidate identifier
signup_at	TIMESTAMP	Timestamp when the candidate created an account
acquisition_channel	VARCHAR(50)	Marketing acquisition source

applications

Column	Type	Description
application_idPK	INT	Unique application identifier
user_id	INT	Candidate who submitted the application
submitted_at	TIMESTAMP	Application submission timestamp