Interview Question Bank · COUNT(DISTINCT) · scalar denominator · timestamp ranges
6. Feature adoption across active workspaces
MediumSaaSQuestion #114
Concept: COUNT(DISTINCT) · scalar denominator · timestamp rangesshow
Adoption is the share of active users (here, workspaces) that used a feature at least once, so both numerator and denominator count distinct workspaces, not events. The denominator is the active base for the same period, computed once in a CTE or scalar subquery and divided into every feature's count. For a month of timestamps, use >= the 1st and < the 1st of the next month, so the last day's afternoon isn't cut off.
Example (different data):
WITH base AS ( SELECT COUNT(DISTINCT store_id) AS n FROM sales WHERE sold_at >= '2025-01-01' AND sold_at < '2025-02-01' ) SELECT promo, ROUND(100.0 * COUNT(DISTINCT store_id) / MAX(base.n), 1) AS pct FROM sales CROSS JOIN base WHERE sold_at >= '2025-01-01' AND sold_at < '2025-02-01' GROUP BY promo;
Your task
Tallyboard's product manager wants to know how widely each feature was used in March 2025. A workspace counts as active if it logged any event that month, and login doesn't count as a feature.
For March 2025, return feature (each event_name except 'login'), workspaces (distinct accounts that used it) and adoption_pct (workspaces as a % of all accounts active in March, rounded to 1 decimal). Sort by adoption_pct descending, then feature.
Starting Postgres in your browser…
Checking your access…