Drill 6 of 12 in SaaS questions
0

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…