Preppin’ Data — 2019W13

Input

Most clean data I have seen in some exercises.

Daily level transactional data

Dimension table with Name and Credit Info

Steps for Solution

1. Join the 2 tables using the account Key
2. The tricky part is the no. of days with negative balance and no. of days below credit limit. This cannot be calculated once you aggregate your data on different timelines. So, we need to calculate this on the current level as flags and aggregate them on different timeline levels.
3. Also, the most difficult part I has to learn was how-to split the branches instead of adding steps in between branches. You need to click on the + sign on the box rather than on the line.
4. Calculate start dates, based on week, month and year truncation. Average the balance and transaction as instructed and sum up the flags for negative balance and below credit cases.
5. Make sure to remove the date field from the grouped columns , else your granularity will not change.

Solution Flow

Output

Daily Out

Quarterly Output

Monthly Output

Photo by Aziz Acharki on Unsplash