Skip to content

yaswanthteja/SQL_Dannys_Data-Bank_CaseStudy4

Repository files navigation

Case Study #4: Data Bank

Image

📚 Table of Contents


Business Task

Danny launched a new initiative, Data Bank which runs banking activities and also acts as the world’s most secure distributed data storage platform!

Customers are allocated cloud data storage limits which are directly linked to how much money they have in their accounts.

The management team at Data Bank want to increase their total customer base - but also need some help tracking just how much data storage their customers will need.

This case study is all about calculating metrics, growth and helping the business analyse their data in a smart way to better forecast and plan for their future developments!

Entity Relationship Diagram

image

Table 1: Regions

This regions table contains the region_id and their respective region_name values.

image

Table 2: Customer Nodes

Customers are randomly distributed across the nodes according to their region. This random distribution changes frequently to reduce the risk of hackers getting into Data Bank’s system and stealing customer’s money and data!

image

Table 3: Customer Transactions

This table stores all customer deposits, withdrawals and purchases made using their Data Bank debit card.

image


Case Study Questions

A. Customer Nodes Exploration

View my solution here.

  1. How many unique nodes are there on the Data Bank system?
  2. What is the number of nodes per region?
  3. How many customers are allocated to each region?
  4. How many days on average are customers reallocated to a different node?
  5. What is the median, 80th and 95th percentile for this same reallocation days metric for each region?

B. Customer Transactions

View my solution here.

  1. What is the unique count and total amount for each transaction type?
  2. What is the average total historical deposit counts and amounts for all customers?
  3. For each month - how many Data Bank customers make more than 1 deposit and either 1 purchase or 1 withdrawal in a single month?
  4. What is the closing balance for each customer at the end of the month?
  5. Comparing the closing balance of a customer’s first month and the closing balance from their second nth, what percentage of customers:
  • Have a negative first month balance?
  • Have a positive first month balance?
  • Increase their opening month’s positive closing balance by more than 5% in the following month?
  • Reduce their opening month’s positive closing balance by more than 5% in the following month?
  • Move from a positive balance in the first month to a negative balance in the second month?

Do give me a 🌟 if you like what you're reading. Thank you! 🙆🏻‍♀️

About

No description, website, or topics provided.

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published