SQL → Relational Algebra

Database Schema

Account (AccountID, AccountHolderName, AccountType, Balance)

Transaction (TransactionID, TransactionType, RiskLevel)

AccountTransaction (AccountID, TransactionID, TransactionDate, Amount)

Question 1

Find the AccountHolderNames of all accounts that performed more transactions than the average number of transactions per account.

SQL

SELECT A.AccountHolderName
FROM Account A
JOIN AccountTransaction AT ON A.AccountID = AT.AccountID
GROUP BY A.AccountID, A.AccountHolderName
HAVING COUNT(*) >
(
    SELECT AVG(TransactionCount)
    FROM
    (
        SELECT COUNT(*) AS TransactionCount
        FROM AccountTransaction
        GROUP BY AccountID
    ) T
);

Relational Algebra

Step 1 — count transactions per account

R1 ← γAccountID, COUNT(TransactionID) → TxCount (AccountTransaction)

Step 2 — compute average transaction count

R2 ← γAVG(TxCount) → AvgTx (R1)

Step 3 — select accounts with more transactions than average

R3 ← σTxCount > AvgTx (R1 × R2)

Step 4 — join with Account table

R4 ← R3 ⋈AccountID Account

Final result

πAccountHolderName (R4)