Walmart: Active Walmart+ Members on 30 June by Plan

Easy
1000.

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.

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

ColumnTypeValues
member_idint
planenummonthly, annual
start_datedate
end_datedate

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

The rows may come back in any order; the columns must be the ones shown, in that order.

Example 1

PlusMembership

member_idplanstart_dateend_date
1annual2024-08-01NULL
2monthly2025-01-152025-06-30
3monthly2025-03-012025-06-29
4annual2025-06-30NULL
5monthly2025-07-01NULL
6monthly2025-05-10NULL
7annual2024-02-012025-01-31

Output

planactive_members
annual2
monthly2

Example 2

PlusMembership

member_idplanstart_dateend_date
1monthly2025-04-01NULL
2annual2025-08-01NULL

Output

planactive_members
monthly1
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
member_idint
planenum(monthly, annual)
start_datedate
end_datedate

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_idplanstart_dateend_date
1annual2024-08-01NULL
2monthly2025-01-152025-06-30
3monthly2025-03-012025-06-29
4annual2025-06-30NULL
5monthly2025-07-01NULL
6monthly2025-05-10NULL
7annual2024-02-012025-01-31

Output

planactive_members
annual2
monthly2

Example 2

PlusMembership

member_idplanstart_dateend_date
1monthly2025-04-01NULL
2annual2025-08-01NULL

Output

planactive_members
monthly1

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 plan

Another 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 plan

Another 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 →