A grocery chain wants shorter checkout lines without hiring more staff. How do you approach it?

“I’ll find the stores and slots where lines form, measure the wait there, compare the schedule with demand in those slots, and test one change in one store.”

Clarify. “Which stores and hours do the complaints come from? I’ll assume the busiest stores at weekend and after-work peaks, and check that in the first query.”

People and metric. “Store operations owns the goal; front-end leads are the users. The metric is the share of peak-slot customers who wait longer than a threshold store operations picks, say wait > 5 min, measured against the current baseline from the sampling week. Labor hours are the constraint, and shrink and out-of-stocks are guardrails, because pulling stockers to the lanes empties shelves.”

What a transaction record gives me. “Store, lane, cashier, first scan time, payment-complete time, item count, payment type, and any voids or overrides. From that I get service time directly. Queue time I can only infer: if a lane’s next transaction starts within seconds of the last one ending, someone was already waiting.”

WITH tx AS (
  SELECT t.*, t.started_at AT TIME ZONE s.tz AS local_ts
  FROM pos_transaction t
  JOIN store s USING (store_id)
  WHERE t.started_at >= now() - interval '8 weeks'
),
gaps AS (
  SELECT *,
         started_at - lag(ended_at) OVER (
           PARTITION BY store_id, lane_id, local_ts::date
           ORDER BY started_at
         ) AS idle
  FROM tx
),
per_day AS (
  SELECT store_id,
         local_ts::date AS day,
         date_bin('15 minutes', local_ts,
                  TIMESTAMP '2026-01-05')::time AS slot,
         count(DISTINCT lane_id) AS lanes,
         count(*) AS tx,
         avg(ended_at - started_at) AS service,
         avg((idle < interval '20 seconds')::int) AS b2b
  FROM gaps
  GROUP BY 1, 2, 3
)
SELECT store_id,
       extract(isodow FROM day) AS dow,
       slot,
       avg(lanes) AS open_lanes,
       avg(tx) AS tx,
       avg(service) AS service,
       avg(b2b) AS back_to_back
FROM per_day
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

“Each row is one store, one weekday and one quarter-hour of the day, in the store’s own time zone, averaged over eight weeks. Converting to local time before taking the date matters: in the database session’s time zone, a late sale can land on the wrong day, and the lag would compare a lane’s first sale of the morning with last night’s. A slot where nearly every transaction is back-to-back is a slot with a queue. That map of stores, weekdays and slots is the first deliverable.”

Schedule against demand. “I ask workforce management for their front-end forecast by slot and lay the published schedule over it. Where the map shows a queue and the schedule has fewer lanes than the forecast calls for, that is the gap. Then I ask why each gap exists: minimum shift lengths, a manager’s override, or breaks landing in the peak.”

Measuring real wait. “The proxy says a queue existed, not how long it was. So at one store I’d run a week of sampling at peaks: a front-end lead with a tally app taps when a sampled customer joins a line and when they reach the belt. That calibrates the proxy. With arrival and service rates per slot, an Erlang C estimate tells me roughly how the wait changes if the store opens an extra lane, which is the question the scheduling team will ask. Erlang C assumes one shared line, so it is optimistic for separate lanes. I use it to rank options and check it against the sampled waits, not to promise minutes.”

“Here’s the kind of number it gives. Say one store’s Saturday early-afternoon hour sees 150 customers at 2.4 minutes each. That’s 6 lanes’ worth of work. With 7 lanes open, the model says about 8% of customers wait more than 5 minutes; with 8, under 1%. So one extra lane in that hour, found by moving a start time, may be the whole fix, and it’s the same labor hours spent at a different time.”

Levers under the union contract. “Fixed shift lengths still leave start times, break placement and task assignment, and I’d confirm each with labor relations before promising anything. Stagger starts so the fourth register opens at the peak, not an hour before it. Move breaks out of the peak slots. Train two stockers per shift to open a lane when a front-end lead calls. Where the floor allows it, one shared line feeding several registers evens out the wait. And cut service time where the log shows it: if overrides wait for a supervisor, give the front-end lead that approval. Service time by item count and payment type tells me which lever is worth more at the peak: an express lane with an item cutoff, a bagger on the busiest lanes, or another register.”

Test. “One store changes start times and breaks for four weeks; a similar store does not. I take two weeks of before-data from both and compare the change in each, not the levels, with the same labor hours in both. I compare back-to-back share and sampled waits in peak slots, and watch shrink and out-of-stocks.”

Failure modes. “The query counts a lane as open only if it rang a sale, so an idle staffed lane is missed and a lane logged in at the start of a break looks open. I check both against time punches. And if the peak moves with the weather or paydays, a fixed schedule change will miss it, so the scheduling team gets the slot map every week, not once.”