Ravindra BagaleCourses & study guides Track your progress

9. Date and Time Queries for Analysts

9.5 Practice: Monthly Totals and Late Cheque Clearing

By Last updated:

Why. This practice joins two everyday bank questions: how much moved each month, and which cheques cleared late.

What. You group by branch and month for totals. Then you use julianday() to find cheques that took more than 3 days. Each branch total stays in its own currency, so you never add London pounds to Mumbai rupees.

How. Run one monthly query for the two Indian branches, and one query for late cheques.

Steps

  1. Run the first example to get monthly totals for branch 3 (Mumbai) and branch 4 (Pune), both in INR.
  2. Read one row per branch per month.
  3. Run the second example to list cheques that took more than 3 days.
  4. Write a one-line summary for each result in your notes.

Examples

Example 1 · dataset: northstar_bank

SELECT branch_id,
       strftime('%Y-%m', posted_at) AS month,
       SUM(amount)                  AS total_inr
FROM transactions
WHERE branch_id IN (3, 4)
GROUP BY branch_id, month
ORDER BY branch_id, month;

Output

branch_idmonthtotal_inr
32026-08192000.0
32026-09276000.0
32026-10657000.0
42026-0818000.0
42026-09166000.0
42026-1098000.3

Meaning. Mumbai grows from about 1.9 lakh rupees in August to 6.57 lakh in October. Pune has its biggest month in September.

Example 2 · dataset: northstar_bank

SELECT txn_id, branch_id, date(posted_at) AS posted_date, cleared_on,
       julianday(cleared_on) - julianday(date(posted_at)) AS days_to_clear
FROM transactions
WHERE txn_type = 'cheque'
  AND julianday(cleared_on) - julianday(date(posted_at)) > 3
ORDER BY days_to_clear DESC;

Output

txn_idbranch_idposted_datecleared_ondays_to_clear
422026-08-282026-09-025.0
912026-09-142026-09-184.0
1332026-09-252026-09-294.0

Meaning. Three cheques took more than 3 days, and the slowest took 5 days. Cheques with no clearing date are not listed, because a difference with NULL is NULL, not a number.

Ravindra Bagale's Tip

When you report late cheques, also report the ones not cleared yet with cleared_on IS NULL. They may be the most urgent.