Lab · SQL and Databases
Lab: where did my UPI money go?
The brief
Two months of UPI payments from a payments app. Work out the headline numbers, the spend per category, the shops you pay again and again, and a month-by-month summary that also counts failed payments.
Why it matters
Budgeting apps, bank statements and finance teams all show the same thing: totals, a split by category and the repeat payees. It is GROUP BY and HAVING, and the trap is always the same: forgetting to leave out failed payments.
Skills you practise
- COUNT, SUM, AVG, MAX
- GROUP BY
- HAVING versus WHERE
- conditional aggregation with CASE
- strftime
What you will have at the end
Four queries that make a personal UPI spending report: headline numbers, categories, regular payees and a monthly summary.
How it is marked (rubric, 100 points)
| Correct resultsAll four checks pass; failed and pending payments never count as spending. | 50 |
|---|---|
| WHERE, GROUP BY and HAVING in the right placesRows are filtered with WHERE, groups with HAVING, and every selected column is grouped or aggregated. | 30 |
| ExplainedYou can explain the difference between WHERE and HAVING using the BESCOM example. | 20 |
0/4 steps done
Step 1 of 4
The headline numbers
01-headline.sql returns one row: the number of successful payments, their total, their average rounded to 2 places, and the biggest one.
Aggregate functions turn many rows into one: COUNT(*) counts rows, SUM adds, AVG averages and MAX finds the largest. A FAILED or PENDING payment never left your account, so keep only status = 'SUCCESS' with WHERE. WHERE runs before the aggregates, so they only see the rows it keeps.
Run shows what your code prints. Check runs the step's tests. Ctrl+Enter checks too.