Interview Drills · COUNT(DISTINCT month) · relational division
42. Active in every month
Concept: COUNT(DISTINCT month) · relational divisionshow
'Did X in every period' is relational division. The usual SQL trick: filter to the period, group by the entity, and keep groups where the number of distinct periods equals the number of periods: HAVING COUNT(DISTINCT date_trunc('month', d)) = 3. Count distinct months, not rows, because one busy month can fake the count.
Example (different data):
SELECT member_id
FROM gym_visits
WHERE visit_date >= '2024-01-01' AND visit_date < '2024-07-01'
GROUP BY member_id
HAVING COUNT(DISTINCT date_trunc('month', visit_date)) = 6;Your task
A card-rewards program qualifies accounts that used the card for everyday spending (groceries or dining, not travel) in every month of Q1 2024.
Return account_id and everyday_spend (total groceries and dining spend in Q1) for accounts with at least one groceries or dining transaction in each of January, February and March 2024. Sort by account_id.
Starting Postgres in your browser…
Checking your access…