anurupa19 / credit_card_case_study

SQL Case Study: Credit Card

Here is a fantastic Data Analyst case study to check SQL knowledge.

Project Description

This dataset contains insights into a collection of credit card transactions made in India, offering a comprehensive look at the spending habits of Indians across the nation. From the Gender and Card type used to carry out each transaction, to which city saw the highest amount of spending and even what kind of expenses were made, this dataset paints an overall picture about how money is being spent in India today.

Download credit card transactions dataset from below link: https://lnkd.in/d6ZTY2fQ

To successfully answer all the questions you should have been exposed to the following areas of SQL:

1. Date functions: DATEDIFF, DATEPART, DATEADD
2. Case When Statements
3. Aggregate functions with CASE WHEN, GROUP BY, ORDER BY
4. Joins
5. Window functions(DENSE_RANK, LEAD, LAG)
6. CTE(common table expression)

🎯 Project Objective: The main objective of this case study is to apply various SQL concepts and techniques to answer insightful questions about Credit Card spend habits. Some of the key objectives include:

1. Write a query to print top 5 cities with highest spends and their percentage contribution of total credit card spends
2. Write a query to print highest spend month and amount spent in that month for each card type
3. Write a query to print the transaction details(all columns from the table) for each card type when it reaches a cumulative of 1000000 total spends(We should have 4 rows in the o/p one for each card type)
4. Write a query to find city which had lowest percentage spend for gold card type
5. Write a query to print 3 columns: city, highest_expense_type , lowest_expense_type (example format : Delhi , bills, Fuel)
6. Write a query to find percentage contribution of spends by females for each expense type
7. Which card and expense type combination saw highest month over month growth in Jan-2014
8. During weekends which city has highest total spend to total no of transcations ratio
9. Which city took least number of days to reach its 500th transaction after first transaction in that city

Working on this case study has been an amazing experience, allowing me to dive deep into analyzing credit card spending patterns in India. By leveraging SQL I was able to extract valuable insights and draw meaningful conclusions.