Question

Data Analyst – Pre-Interview Questions

 

Your Name: Seth Renda

 

Please write SQL queries to address each of the following 3 scenarios:

 

Scenario 1:  Marketing would like a list of individuals, 18 years or older, who are the primary owner of at least one open checking account.  Using the ERD on page 4, write a select query that returns:

 

·         MEMBER_NBR

·         FIRST_NAME

·         LAST_NAME

·         Member age

·         Count of checking accounts

·         Total balance of checking accounts

 

Assume the “Share” table holds deposit accounts, such as checking and savings.

Assume open checking accounts are identified with the field CLOSED, when its value is 0 (zero). 

Assume primary owners are identified with the field PARTICIPATION_TYPE when its value is 101.

Assume the Individual table contains a date field called BIRTH_DATE.

 

Query 1:

 

select MEMBER_NBR M, I.FIRST_NAME, I.LAST_NAME,I.DATEBIRTH_DATE,S.COUNT(CLOSED),S.SUM(BALANCE)

FROM Individual I

JOIN MembershipParticipant M

ON I.DRIVERS_LICENSE_NBR = M.INDIVIDUAL_ID

JOIN Share S

ON S.MEMBER_NBR = M.MEMBER_NBR

WHERE DATEDIFF(YEAR, I.BIRTH_DATE,SYSDATE) >= 18 AND S.CLOSED = 0 AND S.PARTICIPATION_TYPE = 101 AND S.ACCOUNT_TYPE = 2  ----(checking)

 

 

Scenario 2:  Accounting would like a list of members who have one or more open credit card accounts with a credit limit of $20,000 or more.  Using the ERD on page 4, write a select query that returns:

 

·         MEMBER_NBR

·         Count of credit card accounts

·         Total credit limit

·         Total credit card balances

·         Total available credit

·         Percent credit usage

 

Assume credit cards are identified with the field IS_CREDIT_CARD when its value is equal to 1. 

Assume open credit card accounts are identified with the field CLOSED, when its value is equal to 0 (zero). 

 

Scenario 3:  Consumer Loans would like to know how many members have an open checking account with balances between $1,000 and $2,000 and had closed a credit card loan in the past 6 months. Using the ERD on page 4, write a select query that returns:

 

·         Count of members per month

·         Month name

 

Assume the “Share” table holds deposit accounts, such as checking and savings.

Assume open checking accounts are identified with the field CLOSED, when its value is 0 (zero). 

Assume credit cards are identified with the field IS_CREDIT_CARD when its value is equal to 1. 

Assume CLOSED_DATE will have a value of the date in which the loan was closed.

 

> EDR is awful and I don't like to assume