Banking Dataset for SQL Practice

• accounts.customer_id →references customers.customer_idmany to one: each Accounts row points to one Customers row
• transactions.account_id →references accounts.account_idmany to one: each Transactions row points to one Accounts row

1. Write a SQL query to find the total balance for each customer, joining the accounts and customers tables.

   Use a SUM aggregate function on the balance column and group by customer.

   tables: accounts, customers

2. Write a SQL query to list all transactions for a specific account (e.g., account_id = '6c48e9de-46ab-4c4c-b1f9-e52c283ace6d') within a date range (e.g., '2023-01-01' to '2023-12-31').

   Use a WHERE clause with conditions for account_id and transaction_date.

   tables: transactions

3. Write a SQL query to identify customers who have more than one account type, using a GROUP BY and HAVING clause.

   Group by customer_id and count distinct account_types, then filter with HAVING.

   tables: accounts, customers

4. Calculate the average transaction amount for each transaction type (Deposit, Withdrawal, etc.) using SQL.

   Use the AVG aggregate function and group by the transaction type.

   tables: transactions

Table names match the SQLite file and the SQL script.

SELECT c.customer_id, c.first_name, c.last_name, SUM(a.balance) AS total_balance
FROM customers c
JOIN accounts a ON c.customer_id = a.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_balance DESC
LIMIT 20;

SELECT transaction_id, transaction_date, type, amount
FROM transactions
WHERE account_id = '6c48e9de-46ab-4c4c-b1f9-e52c283ace6d'
AND transaction_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY transaction_date
LIMIT 20;

SELECT c.customer_id, c.first_name, c.last_name, COUNT(DISTINCT a.account_type) AS num_account_types
FROM customers c
JOIN accounts a ON c.customer_id = a.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING COUNT(DISTINCT a.account_type) > 1
LIMIT 20;

SELECT type, AVG(amount) AS average_amount
FROM transactions
GROUP BY type
LIMIT 20;

SELECT
    t.transaction_id,
    t.transaction_date,
    t.amount,
    a.account_type,
    ROW_NUMBER() OVER (PARTITION BY t.account_id ORDER BY t.transaction_date DESC) as rn
FROM transactions t
JOIN accounts a ON t.account_id = a.account_id
WHERE t.transaction_date BETWEEN '2023-01-01' AND '2023-12-31'
LIMIT 20;

SQL

The SQLite database contains 3 tables: customers, accounts, and transactions. Primary keys are customer_id (customers), account_id (accounts), and transaction_id (transactions). Foreign key relationships are accounts.customer_id -> customers.customer_id and transactions.account_id -> accounts.account_id.

• 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