Advertisement
Guest User

Untitled

a guest
Oct 22nd, 2018
82
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 0.33 KB | None | 0 0
  1. SELECT avg(n_hours/n_slots) FROM
  2. (
  3. SELECT courier_id, COUNT(*) n_hours, SUM(CASE WHEN COALESCE(test > INTERVAL '1 hour', TRUE) THEN 1 ELSE 0 END) n_slots FROM (
  4. SELECT slot_time, courier_id,
  5. (slot_time - (lag(slot_time,1) OVER (partition BY courier_id ORDER BY slot_time))) AS test
  6. FROM scheduled_slots
  7. ) a
  8. GROUP BY courier_id) a
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement