Walmart: Truck Routes Rebuilt From Store Geofence Pings

Hard
1018.

Walmart: Truck Routes Rebuilt From Store Geofence Pings

Walmart's transportation analytics team rebuilds the route each delivery truck actually drove from its telematics: every time a truck enters a store's geofence, a ping is logged. A truck idling or shunting in a store's yard can enter the same geofence several times in a row.

For each run with at least one ping, list the stores in the order the truck entered them (entered_at), with consecutive pings at the same store collapsed into one visit. A store the truck comes back to later, after another store, is a new visit and appears again. Return run_id, dc_id, route (the visited store_ids joined by ' > ', e.g. 1204 > 3318 > 1204) and store_visits (the number of visits in the route). Runs with no pings are left out. Order by run_id.

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: TruckRun

ColumnType
run_idint
dc_idint
run_datedate

Primary key: run_id.

One row per outbound truck run from a distribution centre.

Table: GeofencePing

ColumnType
ping_idint
run_idint
store_idint
entered_atdatetime

Primary key: ping_id.

One row each time the run's truck entered a store's geofence. Within a run no two pings share a time; ping ids are not in time order.

Example

The rows must come back in the order shown.

TruckRun

run_iddc_idrun_date
50160112025-09-15
50260942025-09-15
50360112025-09-15
50460942025-09-16

GeofencePing

ping_idrun_idstore_identered_at
750112042025-09-15 06:10:00
250112042025-09-15 06:25:00
950133182025-09-15 07:40:00
450152602025-09-15 09:05:00
150152602025-09-15 09:07:00
850133182025-09-15 10:30:00
350241002025-09-15 05:50:00
650433182025-09-16 07:00:00
550412042025-09-16 08:15:00

Output

run_iddc_idroutestore_visits
50160111204 > 3318 > 5260 > 33184
502609441001
50460943318 > 12042
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: Truck Routes Rebuilt From Store Geofence Pings

Walmart's transportation analytics team rebuilds the route each delivery truck actually drove from its telematics: every time a truck enters a store's…

  • Difficulty: Hard
  • Topics: Strings, Window Functions, Aggregation
  • Dialect: MySQL
  • Problem: #1018

Problem statement

Walmart's transportation analytics team rebuilds the route each delivery truck actually drove from its telematics: every time a truck enters a store's geofence, a ping is logged. A truck idling or shunting in a store's yard can enter the same geofence several times in a row.

For each run with at least one ping, list the stores in the order the truck entered them (entered_at), with consecutive pings at the same store collapsed into one visit. A store the truck comes back to later, after another store, is a new visit and appears again. Return run_id, dc_id, route (the visited store_ids joined by ' > ', e.g. 1204 > 3318 > 1204) and store_visits (the number of visits in the route). Runs with no pings are left out. Order by run_id.

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: TruckRun

ColumnType
run_idint
dc_idint
run_datedate

Primary key: run_id.

One row per outbound truck run from a distribution centre.

Table: GeofencePing

ColumnType
ping_idint
run_idint
store_idint
entered_atdatetime

Primary key: ping_id.

One row each time the run's truck entered a store's geofence. Within a run no two pings share a time; ping ids are not in time order.

Examples

Example 1

TruckRun

run_iddc_idrun_date
50160112025-09-15
50260942025-09-15
50360112025-09-15
50460942025-09-16

GeofencePing

ping_idrun_idstore_identered_at
750112042025-09-15 06:10:00
250112042025-09-15 06:25:00
950133182025-09-15 07:40:00
450152602025-09-15 09:05:00
150152602025-09-15 09:07:00
850133182025-09-15 10:30:00
350241002025-09-15 05:50:00
650433182025-09-16 07:00:00
550412042025-09-16 08:15:00

Output

run_iddc_idroutestore_visits
50160111204 > 3318 > 5260 > 33184
502609441001
50460943318 > 12042

How to solve Walmart: Truck Routes Rebuilt From Store Geofence Pings

The output is a sequence string, and GROUP_CONCAT builds one — but only if it is told the order. An aggregate otherwise sees rows in whatever order the engine reads them, and here the ping ids are deliberately out of time order, so GROUP_CONCAT(store_id ORDER BY entered_at SEPARATOR ' > ') is the essential spelling. Without the ORDER BY the route could come out shuffled; without the separator MySQL uses a bare comma.

The pings are not the visits yet. A truck shunting in a yard enters the same geofence two or three times in a row, and those must count once. That is a collapse of consecutive duplicates: keep a ping only when the previous ping of the same run was at a different store. LAG(store_id) OVER (PARTITION BY run_id ORDER BY entered_at) brings the previous store onto each row; the first ping of a run has NULL there and is kept. Note what this does not do: a store revisited after another stop is a new visit and stays in the route. DISTINCT inside GROUP_CONCAT would wrongly erase that second visit, and it would also lose the order.

After the collapse, grouping by run gives the route and, with COUNT(*), the number of visits; the inner join to TruckRun supplies the DC and leaves out runs with no pings. The alternative finds the previous ping with a correlated MAX(entered_at) and drops a ping when that previous ping was at the same store (NOT EXISTS), then concatenates in the same way. The window version is one sort per run.

Reference solution (MySQL)

WITH p AS (
  SELECT run_id, store_id, entered_at,
         LAG(store_id) OVER (PARTITION BY run_id ORDER BY entered_at) AS prev_store
  FROM GeofencePing
), visits AS (
  SELECT run_id, store_id, entered_at
  FROM p
  WHERE prev_store IS NULL OR prev_store <> store_id
)
SELECT r.run_id, r.dc_id,
       GROUP_CONCAT(v.store_id ORDER BY v.entered_at SEPARATOR ' > ') AS route,
       COUNT(*) AS store_visits
FROM TruckRun r
JOIN visits v ON v.run_id = r.run_id
GROUP BY r.run_id, r.dc_id
ORDER BY r.run_id

Another way

SELECT r.run_id, r.dc_id,
       GROUP_CONCAT(g.store_id ORDER BY g.entered_at SEPARATOR ' > ') AS route,
       COUNT(*) AS store_visits
FROM TruckRun r
JOIN GeofencePing g ON g.run_id = r.run_id
WHERE NOT EXISTS (
  SELECT 1 FROM GeofencePing q
  WHERE q.run_id = g.run_id AND q.store_id = g.store_id
    AND q.entered_at = (SELECT MAX(z.entered_at) FROM GeofencePing z
                        WHERE z.run_id = g.run_id AND z.entered_at < g.entered_at)
)
GROUP BY r.run_id, r.dc_id
ORDER BY r.run_id

← Walmart: Walmart+ Membership Spells, Bridging Short Lapses · Walmart: Department Pairs Bought Together More Than by Chance →