Ready-Made Accounting Dataset for Practice

• transactions.account_id →references accounts.account_idmany to one: each Transactions row points to one Accounts row
• ledgers.transaction_id →references transactions.transaction_idmany to one: each Ledgers row points to one Transactions row

1. What is the balance for each account at the end of the period using the accounts, transactions, and ledgers tables?

   Sum of opening balance and net transaction amounts.

   tables: accounts, transactions, ledgers

2. Identify any discrepancies between transactions and ledger entries by joining transactions and ledgers.

   Look for mismatches in amounts or descriptions where transaction_id links them.

   tables: transactions, ledgers

3. Analyze the flow of funds through different account types using pivot tables or Power BI.

   Aggregate debit and credit amounts by account type.

   tables: accounts, transactions

4. What are the total debit and credit amounts for each transaction description?

   Group by the description column and sum debit_amount and credit_amount.

   tables: transactions

Table names match the SQLite file and the SQL script.

SELECT a.account_name, a.opening_balance + SUM(t.credit_amount - t.debit_amount) AS ending_balance
FROM accounts a
LEFT JOIN transactions t ON a.account_id = t.account_id
GROUP BY a.account_name, a.opening_balance
ORDER BY ending_balance DESC
LIMIT 20;

SELECT t.transaction_id, t.transaction_date, t.description, l.amount
FROM transactions t
JOIN ledgers l ON t.transaction_id = l.transaction_id
WHERE l.amount <> 0
LIMIT 20;

SELECT description, SUM(debit_amount) AS total_debit
FROM transactions
GROUP BY description
ORDER BY total_debit DESC
LIMIT 5;

SELECT a.account_name, t.transaction_date, (t.credit_amount - t.debit_amount) AS transaction_value
FROM accounts a
JOIN transactions t ON a.account_id = t.account_id
ORDER BY t.transaction_date DESC
LIMIT 20;

Excel

Each table can be loaded into a separate Excel sheet. Use Power Query to join tables based on their keys (e.g., account_id, transaction_id). Create a PivotTable on the 'transactions' sheet, using 'account_type' from the 'accounts' table (after joining) as rows and summing 'debit_amount' and 'credit_amount'.

Power BI

Load all three tables. Create relationships: transactions.account_id to accounts.account_id (Many-to-One), and ledgers.transaction_id to transactions.transaction_id (One-to-One or One-to-Many depending on data granularity). Create measures like Total Debits = SUM(transactions[debit_amount]) and Total Credits = SUM(transactions[credit_amount]).

SQL

Load the SQLite or SQL relational download. The primary keys are account_id (accounts), transaction_id (transactions), and ledger_id (ledgers). Foreign keys are transactions.account_id referencing accounts.account_id, and ledgers.transaction_id referencing transactions.transaction_id. Joins can be performed on these keys.

• 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