Walmart: Sam's Club Members Who Shopped Away From Home
Sam's Club's membership analytics team wants to know which members also shop at clubs other than the one they signed up at, for a travelling-member campaign.
- Difficulty: Easy
- Topics: Joins
- Dialect: MySQL
- Problem: #1005
Problem statement
Sam's Club's membership analytics team wants to know which members also shop at clubs other than the one they signed up at, for a travelling-member campaign.
Return each member with at least one visit to a club other than their home_club_id, as member_id and member_name. Each member appears once, however many such visits they made. Members with no visits, or only home-club visits, are left out. 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: ClubMember
| Column | Type |
|---|---|
| member_id | int |
| member_name | varchar |
| home_club_id | int |
Primary key: member_id.
One row per Sam's Club member; home_club_id is the club where the membership was bought.
Table: ClubVisit
| Column | Type |
|---|---|
| visit_id | int |
| member_id | int |
| club_id | int |
| visit_date | date |
| spend | decimal |
Primary key: visit_id.
One row per checkout at a club, scanned with the member's card.
Examples
Example 1
ClubMember
| member_id | member_name | home_club_id |
|---|---|---|
| 1 | Maria | 8101 |
| 2 | David | 8101 |
| 3 | Priya | 8240 |
| 4 | John | 8240 |
| 5 | Emma | 8355 |
ClubVisit
| visit_id | member_id | club_id | visit_date | spend |
|---|---|---|---|---|
| 10 | 1 | 8101 | 2025-02-01 | 182.4 |
| 11 | 1 | 8101 | 2025-02-15 | 96.1 |
| 12 | 2 | 8240 | 2025-02-03 | 240 |
| 13 | 2 | 8355 | 2025-02-20 | 75.5 |
| 14 | 2 | 8240 | 2025-03-01 | 130.25 |
| 15 | 3 | 8240 | 2025-02-07 | 61 |
| 16 | 4 | 8101 | 2025-02-09 | 410.9 |
Output
| member_id | member_name |
|---|---|
| 2 | David |
| 4 | John |
How to solve Walmart: Sam's Club Members Who Shopped Away From Home
The question is a yes/no one per member: does at least one away visit exist? That is a semi join.
Joining members to their visits puts the home club and the visited club on the same row, and v.club_id <> m.home_club_id keeps the away visits. A member with several away visits produces several rows, so DISTINCT collapses them to one. Members with no visits never join, and members whose visits were all at home lose every row in the filter — both drop out without extra work.
EXISTS expresses the same idea without creating duplicates in the first place: for each member it stops at the first away visit it finds. The third version groups each member's visits and keeps those where the count of away visits is positive. All three read each visit once; an index on ClubVisit(member_id) makes the EXISTS probe cheap.
Reference solution (MySQL)
SELECT DISTINCT m.member_id, m.member_name
FROM ClubMember m
JOIN ClubVisit v ON v.member_id = m.member_id
WHERE v.club_id <> m.home_club_idAnother way
SELECT m.member_id, m.member_name FROM ClubMember m WHERE EXISTS (SELECT 1 FROM ClubVisit v WHERE v.member_id = m.member_id AND v.club_id <> m.home_club_id)Another way
SELECT m.member_id, m.member_name
FROM ClubMember m
JOIN ClubVisit v ON v.member_id = m.member_id
GROUP BY m.member_id, m.member_name
HAVING SUM(v.club_id <> m.home_club_id) > 0← Walmart: DC Trailers Dispatched Under 80% Full · Walmart: Price Match Approval Rate by Competitor →