Walmart: Active Walmart+ Members on 30 June by Plan
The Walmart+ membership team reports its member base at the end of each quarter, split by how members pay.
- Difficulty: Easy
- Topics: Aggregation, Dates
- Dialect: MySQL
- Problem: #1000
Problem statement
The Walmart+ membership team reports its member base at the end of each quarter, split by how members pay.
A membership is active on 2025-06-30 when it started on or before that day and either has no end_date yet or its end_date is on or after that day. For each billing plan with at least one active membership, return plan and active_members. Plans with no active membership do not appear. Return the rows in any order.
A practice scenario set at Walmart: the tables and data are invented, and this is not a question from, or endorsed by, Walmart.
Tables
Table: PlusMembership
| Column | Type |
|---|---|
| member_id | int |
| plan | enum(monthly, annual) |
| start_date | date |
| end_date | date |
Primary key: member_id.
One row per membership. end_date is the last day of benefits, or NULL while the membership is still running.
Examples
Example 1
PlusMembership
| member_id | plan | start_date | end_date |
|---|---|---|---|
| 1 | annual | 2024-08-01 | NULL |
| 2 | monthly | 2025-01-15 | 2025-06-30 |
| 3 | monthly | 2025-03-01 | 2025-06-29 |
| 4 | annual | 2025-06-30 | NULL |
| 5 | monthly | 2025-07-01 | NULL |
| 6 | monthly | 2025-05-10 | NULL |
| 7 | annual | 2024-02-01 | 2025-01-31 |
Output
| plan | active_members |
|---|---|
| annual | 2 |
| monthly | 2 |
Example 2
PlusMembership
| member_id | plan | start_date | end_date |
|---|---|---|---|
| 1 | monthly | 2025-04-01 | NULL |
| 2 | annual | 2025-08-01 | NULL |
Output
| plan | active_members |
|---|---|
| monthly | 1 |
How to solve Walmart: Active Walmart+ Members on 30 June by Plan
The count is per plan, but whether a row counts is a question about that row alone, so the work splits cleanly: filter, then group.
A membership is active on the day when the day sits between its start and its end. The end is open while end_date is NULL, and a comparison with NULL is never true, so the condition must say so explicitly: end_date IS NULL OR end_date >= '2025-06-30'. The parentheses matter — without them AND binds first and every open-ended membership would count, even one that starts in July. Both boundaries are inclusive: a membership that started or ended on 30 June was active that day.
Because the filter runs before GROUP BY, a plan whose memberships all fail it simply never forms a group, which is what the statement asks. The first alternative replaces the NULL with a far-future date using COALESCE and uses one BETWEEN; the second counts with a conditional SUM and removes empty plans with HAVING. All are a single scan.
Reference solution (MySQL)
SELECT plan, COUNT(*) AS active_members
FROM PlusMembership
WHERE start_date <= '2025-06-30'
AND (end_date IS NULL OR end_date >= '2025-06-30')
GROUP BY planAnother way
SELECT plan, COUNT(*) AS active_members FROM PlusMembership WHERE '2025-06-30' BETWEEN start_date AND COALESCE(end_date, '9999-12-31') GROUP BY planAnother way
SELECT plan, SUM(CASE WHEN start_date <= '2025-06-30' AND (end_date IS NULL OR end_date >= '2025-06-30') THEN 1 ELSE 0 END) AS active_members
FROM PlusMembership GROUP BY plan
HAVING SUM(CASE WHEN start_date <= '2025-06-30' AND (end_date IS NULL OR end_date >= '2025-06-30') THEN 1 ELSE 0 END) > 0← Walmart: Grocery Pickup Slots Nobody Booked · Walmart: Grocery vs General Merchandise Sales per Store →