Drill 7 of 12 in Logistics questions
0

Interview Question Bank · EXTRACT(HOUR) · RANK per group · ties

7. Busiest pick hour per warehouse

MediumLogisticsQuestion #163

Concept: EXTRACT(HOUR) · RANK per group · tiesshow

EXTRACT(HOUR FROM ts) gives the hour of day (0-23), so the same hour on different days falls in one bucket; date_trunc('hour', ts) keeps the date and makes a separate bucket for every day. To keep the top row in each group, rank within the group with RANK() OVER (PARTITION BY group ORDER BY count DESC) and keep rank 1. RANK gives tied rows the same rank, so a tie for first shows every winner, while ROW_NUMBER keeps one at random.

Example (different data):

WITH h AS (
  SELECT branch, EXTRACT(HOUR FROM paid_at) AS hr, COUNT(*) AS sales
  FROM till_sales GROUP BY 1, 2
)
SELECT branch, hr, sales
FROM (SELECT *, RANK() OVER (PARTITION BY branch ORDER BY sales DESC) AS r FROM h) x
WHERE r = 1;

Your task

Larkspur Freight is planning shift patterns and wants to know when each warehouse's pickers are busiest. Only picks matter; inbound receipts are handled by a separate dock team.

From lg_inventory_moves, count picks (kind 'pick') per warehouse and hour of day (0-23) across all days. Return warehouse (code), hour and picks for each warehouse's busiest hour, keeping every hour that ties for first. Sort by warehouse, then hour.

Starting Postgres in your browser…

Checking your access…