Walmart: Sam's Club Members Who Shopped Away From Home

Easy
1005.

Walmart: Sam's Club Members Who Shopped Away From Home

EasyJoins

MySQL

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

ColumnType
member_idint
member_namevarchar
home_club_idint

Primary key: member_id.

One row per Sam's Club member; home_club_id is the club where the membership was bought.

Table: ClubVisit

ColumnType
visit_idint
member_idint
club_idint
visit_datedate
spenddecimal

Primary key: visit_id.

One row per checkout at a club, scanned with the member's card.

Example

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

ClubMember

member_idmember_namehome_club_id
1Maria8101
2David8101
3Priya8240
4John8240
5Emma8355

ClubVisit

visit_idmember_idclub_idvisit_datespend
10181012025-02-01182.4
11181012025-02-1596.1
12282402025-02-03240
13283552025-02-2075.5
14282402025-03-01130.25
15382402025-02-0761
16481012025-02-09410.9

Output

member_idmember_name
2David
4John
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: 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

ColumnType
member_idint
member_namevarchar
home_club_idint

Primary key: member_id.

One row per Sam's Club member; home_club_id is the club where the membership was bought.

Table: ClubVisit

ColumnType
visit_idint
member_idint
club_idint
visit_datedate
spenddecimal

Primary key: visit_id.

One row per checkout at a club, scanned with the member's card.

Examples

Example 1

ClubMember

member_idmember_namehome_club_id
1Maria8101
2David8101
3Priya8240
4John8240
5Emma8355

ClubVisit

visit_idmember_idclub_idvisit_datespend
10181012025-02-01182.4
11181012025-02-1596.1
12282402025-02-03240
13283552025-02-2075.5
14282402025-03-01130.25
15382402025-02-0761
16481012025-02-09410.9

Output

member_idmember_name
2David
4John

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_id

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