Budget Dataset for Financial Analysis

• transactions.account_id →references accounts.account_idmany to one: each Transactions row points to one Accounts row
• transactions.category_id →references categories.category_idmany to one: each Transactions row points to one Categories row

1. Calculate the total spending per category for each account over the last year.

   Filter transactions for the last full calendar year and join accounts, categories, and transactions tables. Group by account and category to sum the amounts.

   tables: accounts, categories, transactions

2. Identify the top 5 accounts with the highest net income (income - expenses) in the last quarter.

   Calculate net income for each account by summing credit amounts and subtracting debit amounts. Filter transactions for the last quarter and rank accounts by net income.

   tables: accounts, transactions

3. Analyze the distribution of transaction amounts for 'Expenses' category group.

   Join transactions with categories. Filter for transactions where category_group is 'Expenses'. Analyze the distribution of the 'amount' column.

   tables: categories, transactions

4. What is the average transaction amount per account type for the current year?

   Join transactions with accounts. Filter transactions for the current year. Group by account_type and calculate the average of the absolute transaction amounts.

   tables: accounts, transactions

Table names match the SQLite file and the SQL script.

SELECT c.category_name, SUM(t.amount) AS total_spent
FROM transactions t
JOIN categories c ON t.category_id = c.category_id
WHERE t.transaction_date BETWEEN '2023-01-01' AND '2023-12-31' AND c.category_group = 'Expenses'
GROUP BY c.category_name
ORDER BY total_spent ASC
LIMIT 20;

SELECT a.account_name, SUM(t.amount) AS net_income
FROM transactions t
JOIN accounts a ON t.account_id = a.account_id
WHERE t.transaction_date BETWEEN '2024-10-01' AND '2024-12-31'
GROUP BY a.account_name
ORDER BY net_income DESC
LIMIT 5;

SELECT AVG(t.amount) AS average_income
FROM transactions t
JOIN categories c ON t.category_id = c.category_id
WHERE t.transaction_type = 'Credit' AND c.category_group = 'Income'
LIMIT 20;

SELECT STRFTIME('%Y-%m', t.transaction_date) AS transaction_month, SUM(t.amount) AS monthly_spending
FROM transactions t
WHERE t.transaction_type = 'Debit'
GROUP BY transaction_month
ORDER BY transaction_month
LIMIT 20;

Excel

Load each table into a separate sheet. Use Power Query to join tables based on account_id and category_id. Create a PivotTable on the joined data to analyze spending by category and account type.

Power BI

Create relationships between accounts and transactions (account_id), and categories and transactions (category_id). Build measures for total income, total expenses, and net income. Visualize monthly spending trends and budget vs. actuals by category group.

SQL

Load the CSV files into separate tables or use the provided SQLite database. The 'transactions' table contains foreign keys 'account_id' and 'category_id' which can be used to join with the 'accounts' and 'categories' tables respectively.

Python

Use pandas to read the CSV files. Merge the DataFrames using 'account_id' and 'category_id'. Perform aggregations and analysis on the merged data to calculate metrics like total spending per category or net income per account.

• CSV (zip)One CSV file per table, zipped
• Excel workbookOne worksheet per table
• SQLite databaseA ready-to-query database file with every table
• Parquet (zip)One Parquet file per table, zipped
• SQL scriptCREATE TABLE with primary and foreign keys, then INSERTs
• CSVA single CSV file
• JSONA single JSON file

• Excel Practice Datasets: Sample Data for Skill Development