Interview Question Bank · date_trunc('week') · COUNT FILTER
3. Trial-to-paid conversion by signup week
EasySaaSQuestion #111
Concept: date_trunc('week') · COUNT FILTERshow
date_trunc('week', d) snaps any date to the Monday that starts its week, which makes a clean weekly bucket. Inside each bucket, COUNT(*) FILTER (WHERE ...) counts only the rows that pass a condition, so you can get a total and a subset in one pass. Leave out rows whose outcome isn't known yet, or they drag the rate down.
Example (different data):
SELECT date_trunc('week', applied_on)::date AS week,
COUNT(*) AS applications,
COUNT(*) FILTER (WHERE outcome = 'hired') AS hires
FROM job_applications
WHERE outcome <> 'pending'
GROUP BY 1
ORDER BY 1;Your task
Growth at Tallyboard changed the onboarding emails in June 2025 and wants trial-to-paid conversion by the week each trial started. Trials last 14 days, and the data is a snapshot taken on 2025-07-01.
Leaving out trials still running (status 'in_trial'), return signup_week (the Monday of the week the trial started, as a date), trials, converted (status 'converted') and conversion_pct (converted as a % of trials, rounded to 1 decimal). Sort by signup_week.
Starting Postgres in your browser…
Checking your access…