SQL COURSE PROBLEM #4

SQL Crash Course - Managing Financial Services Database

Josep Ferrer

and

Cornellius Yudha Wijaya

Jun 06, 2025

Cross-post from DataBites

Don't hesitate to answer this SQL Problems! -

Cornellius Yudha Wijaya

All the course material is stored in the SQL Crash Course repository.

Hi everyone! Josep and Cornellius Yudha Wijaya from Non-Brand Data here 👋🏻

As promised, today we are publishing the next two issues of our SQL Crash Course – From Zero to Hero! 🚀

I am sure you are here to continue our SQL Crash Course Journey!📚

If this is your first time or you’ve forgotten what we’ll cover, we have examined seven key SQL topics, each divided into multiple posts across Non-Brand Data and DataBites and now we are having some practice!

This week, we will continue to apply our SQL skills to real-world problems.

Last week, Cornellius and I sent you a simple problem that will test your knowledge using everything we have learned in our previous SQL Crash Courses.

If you miss them, here is the SQL problem you might miss with answers (try to solve it yourself first):

👉SQL COURSE PROBLEM #1 (Answer)

👉SQL COURSE PROBLEM #2 (Answer)

❓ Context

You manage customer relationships at a growing financial institution offering accounts, loans, and credit products. Customers hold multiple account types, conduct transactions, and use various credit services.

As a data analyst, you'll use SQL to:

Analyze account balances and transaction patterns
Identify high-risk loans and credit relationships
Evaluate customer financial health
Generate reports for management

🧱 Database Schema

Sample data contains real financial records:

6 customers with credit scores
8 accounts (checking/savings/credit)
10 transactions (deposits/withdrawals/payments)
5 loans (mortgage/auto/personal/business)
5 credit cards with limits and fees

You can check the schema in the following SQL playground.

🧩 Tasks

Use SQL to answer the following questions:

1. Top Performing Customers

List customers whose total account balance is above the average across all customers.

Hint: Aggregate balances per customer and compare to a subquery with the overall average.
Keywords: GROUP BY, HAVING, AVG(), subquery, JOIN

2. Active Loans with Risk

Find customers with loans marked as 'delinquent' or 'default', and a credit score below 700.

Hint: Filter using multiple conditions across joined tables.
Keywords: JOIN, WHERE, AND, OR, comparison

3. Spending Rank by Customer

Calculate total spending per customer (sum of withdrawals and payments).
Rank customers by spending within their country (assume you’ve extended customers with a country field).

Hint: Use a CTE and window function.
Keywords: WITH, CASE WHEN, SUM(), RANK(), PARTITION BY, JOIN

4. Account Growth Over Time

Show the number of accounts opened per month since 2020 and cumulative growth over time.

Hint: Use date functions + windowed aggregation.
Keywords: DATE_FORMAT, GROUP BY, SUM() OVER(), ORDER BY

5. Generate a Date Series for 2023

Generate each date in 2023 using a recursive CTE. Count how many transactions happened on each date.

Hint: Recursive date generator + LEFT JOIN.
Keywords: RECURSIVE, CTE, LEFT JOIN, DATE, COUNT()

6. Loan Maturity Tracker

Generate a row for each month between a loan’s start and end date. Add a column for remaining term.

Hint: Recursively build monthly intervals per loan.
Keywords: RECURSIVE, UNION ALL, INTERVAL, DATEDIFF(), JOIN

7. Create a View for High-Value Transactions

Create a view big_txns with all transactions over €10,000. Then query it to count how many each account had.

Hint: CREATE VIEW + GROUP BY.
Keywords: CREATE VIEW, WHERE, GROUP BY, COUNT(), JOIN

8. Segment Revenue by Credit Score Tier

You’ll group customers into tiers (Excellent, Good, Fair, Poor) based on their credit_score, calculate the total annual fees per tier, and compute each tier’s percentage of the overall revenue.

Hint: Use a CASE statement for classification, then aggregate and normalize using two CTEs.
Keywords: CASE, WITH, JOIN, SUM(), GROUP BY, percentage, ROUND(), segmentation

9. Aggregation Filter Fix

Here’s a classic SQL mistake. Why does this query fail?

SELECT account_type, COUNT(*) AS num_accounts
FROM accounts
WHERE num_accounts > 5
GROUP BY account_type;

📥 How to submit?

Open the shared SQL playground from earlier and make a copy to work on your answers.
Once you're done, leave a comment below to confirm your participation and share the link to your playground.
Feel free to share your thoughts or questions, too!

Let’s make this week count 🔥

How to Get Started 🚀

Over the coming weeks, we’ll guide you through:

✅ SQL Fundamentals
✅ Intermediate SQL
✅ Advanced SQL
✅ Database Operations
✅ Writing Efficient Queries

Once you grasp the basics, practice is key! Start working on real-world projects and solving hands-on data problems.

What’s Next? ➡️

This is the first of many posts about the upcoming SQL Courses. It will only explain what SQL is in its crude form.

To get the full experience and fully immersed in the learning:

👉 Subscribe to Databites.tech (By Josep)

👉 Subscribe to Non-Brand Data (By Cornellius)

👉 Check out the SQL Crash Course GitHub repo

👉 Share with your friend and whoever needs it!

🗓️ Every Thursday, you will have two new issues in your inbox!

Let’s dive in and make SQL less scary, more fun, and way more useful! 🚀

Josep & Cornellius