-- Simplified business hours calculator (9am-6pm weekdays)
WITH params AS (
  SELECT
    order_date AS start_ts,
    shipped_at AS end_ts,
    EXTRACT(EPOCH FROM (shipped_at - order_date)) / 3600 AS total_hours
  FROM orders
  WHERE shipped_at IS NOT NULL
)
SELECT
  start_ts,
  end_ts,
  ROUND(total_hours, 2) AS calendar_hours,
  -- Approximate: subtract nights (15h) and weekends
  ROUND(total_hours
    - (FLOOR(total_hours / 24) * 15) -- subtract non-business hours per day
    - (FLOOR(total_hours / 168) * 48) -- subtract weekends per week
  , 2) AS approx_business_hours
FROM params;