Late Work Order Rate by Line

Medium
259.

Late Work Order Rate by Line

A work order is late when it was completed on a calendar day after its due_date; completing it any time on the due date itself is on time. Orders not yet completed (completed_at NULL) are left out of every number.

For every line with at least one completed order, return line_code, completed_orders, late_orders and late_pct — late orders as a percentage of completed orders, rounded to two decimals. Order by late_pct from highest to lowest, then by line_code.

Tables

Table: LineWorkOrder

ColumnType
work_order_idint
line_codevarchar
due_datedate
completed_atdatetime

Primary key: work_order_id.

completed_at is NULL while an order is open.

Example

The rows must come back in the order shown.

LineWorkOrder

work_order_idline_codedue_datecompleted_at
9001L1-ASSY2024-10-052024-10-05 22:40:00
9002L1-ASSY2024-10-072024-10-08 01:15:00
9003L1-ASSY2024-10-092024-10-08 16:00:00
9004L4-WELD2024-10-062024-10-10 09:30:00
9005L4-WELD2024-10-12NULL
9006L5-PACK2024-10-152024-10-14 11:00:00
9007L3-PAINT2024-10-20NULL

Output

line_codecompleted_orderslate_orderslate_pct
L4-WELD11100
L1-ASSY3133.33
L5-PACK100
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Late Work Order Rate by Line — SQL Dates Problem

A work order is late when it was completed on a calendar day after its due_date; completing it any time on the due date itself is on time.

  • Difficulty: Medium
  • Topics: Dates, Conditional Logic, Aggregation
  • Dialect: MySQL
  • Problem: #259

Problem statement

A work order is late when it was completed on a calendar day after its due_date; completing it any time on the due date itself is on time. Orders not yet completed (completed_at NULL) are left out of every number.

For every line with at least one completed order, return line_code, completed_orders, late_orders and late_pct — late orders as a percentage of completed orders, rounded to two decimals. Order by late_pct from highest to lowest, then by line_code.

Tables

Table: LineWorkOrder

ColumnType
work_order_idint
line_codevarchar
due_datedate
completed_atdatetime

Primary key: work_order_id.

completed_at is NULL while an order is open.

Examples

Example 1

LineWorkOrder

work_order_idline_codedue_datecompleted_at
9001L1-ASSY2024-10-052024-10-05 22:40:00
9002L1-ASSY2024-10-072024-10-08 01:15:00
9003L1-ASSY2024-10-092024-10-08 16:00:00
9004L4-WELD2024-10-062024-10-10 09:30:00
9005L4-WELD2024-10-12NULL
9006L5-PACK2024-10-152024-10-14 11:00:00
9007L3-PAINT2024-10-20NULL

Output

line_codecompleted_orderslate_orderslate_pct
L4-WELD11100
L1-ASSY3133.33
L5-PACK100

How to solve Late Work Order Rate by Line

The trap is the type mismatch. completed_at is a datetime and due_date a date; compared directly, '2024-10-05 22:40:00' > '2024-10-05' is true, so an order finished on its due date would be called late. Reduce the timestamp to its day with DATE(completed_at) before comparing, or use DATEDIFF(completed_at, due_date) > 0, which compares calendar days, or ask whether it finished at or after midnight of the next day.

With that settled it is conditional aggregation: drop open orders in WHERE, group by line, COUNT(*) the completed ones and SUM(CASE … THEN 1 ELSE 0 END) the late ones, and divide for the percentage, rounded to two decimals. A line whose orders are all open has no rows after the filter and is not listed, which also keeps the division away from zero.

The order is by the late rate, worst line first, ties alphabetical. One scan and one small aggregation.

Reference solution (MySQL)

SELECT line_code,
       COUNT(*) AS completed_orders,
       SUM(CASE WHEN DATE(completed_at) > due_date THEN 1 ELSE 0 END) AS late_orders,
       ROUND(100 * SUM(CASE WHEN DATE(completed_at) > due_date THEN 1 ELSE 0 END) / COUNT(*), 2) AS late_pct
FROM LineWorkOrder
WHERE completed_at IS NOT NULL
GROUP BY line_code
ORDER BY late_pct DESC, line_code

Another way

SELECT line_code, COUNT(completed_at) AS completed_orders,
       SUM(IF(DATEDIFF(completed_at, due_date) > 0, 1, 0)) AS late_orders,
       ROUND(100 * SUM(IF(DATEDIFF(completed_at, due_date) > 0, 1, 0)) / COUNT(completed_at), 2) AS late_pct
FROM LineWorkOrder GROUP BY line_code HAVING COUNT(completed_at) > 0
ORDER BY late_pct DESC, line_code

Another way

SELECT line_code, COUNT(*) AS completed_orders,
       SUM(CASE WHEN completed_at >= DATE_ADD(due_date, INTERVAL 1 DAY) THEN 1 ELSE 0 END) AS late_orders,
       ROUND(100 * SUM(CASE WHEN completed_at >= DATE_ADD(due_date, INTERVAL 1 DAY) THEN 1 ELSE 0 END) / COUNT(*), 2) AS late_pct
FROM LineWorkOrder WHERE completed_at IS NOT NULL GROUP BY line_code
ORDER BY late_pct DESC, line_code

← Top Operators per Shift by Good Output · Machines Due for Preventive Maintenance by Quarter End →