Calculate Average Rate and Cumulative Jobs by State

jobs

+--------+---------+-----------+------------+----------------+-------+----------------+---------------+
| job_id | load_id | driver_id | status     | delivered_date | miles | payment_issued | rate_per_mile |
+--------+---------+-----------+------------+----------------+-------+----------------+---------------+
| 1      | 101     | 501       | completed  | 2023-07-01     | 450   | true           | 2.2           |
| 2      | 102     | 502       | cancelled  | 2023-07-03     | 0     | false          | 0             |
| 3      | 103     | 501       | completed  | 2023-07-04     | 600   | true           | 1.9           |
+--------+---------+-----------+------------+----------------+-------+----------------+---------------+

loads

+---------+--------------+-------------------+--------------+
| load_id | origin_state | destination_state | created_date |
+---------+--------------+-------------------+--------------+
| 101     | CA           | TX                | 2023-06-25   |
| 102     | NV           | AZ                | 2023-06-26   |
| 103     | OR           | WA                | 2023-06-27   |
+---------+--------------+-------------------+--------------+

drivers

+-----------+-----------+-----------+-------+
| driver_id | name      | hire_date | state |
+-----------+-----------+-----------+-------+
| 501       | Alice     | 2022-01-10| CA    |
| 502       | Bob       | 2021-11-05| NV    |
+-----------+-----------+-----------+-------+

Scenario

SQL screen on logistics data – tables jobs, loads, drivers for a trucking marketplace.

Question

Among the five states that appear most often in the loads table, which state has the highest count of DISTINCT completed jobs? For completed jobs, calculate the average rate_per_mile for each delivered_date (exclude rows where miles = 0). Produce a running cumulative count of completed jobs by delivered_date using a window function (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

Hints

Join jobs ↔ loads, filter status = 'completed', be careful with DISTINCT and division by zero, and use window functions correctly.

Top Load State Completed Jobs and Daily Metrics

You are given three tables for a trucking marketplace: jobs, loads, and drivers.

1. Among the five states that appear most often in the loads table (considering both origin_state and destination_state), find the single state that has the highest count of DISTINCT completed jobs associated with it. If there is a tie, pick the alphabetically smallest state.

2. For that winning state, and only for jobs with status = 'completed', calculate the following per delivered_date:

   • The average rate_per_mile, excluding rows where miles = 0 (these rows should not contribute to the average).
   • The number of completed jobs that day (COUNT of DISTINCT job_id for that day).
   • A running cumulative count of completed jobs over time, ordered by delivered_date, using a window function with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Return one row per delivered_date for the winning state, including the state, delivered_date, avg_rate_per_mile, daily_completed_jobs, and cumulative_completed_jobs.

Table jobs

column	type	constraints
job_id	INTEGER	PRIMARY KEY, NOT NULL
load_id	INTEGER	NOT NULL, FOREIGN KEY -> loads.load_id
driver_id	INTEGER	NOT NULL, FOREIGN KEY -> drivers.driver_id
status	VARCHAR(20)	NOT NULL
delivered_date	DATE
miles	INTEGER	NOT NULL
payment_issued	BOOLEAN	NOT NULL
rate_per_mile	DECIMAL(5,2)	NOT NULL

Sample data:

delivered_date	driver_id	job_id	load_id	miles	payment_issued	rate_per_mile	status
2023-07-01	501	1	101	450	true	2.2	completed
2023-07-03	502	2	102	0	false	0	cancelled
2023-07-04	501	3	103	600	true	1.9	completed

Table loads

column	type	constraints
load_id	INTEGER	PRIMARY KEY, NOT NULL
origin_state	CHAR(2)	NOT NULL
destination_state	CHAR(2)	NOT NULL
created_date	DATE	NOT NULL

Sample data:

created_date	destination_state	load_id	origin_state
2023-06-25	TX	101	CA
2023-06-26	AZ	102	NV
2023-06-27	WA	103	OR

Table drivers

column	type	constraints
driver_id	INTEGER	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL
hire_date	DATE	NOT NULL
state	CHAR(2)	NOT NULL

Sample data:

driver_id	hire_date	name	state
501	2022-01-10	Alice	CA
502	2021-11-05	Bob	NV

Expected output

Return per-day metrics for the single 'winning' state: the state among the top-5 most frequent states in the loads table (considering both origin_state and destination_state) that has the highest DISTINCT completed job count; ties are broken alphabetically. For that state and for each delivered_date with completed jobs, show the state, the average rate_per_mile (excluding rows where miles = 0), the number of completed jobs that day (COUNT DISTINCT job_id), and the running cumulative count of completed jobs over time ordered by delivered_date.

avg_rate_per_mile	cumulative_completed_jobs	daily_completed_jobs	delivered_date	state
2.2	1	1	2023-07-01	CA

Hints

1. Treat both origin_state and destination_state as contributing to state frequency and association with jobs (e.g., via an unpivot/UNION ALL).
2. First find the top 5 most frequent states in loads, then restrict all later computations to those states.
3. Join jobs to loads and filter status = 'completed' before aggregating.
4. Use COUNT(DISTINCT job_id) when aggregating by state to avoid double-counting a job when origin_state = destination_state.
5. Exclude rows with miles = 0 only from the AVG(rate_per_mile) calculation, not from the completed job counts.
6. Compute the running total with a window function such as SUM(daily_completed_jobs) OVER (ORDER BY delivered_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).