SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…