INTERACTIVE LECTURES
SQL functions & delivery evidence
Guided class activity · apply the ideas from the full SQL lessons.
Preparing for Lab 4 · After practice, try the team challenge.
Opening your saved work…
Sign in to save across devices → · Download your guest work before switching accounts.
Measure elapsed time with explicit units
Step 4 of 7 · Database: North Shore Deliveries
READ & UNDERSTAND
julianday converts a supported timestamp into a day number. Subtract the earlier value from the later value to get elapsed days. Multiply by 24 for hours or 1440 for minutes. Subtracting the original text strings does not calculate elapsed time.
ROUND is a presentation choice applied after the calculation. ABS removes the sign: it is useful when you want a magnitude, but can conceal that start and end were reversed. Preserve direction until you have checked the event sequence.
MIN(timestamp) and MAX(timestamp) identify the observed bounds in this consistently formatted snapshot. Their difference describes the scan window. It is not automatically total shipping time: events before the first scan or after the last scan are not observed.
Worked example
SELECT ROUND((julianday('2024-10-02 10:30:00') - julianday('2024-10-01 09:00:00')) * 24, 1) AS elapsed_hours;The literal example crosses midnight, so subtracting clock times alone would give the wrong interval. The date is part of the calculation.
In the practice, show both endpoints next to the duration. This makes the units and the source window reviewable.
Your question
Return MIN(timestamp) AS first_seen, MAX(timestamp) AS last_seen, and the elapsed hours between them rounded to 2 decimals AS observed_hours for package_id = 8.
Explain your evidence
What can you conclude from the observed hours, and what would be an overclaim about delivery speed?
Try a query to unlock the worked-answer discussion.
Query results
Predict the result, then run your query.Predict the result, then run your query.
Read-only activity · One statement per run · First 200 rows displayed · Checks compare the complete bounded result, not just this preview.
Explore this query freely in the playground ↗Check the save status above. Account-saved work is available in My Progress. Explanations are for discussion, not automatically graded. No prerequisite locks.