Drill 3 of 12 in SaaS questions
0

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…