Dataset

Banking Transactions SQL Practice Dataset

Accounts, merchants, and two years of posted transactions with no stored balance to read off.

Tables: 5
Rows: 343,840
Size: Large
Level: Advanced
Domain: Finance
Formats: Parquet · Schema SQL
In browser: 5,415 rows in the browser

Download this dataset, free

No account, no email, no attribution required: dedicated to the public domain under CC0 1.0, so commercial use and redistribution are fine. Open it in the browser playground, or download the Parquet files below and run banking_transactions.schema.sql in DuckDB to create the tables and load them.

Open in playground → 15 questions with answers ↓

banking_transactions.schema.sql All 5 tables, 343,840 rows, as CREATE TABLE and COPY statements that load the files below. Download →

This is a large dataset. The downloads here are the complete 343,840 rows as Parquet. Opening it in the playground loads a 5,415-row sample instead, so the browser tab stays responsive. The schema is identical, so a query you write against the sample runs unchanged against the full download.

branches.parquet 40 rows merchants.parquet 800 rows customers.parquet 9,000 rows accounts.parquet 14,000 rows transactions.parquet 320,000 rows

Two years of posted transactions for a retail bank: who holds which accounts, where the money went, and when. It is built around one deliberate omission: there is no balance column anywhere. Not on the account, not on the transaction. If you want to know what an account was worth on a given day, you have to add up its history in order, and that is a window function rather than a GROUP BY.

That omission is the point. A running balance, a rolling 30-day spend, "the transaction that took this account overdrawn", and "each account's largest payment of the month" are all the same shape: a calculation that needs to see the rows around the current one. Aggregation collapses those rows away before you can look at them. This dataset is large enough that you cannot check the answer by eye, so the window has to be right.

The second thing to find is a duplicate. A small number of card payments were posted twice: same account, same merchant, same amount, a few minutes apart. Nothing marks them. They are not exact duplicates, because the timestamp and the id differ, so GROUP BY and DISTINCT both walk straight past them. Comparing each row with the previous one on the same account, with LAG, is what surfaces them.

The nulls are honest ones rather than gaps in the data. A transfer, a fee, an ATM withdrawal, interest and salary all have no counterparty, so merchant_id is null on roughly two transactions in five. Join to merchants without thinking and you silently lose that money from every total. closed_on is null while an account is open, and closed accounts keep their full history, so "active customers" is a decision rather than a filter.

All of it is synthetic. No real person, account, card, merchant, or transaction is represented, and nothing here comes from a real financial institution.

Schema

Arrows point from a column to the key it references. PK marks a table's key and FK a column that references another table.

Join conditions

customers.home_branch_id = branches.branch_id
accounts.customer_id = customers.customer_id
transactions.account_id = accounts.account_id
transactions.merchant_id = merchants.merchant_id

Schema · Banking Transactions View dataset schema 5 tables

Table · branches

Physical branches. Small enough to join freely, and, like merchants, it has no nullable column.

5 columns · 39 rows

branch_id int branch_name text city text country text opened_on date

Table · merchants

Counterparties for card payments and direct debits. Transfers, fees and ATM withdrawals have no merchant, so not every transaction points here.

4 columns · 782 rows

merchant_id int merchant_name text category text country text

Table · customers

One row per banking customer. risk_band is null for customers never assessed, which is most of them.

9 columns · 215 rows

customer_id int first_name text last_name text email text city text country text home_branch_id int joined_on date risk_band text

Table · accounts

One row per account; a customer may hold several. closed_on is null while the account is open, and a closed account keeps its transaction history.

7 columns · 240 rows

account_id int customer_id int account_type text currency text opened_on date closed_on date overdraft_limit double

Table · transactions

Every posted transaction. No balance column: an account's balance is the running sum of amount in posted_at order. merchant_id is null on transfers, fees, interest, salary and ATM withdrawals.

9 columns · 4139 rows

transaction_id int account_id int merchant_id int posted_at timestamp transaction_type text direction text amount double channel text description text

Best for

Running balances and rolling totals, because no balance column exists to read off.
Finding duplicate card charges posted minutes apart, which needs LAG rather than GROUP BY.
Conditional aggregation over debits and credits, and the NULL merchant on every transfer and fee.

Starter questions

What is the running balance on each account, in posted order?
Which card payments look like a double charge?
How much did each account take in and pay out per month?

Practice questions

Work these in the playground against the browser sample. Each link opens the dataset with the question in the editor, ready for your query. Solutions are collapsed, so open one only after you have an answer of your own.

Beginner

1. How many transactions are there in total, and how many distinct accounts do they cover?

COUNT versus COUNT DISTINCT before any joining.

Try it in the playground →
Show solution
```
select count(*) as transactions, count(distinct account_id) as accounts
from transactions;
```
2. How many transactions are there of each type, and what do they total? Biggest total first.

GROUP BY with two aggregates, ordered by the aggregate.

Try it in the playground →
Show solution
```
select transaction_type,
       count(*) as transactions,
       round(sum(amount), 2) as total
from transactions
group by transaction_type
order by total desc;
```
3. Which transaction types have no merchant at all? Return the type and how many rows it covers.

Filtering on IS NULL, and seeing how much of the data an inner join would drop.

Try it in the playground →
Show solution
```
select transaction_type, count(*) as transactions
from transactions
where merchant_id is null
group by transaction_type
order by transactions desc;
```
4. How many accounts of each type are still open? An account is open when it has no closing date.

IS NULL as a business rule rather than as missing data.

Try it in the playground →
Show solution
```
select account_type, count(*) as open_accounts
from accounts
where closed_on is null
group by account_type
order by open_accounts desc;
```
5. How many customers hold no account at all?

An anti-join with NOT EXISTS, on a table where most rows do match.

Try it in the playground →
Show solution
```
select count(*) as customers_without_accounts
from customers c
where not exists (
  select 1 from accounts a where a.customer_id = c.customer_id
);
```

Intermediate

1. For the busiest account in the data, show each transaction with the running balance after it. Debits subtract, credits add.

The window function this dataset exists for: SUM OVER with an explicit order.

Try it in the playground →

Show solution

with signed as (
  select account_id,
         transaction_id,
         posted_at,
         transaction_type,
         case when direction = 'debit' then -amount else amount end as delta
  from transactions
  where account_id = (
    select account_id from transactions group by account_id order by count(*) desc limit 1
  )
)
select posted_at,
       transaction_type,
       round(delta, 2) as amount,
       round(sum(delta) over (order by posted_at, transaction_id), 2) as running_balance
from signed
order by posted_at, transaction_id;

2. For each month, how much went out and how much came in? Return the month, total debits, total credits, and the net.

Conditional aggregation: two filters over one pass instead of two queries unioned together.

Try it in the playground →

Show solution

select date_trunc('month', posted_at) as month,
       round(sum(amount) filter (where direction = 'debit'), 2) as debits,
       round(sum(amount) filter (where direction = 'credit'), 2) as credits,
       round(sum(case when direction = 'debit' then -amount else amount end), 2) as net
from transactions
group by month
order by month;

3. Which merchant categories take the most money, and what share of card spending is each? Keep only card payments.

Joining past a nullable foreign key, then a window aggregate for the share of total.

Try it in the playground →

Show solution

select m.category,
       round(sum(t.amount), 2) as spend,
       round(100.0 * sum(t.amount) / sum(sum(t.amount)) over (), 1) as pct_of_spend
from transactions t
join merchants m on m.merchant_id = t.merchant_id
where t.transaction_type = 'card_payment'
group by m.category
order by spend desc;

4. What is the highest-spending merchant in each category?

Top-1 per group, using ROW_NUMBER after spend has been grouped by merchant.

Try it in the playground →

Show solution

select category, merchant_name, spend
from (
  select m.category,
         m.merchant_name,
         round(sum(t.amount), 2) as spend,
         row_number() over (
           partition by m.category
           order by sum(t.amount) desc, m.merchant_id
         ) as rank
  from transactions t
  join merchants m on m.merchant_id = t.merchant_id
  group by m.category, m.merchant_id, m.merchant_name
)
where rank = 1
order by spend desc;

5. How many customers hold one account, two accounts, three, and so on?

Aggregating an aggregate: count per customer first, then count the customers.

Try it in the playground →

Show solution

with per_customer as (
  select c.customer_id, count(a.account_id) as accounts
  from customers c
  left join accounts a on a.customer_id = c.customer_id
  group by c.customer_id
)
select accounts, count(*) as customers
from per_customer
group by accounts
order by accounts;

Advanced

1. Find the duplicate card payments: same account, same merchant, same amount, posted no more than 15 elapsed minutes after the one before it. Return both timestamps and the amount.

The anomaly this dataset hides. The rows are not identical, so DISTINCT and GROUP BY both miss them; LAG over the account is what finds them.

Try it in the playground →

Show solution

with card as (
  select account_id,
         merchant_id,
         amount,
         posted_at,
         lag(posted_at) over (
           partition by account_id, merchant_id, amount order by posted_at
         ) as previous_posted_at
  from transactions
  where transaction_type = 'card_payment'
    and merchant_id is not null
)
select account_id,
       merchant_id,
       round(amount, 2) as amount,
       previous_posted_at,
       posted_at,
       round(date_diff('second', previous_posted_at, posted_at) / 60.0, 2) as minutes_apart
from card
where previous_posted_at is not null
  and posted_at between previous_posted_at and previous_posted_at + interval 15 minute
order by account_id, posted_at;

2. Which accounts' observed transaction balance went below zero, and when did it first happen? Use the running balance, and ignore accounts that never did.

A running balance inside a CTE, then a first-occurrence filter over it.

Try it in the playground →

Show solution

with ledger as (
  select account_id,
         posted_at,
         sum(case when direction = 'debit' then -amount else amount end)
           over (partition by account_id order by posted_at, transaction_id) as balance
  from transactions
)
select account_id,
       min(posted_at) as first_overdrawn_at,
       round(min(balance), 2) as lowest_balance
from ledger
where balance < 0
group by account_id
order by lowest_balance
limit 20;

3. For each account type, how did monthly card spending change against the month before? Return the month, the type, the total, and the change.

LAG across a partition, which is the difference between a trend and a list of monthly totals.

Try it in the playground →

Show solution

with monthly as (
  select a.account_type,
         date_trunc('month', t.posted_at) as month,
         sum(t.amount) as spend
  from transactions t
  join accounts a on a.account_id = t.account_id
  where t.transaction_type = 'card_payment'
  group by a.account_type, month
)
select account_type,
       month,
       round(spend, 2) as spend,
       round(spend - lag(spend) over (partition by account_type order by month), 2) as change_vs_prior_month
from monthly
order by account_type, month;

4. Show a 30-day rolling total of card spending across the whole bank, one row per day.

A window frame measured in days rather than rows, which is where RANGE differs from ROWS.

Try it in the playground →

Show solution

with daily as (
  select cast(posted_at as date) as day,
         sum(amount) as spend
  from transactions
  where transaction_type = 'card_payment'
  group by day
)
select day,
       round(spend, 2) as spend,
       round(sum(spend) over (
         order by day
         range between interval 29 days preceding and current row
       ), 2) as rolling_30_day
from daily
order by day;

5. Which merchants have no customer transactions? Return the merchant name and its category.

An anti-join against a nullable foreign key, where NOT IN would return nothing at all.

Try it in the playground →
Show solution
```
select m.merchant_name, m.category
from merchants m
where not exists (
  select 1 from transactions t where t.merchant_id = m.merchant_id
)
order by m.merchant_name;
```

Practice with this dataset

Guided lessons for this dataset are still being written. In the meantime, open it in the playground: the tables are already loaded, and the starter query is waiting in the editor.

Open Banking Transactions in the playground →

Large · 7 tables

Ecommerce Orders SQL Practice Dataset

Customers, products, orders, line items, payments, and returns at realistic online-retail volume.

Small · 5 tables

Website Analytics SQL Practice Dataset

Sessions, pageviews, events, users, and campaigns for funnel and attribution practice.

Large · 6 tables

SaaS Subscriptions SQL Practice Dataset

Organizations, plan intervals, invoices, and product usage for a B2B SaaS business.

Large · 5 tables

Marketing Campaigns SQL Practice Dataset