Gym Members' First and Latest Branch — SQL Medium Problem

Problem statement

A gym chain with branches across Bengaluru logs every member check-in. The marketing team wants to know where each member started, where they went most recently, and how many visits came in between.

Return one row per member who has checked in at least once, with columns member_id, first_branch (the branch of their earliest check-in), latest_branch (the branch of their most recent check-in) and visits_between (the number of their check-ins strictly after the earliest and strictly before the most recent — 0 for a member with one or two check-ins). Order the rows by member_id.

Tables

Table: GymCheckIn

Primary key: member_id, checked_in_at.

One row per check-in. A member never has two check-ins at the same moment, so their first and latest check-ins are well defined.

Examples

Example 1

GymCheckIn

Output

How to solve Gym Members' First and Latest Branch

The classic trap here is SELECT member_id, MIN(checked_in_at), branch … GROUP BY member_id: the aggregate finds the right time, but branch is not grouped, so the database is free to return the branch of any row (MySQL refuses it outright under ONLY_FULL_GROUP_BY). The branch has to come from the specific row that is first, or latest.

ROW_NUMBER() marks those rows. Partitioned by member and ordered by time, it numbers the earliest check-in 1; ordered by time descending, it numbers the latest one 1. Then group by member: MAX(CASE WHEN from_first = 1 THEN branch END) returns the one branch carrying that mark (the CASE is NULL on every other row, and MAX ignores NULLs). The visits strictly in between are COUNT(*) - 2, floored at 0 by GREATEST for members with a single check-in; with distinct timestamps nothing ties with the first or latest visit.

FIRST_VALUE(branch) over the same two orderings reads the branches without the grouping step; DISTINCT then collapses each member's identical rows. A window-free version finds each member's first and last times with MIN/MAX, then looks up the branch at those times and counts the visits strictly between them with correlated subqueries. The window plans sort the table by member and time once.

Reference solution (MySQL)

SELECT member_id,
       MAX(CASE WHEN from_first = 1 THEN branch END) AS first_branch,
       MAX(CASE WHEN from_latest = 1 THEN branch END) AS latest_branch,
       GREATEST(COUNT(*) - 2, 0) AS visits_between
FROM (
  SELECT member_id, branch,
         ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY checked_in_at) AS from_first,
         ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY checked_in_at DESC) AS from_latest
  FROM GymCheckIn
) v
GROUP BY member_id
ORDER BY member_id

Another way

SELECT DISTINCT member_id,
       FIRST_VALUE(branch) OVER (PARTITION BY member_id ORDER BY checked_in_at) AS first_branch,
       FIRST_VALUE(branch) OVER (PARTITION BY member_id ORDER BY checked_in_at DESC) AS latest_branch,
       GREATEST(COUNT(*) OVER (PARTITION BY member_id) - 2, 0) AS visits_between
FROM GymCheckIn
ORDER BY member_id

Another way

SELECT s.member_id,
       (SELECT x.branch FROM GymCheckIn x WHERE x.member_id = s.member_id AND x.checked_in_at = s.first_at) AS first_branch,
       (SELECT x.branch FROM GymCheckIn x WHERE x.member_id = s.member_id AND x.checked_in_at = s.last_at) AS latest_branch,
       (SELECT COUNT(*) FROM GymCheckIn x
         WHERE x.member_id = s.member_id AND x.checked_in_at > s.first_at AND x.checked_in_at < s.last_at) AS visits_between
FROM (SELECT member_id, MIN(checked_in_at) AS first_at, MAX(checked_in_at) AS last_at FROM GymCheckIn GROUP BY member_id) s
ORDER BY s.member_id