INTERACTIVE LECTURES
Answer questions across tables
Guided class activity · apply the ideas from the full SQL lessons.
Open the detailed lessons for this activity
Detailed lessons in this module
Open the full explanations, examples, and SQL exercises directly.
Preparing for Lab 8 · After practice, try the team challenge.
Opening your saved work…
Sign in to save across devices → · Download your guest work before switching accounts.
Classify return status and choose a defensible denominator
Step 12 of 12 · Database: Twin Ports Tool Library
READ & UNDERSTAND
The official snapshot is the close of business on 2026-09-30. A NULL return_date means still out. A completed return is late when return_date > due_date. Check NULL first, then lateness, then the remaining returned-on-time category.
For a late-return rate, the denominator is returned loans, not every loan. Still-out loans have no observed return outcome. A branch with no returned loans has an undefined rate; NULLIF protects the division and preserves that distinction from a zero late-return rate.
After a LEFT JOIN, also distinguish an unmatched loan placeholder from an actual loan with NULL return_date. COUNT(CASE WHEN l.id IS NOT NULL AND l.return_date IS NULL THEN 1 END) counts actual open loans. CASE order and the left-join gap test protect different meanings.
Worked example
SELECT l.id AS loan_id, CASE WHEN l.return_date IS NULL THEN 'Still out' WHEN l.return_date > l.due_date THEN 'Returned late' ELSE 'Returned on time' END AS loan_status FROM loans AS l ORDER BY l.id LIMIT 5;
The first branch preserves unfinished outcomes. Only actual returns can reach the late/on-time split.
Keep numerator and denominator next to the percentage. A tidy percentage alone hides the evidence needed to interpret it.
Your question
For loans checked out by staff_id = 3, return returned_loans, late_returns, and late_rate_pct rounded to 1 decimal. Count only returned loans in the denominator; express the percentage using 100.0 and NULLIF.
Explain your evidence
Why does a rate with no returned loans differ from 0%, and why should still-out loans not be silently called returned on time?
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.