Warehouse productivity

1-- core.shiphero.warehouse_productivity
2-- Picks and packs per warehouse user per day, ShipHero's "picks per day" and
3-- "packs per day" reports side by side.
4-- Grain: day x shiphero_account x warehouse x user.
5-- Depends on: staging.shiphero.pick_per_day, .pack_per_day
6--
7-- Picks and packs are aggregated separately and then full-outer-joined, so a user
8-- who only packed on a day still has a row, with zero picks.
9--
10-- NOT ON A SPINE, deliberately. A user with no picks or packs on a day was usually
11-- not on shift, and a row of zeros would drag down any per-user average. Sum over
12-- the window you want in BI.
13--
14-- Units per hour cannot be computed: the logs carry event times, not shift hours.
15-- first_activity_at and last_activity_at bound the working span per user per day
16-- as a rough proxy.
17--
18-- The warehouse and order ids in these logs are not joined to shipments or orders
19-- here; the pick and pack logs are reported on their own.
20
21WITH picks AS (
22    SELECT
23        DATE(picked_at) AS date,
24        shiphero_account,
25        warehouse_id,
26        user_id,
27        ANY_VALUE(user_name)       AS user_name,
28        COUNT(*)                   AS picks,
29        SUM(quantity_picked)       AS units_picked,
30        COUNT(DISTINCT order_id)   AS orders_picked,
31        MIN(picked_at)             AS first_pick_at,
32        MAX(picked_at)             AS last_pick_at
33    FROM {{staging.shiphero.pick_per_day}}
34    WHERE picked_at IS NOT NULL
35    GROUP BY 1, 2, 3, 4
36),
37
38packs AS (
39    SELECT
40        DATE(packed_at) AS date,