Data Bank SQL Challenge full questions and answers for A,B, C, D Challenge (SQL 8 Week Challenges)
Link for original challenge source: https://8weeksqlchallenge.com/case-study-4/
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!
Table 1: Regions
This regions table contains the region_id and their respective region_name values.
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!
Table 3: Customer Transactions
This table stores all customer deposits, withdrawals and purchases made using their Data Bank debit card.
View my solution here.
- How many unique nodes are there on the Data Bank system?
- What is the number of nodes per region?
- How many customers are allocated to each region?
- How many days on average are customers reallocated to a different node?
- What is the median, 80th and 95th percentile for this same reallocation days metric for each region?
View my solution here.
- What is the unique count and total amount for each transaction type?
- What is the average total historical deposit counts and amounts for all customers?
- For each month - how many Data Bank customers make more than 1 deposit and either 1 purchase or 1 withdrawal in a single month?
- What is the closing balance for each customer at the end of the month?
- 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?
View my solution here.
To test out a few different hypotheses - the Data Bank team wants to run an experiment where different groups of customers would be allocated data using 3 different options:
Option 1: data is allocated based off the amount of money at the end of the previous month Option 2: data is allocated on the average amount of money kept in the account in the previous 30 days Option 3: data is updated real-time For this multi-part challenge question - you have been requested to generate the following data elements to help the Data Bank team estimate how much data will need to be provisioned for each option:
running customer balance column that includes the impact each transaction customer balance at the end of each month minimum, average and maximum values of the running balance for each customer Using all of the data available - how much data would have been required for each option on a monthly basis?
View my solution here.
Data Bank wants to try another option which is a bit more difficult to implement - they want to calculate data growth using an interest calculation, just like in a traditional savings account you might have with a bank.
If the annual interest rate is set at 6% and the Data Bank team wants to reward its customers by increasing their data allocation based off the interest calculated on a daily basis at the end of each day, how much data would be required for this option on a monthly basis?
Special notes:
Data Bank wants an initial calculation which does not allow for compounding interest, however they may also be interested in a daily compounding interest calculation so you can try to perform this calculation if you have the stamina!
Do give me a 🌟 if you like what you're reading. Thank you!