Case Study 1: Customer Query Patterns at Meridian Bank

Case Study 1: Customer Query Patterns at Meridian Bank

Background

Meridian National Bank has recently hired a new Junior Business Analyst, Priya Desai, who reports to the Hartford Main Street branch manager. Priya's first assignment is to produce a series of ad-hoc customer reports that the relationship management team has been requesting for weeks. The requests have been piling up because no one on the team is comfortable writing SQL, and the IT department has a two-week turnaround on report requests.

Priya has completed her DB2 training (she just finished Chapter 5 of a certain textbook) and is sitting down at her workstation with access to the MERIDIAN database. Her manager, Margaret Chen, has given her a list of five questions that need answers by end of day.

This case study walks through Priya's process of translating business questions into SQL queries, examining the results, and refining her approach when the first attempt does not quite answer the question.

Question 1: "Who are our long-tenured customers in Connecticut?"

Margaret wants to send a thank-you letter to every customer who has been with the bank for more than 15 years and is based in Connecticut.

Priya's first attempt:

SELECT FIRST_NAME, LAST_NAME, CUSTOMER_SINCE
FROM MERIDIAN.CUSTOMERS
WHERE STATE_CODE = 'CT'
  AND CUSTOMER_SINCE < '2011-01-01';

This works, but it hardcodes the date. If someone runs this report next year, the threshold shifts. Priya revises to use date arithmetic:

SELECT CUSTOMER_ID,
       FIRST_NAME || ' ' || COALESCE(MIDDLE_NAME || ' ', '') || LAST_NAME AS FULL_NAME,
       ADDRESS_LINE1,
       CITY || ', ' || STATE_CODE || ' ' || ZIP_CODE AS FULL_ADDRESS,
       CUSTOMER_SINCE,
       YEAR(CURRENT DATE) - YEAR(CUSTOMER_SINCE) AS YEARS_WITH_BANK
FROM MERIDIAN.CUSTOMERS
WHERE STATE_CODE = 'CT'
  AND CUSTOMER_TYPE = 'I'
  AND YEAR(CURRENT DATE) - YEAR(CUSTOMER_SINCE) > 15
ORDER BY CUSTOMER_SINCE;

Result:

CUSTOMER_ID  FULL_NAME                  FULL_ADDRESS                          CUSTOMER_SINCE  YEARS_WITH_BANK
-----------  -------------------------  ------------------------------------  --------------  ---------------
     100000  Alice Marie Thornton       42 Oak Lane, Hartford, CT 06103      1998-06-15               28
     100003  Daniel James Fitzgerald    315 Prospect Street, Hartford, ...   1999-11-01               27
     100001  Benjamin Hartwell          189 Elm Street, Hartford, CT 06105   2001-03-20               25
     100015  Maria Teresa Gonzalez      56 Farmington Ave, Hartford, ...     2002-04-15               24

Key lesson: Priya noticed she needed to exclude business customers (Hartford Coffee Roasters) from the thank-you letter list, so she added CUSTOMER_TYPE = 'I'. She also used COALESCE to handle the NULL middle name for Benjamin Hartwell. Finally, she made the date calculation dynamic so the report remains correct over time.

Question 2: "Which accounts might need attention?"

The operations team defines an "attention" account as one where: - The balance is below the minimum balance for the account type, OR - There has been no activity in the last 60 days

Priya realizes this question requires data from two tables — ACCOUNTS and ACCOUNT_TYPES — to compare the balance against the minimum. She writes a WHERE-join (she has not yet learned JOIN syntax):

SELECT A.ACCOUNT_NUMBER,
       A.CURRENT_BALANCE,
       T.MIN_BALANCE,
       A.LAST_ACTIVITY_DATE,
       CASE
           WHEN A.CURRENT_BALANCE < T.MIN_BALANCE THEN 'Below Minimum'
           WHEN A.LAST_ACTIVITY_DATE < CURRENT DATE - 60 DAYS THEN 'Inactive'
           WHEN A.LAST_ACTIVITY_DATE IS NULL THEN 'No Activity Recorded'
           ELSE 'Unknown Flag'
       END AS ATTENTION_REASON
FROM MERIDIAN.ACCOUNTS A,
     MERIDIAN.ACCOUNT_TYPES T
WHERE A.ACCOUNT_TYPE_CODE = T.ACCOUNT_TYPE_CODE
  AND A.STATUS = 'A'
  AND (A.CURRENT_BALANCE < T.MIN_BALANCE
       OR A.LAST_ACTIVITY_DATE < CURRENT DATE - 60 DAYS
       OR A.LAST_ACTIVITY_DATE IS NULL)
ORDER BY ATTENTION_REASON, A.CURRENT_BALANCE;

Key lesson: The NULL handling for LAST_ACTIVITY_DATE is critical. Without the OR A.LAST_ACTIVITY_DATE IS NULL clause, accounts with no recorded activity would be silently excluded. Priya caught this because she remembered the three-valued logic discussion: NULL < CURRENT DATE - 60 DAYS evaluates to UNKNOWN, not TRUE. She also used a CASE expression to make the reason for each flag human-readable.

Question 3: "Can you give me a formatted customer directory?"

The marketing team wants a printed directory of all customers, formatted nicely, for use at a conference. They want: - Name in "Last, First M." format (with middle initial if available) - City and state - Phone number - Customer type labeled as "Personal" or "Commercial"

SELECT LAST_NAME || ', ' || FIRST_NAME ||
       CASE
           WHEN MIDDLE_NAME IS NOT NULL
           THEN ' ' || SUBSTR(MIDDLE_NAME, 1, 1) || '.'
           ELSE ''
       END AS DIRECTORY_NAME,
       CITY || ', ' || STATE_CODE AS LOCATION,
       COALESCE(PHONE_PRIMARY, 'Unlisted') AS PHONE,
       CASE CUSTOMER_TYPE
           WHEN 'I' THEN 'Personal'
           WHEN 'B' THEN 'Commercial'
           ELSE 'Other'
       END AS ACCT_TYPE
FROM MERIDIAN.CUSTOMERS
WHERE STATUS = 'A'
ORDER BY LAST_NAME, FIRST_NAME;

Result (first six rows):

DIRECTORY_NAME         LOCATION           PHONE         ACCT_TYPE
---------------------  -----------------  ------------  ----------
Blackwood, Nathan      Boston, MA         617-555-1017  Personal
Chambers, Olivia J.    New York, NY       212-555-1018  Personal
Coffee Roasters, ...   Hartford, CT       860-555-1005  Commercial
Design Group, ...      New York, NY       212-555-1013  Commercial
Dominguez, Carmen L.   West Hartford, CT  860-555-1003  Personal
Fitzgerald, Daniel J.  Hartford, CT       860-555-1004  Personal

Key lesson: The CASE expression inside the concatenation elegantly handles the middle initial. When MIDDLE_NAME is NULL, the CASE returns an empty string, and the name formats cleanly without an extra space. Priya used SUBSTR(MIDDLE_NAME, 1, 1) to extract just the initial, then appended a period.

She noticed an issue with the business customers: "Coffee Roasters, Hartford" does not read correctly because FIRST_NAME contains "Hartford" and LAST_NAME contains "Coffee Roasters." This is a data modeling discussion — business names are being stored in individual-name fields. She makes a note to discuss this with the database team but delivers the report as-is with a caveat.

Question 4: "What does our transaction channel mix look like?"

Margaret wants to understand how customers are banking — branch, ATM, online, mobile? Priya needs aggregate functions (which she has not formally studied), but she starts with what she knows:

SELECT DISTINCT CHANNEL
FROM MERIDIAN.TRANSACTIONS
ORDER BY CHANNEL;

Result:

CHANNEL
--------
ACH
ATM
BRANCH
CHECK
MOBILE
ONLINE
SYSTEM

Seven distinct channels. She then writes individual queries to explore each one:

-- How many ATM transactions, and what is the typical amount?
SELECT TRANSACTION_DATE,
       AMOUNT,
       DESCRIPTION,
       ACCOUNT_ID
FROM MERIDIAN.TRANSACTIONS
WHERE CHANNEL = 'ATM'
ORDER BY TRANSACTION_DATE DESC;

She notes that all ATM transactions are withdrawals (negative amounts) ranging from $40 to $500. She writes a CASE-based summary:

SELECT CHANNEL,
       CASE
           WHEN ABS(AMOUNT) < 100 THEN 'Under $100'
           WHEN ABS(AMOUNT) < 500 THEN '$100-$499'
           WHEN ABS(AMOUNT) < 1000 THEN '$500-$999'
           ELSE '$1,000+'
       END AS AMOUNT_RANGE,
       TRANSACTION_TYPE,
       AMOUNT
FROM MERIDIAN.TRANSACTIONS
WHERE CHANNEL IN ('ATM', 'MOBILE', 'ONLINE')
ORDER BY CHANNEL, ABS(AMOUNT) DESC;

Key lesson: Even without aggregate functions, Priya was able to explore the data meaningfully using DISTINCT, CASE, and careful filtering. She documented her findings and noted that a full channel analysis with counts and averages would require GROUP BY (coming in Chapter 7).

Question 5: "Who are our highest-value customers?"

Margaret's final request: identify the bank's top 5 customers by total deposits across all their accounts. Priya knows this needs aggregation across multiple accounts per customer, which she has not yet learned. But she can approximate:

-- Start simple: top accounts by balance
SELECT A.ACCOUNT_NUMBER,
       A.CUSTOMER_ID,
       A.CURRENT_BALANCE,
       A.ACCOUNT_TYPE_CODE
FROM MERIDIAN.ACCOUNTS A
WHERE A.STATUS = 'A'
ORDER BY A.CURRENT_BALANCE DESC
FETCH FIRST 10 ROWS ONLY;

She sees that customers 100009 (Henry Nakamura), 100006 (Franklin Wu), and 100011 (Jack Pemberton) each appear multiple times. She cannot sum across accounts yet, but she presents her findings:

"The highest individual account balance is $750,000 (Henry Nakamura, MMA). Customers Nakamura, Wu, and Pemberton each hold multiple high-value accounts and likely represent our largest total relationships. I will produce exact totals once I learn GROUP BY and SUM."

Key lesson: Priya knows the limits of her current SQL knowledge and communicates them clearly instead of guessing. This is a professional practice that builds trust.

Reflection

Priya completed four out of five requests fully and provided a useful partial answer for the fifth. Along the way, she encountered and resolved several common issues:

Hardcoded dates — She replaced static dates with dynamic date arithmetic so reports stay accurate over time.
NULL handling — She used COALESCE for display formatting and IS NULL in WHERE clauses to avoid silently excluding rows.
Business vs. individual customers — She discovered that the data model stores business names awkwardly in individual-name fields and flagged this for the team.
Knowing her limits — She recognized when a question required features she had not yet learned (aggregation) and said so clearly.

These are the patterns of a skilled SQL practitioner. The syntax is the easy part — the judgment about when and how to apply it is what separates good analysts from great ones.

Discussion Questions

In Question 2, what would happen if Priya omitted the A.LAST_ACTIVITY_DATE IS NULL condition? How many accounts might be silently excluded?
The business customer names (Hartford Coffee Roasters, Boston Tech Solutions, Manhattan Design Group) do not fit neatly into FIRST_NAME/LAST_NAME fields. What design alternatives could the database team consider?
Priya used YEAR(CURRENT DATE) - YEAR(CUSTOMER_SINCE) for tenure. This is approximate — a customer who joined on December 31, 2010 would show 16 years of tenure on January 1, 2026, even though it has been barely over 15 years. How could she make this calculation more precise?
For the channel analysis (Question 4), what additional information would COUNT and AVG provide that Priya's approach could not deliver? What business decisions might depend on those numbers?

Case Study 1 for Chapter 5 of "IBM DB2: From First Query to Enterprise Architecture"