Uber's driver activity reporting needs daily totals even when an Uber trip starts on one calendar day and ends on another. Trip timestamps are stored in UTC. Write a PostgreSQL query that calculates driver time for each day from 2024-01-01 through 2024-01-04, inclusive.
Requirements
- Split every valid trip at midnight boundaries, so each calendar day receives only the time occurring on that day.
- Clip trips to the reporting period and exclude trips with missing timestamps, nonpositive duration, no matching driver, or no overlap with the period.
- Return one row per driver and active date, with total time in hours rounded to two decimal places. Sort by driver name and activity date.