Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT avg(n_hours/n_slots) FROM
- (
- SELECT courier_id, COUNT(*) n_hours, SUM(CASE WHEN COALESCE(test > INTERVAL '1 hour', TRUE) THEN 1 ELSE 0 END) n_slots FROM (
- SELECT slot_time, courier_id,
- (slot_time - (lag(slot_time,1) OVER (partition BY courier_id ORDER BY slot_time))) AS test
- FROM scheduled_slots
- ) a
- GROUP BY courier_id) a
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement