Data Engineering SQL Test

Photo via pvishnoi

1. Sum of calculation logic using SQL

Compute the year-end account balance for a customer based on the contents of the database table transactions, which houses transaction records for the year 2020.

The account begins with a balance of zero and undergoes various transactions throughout the year. Positive amounts denote deposits or credits, with no associated fees. Negative amounts represent debit transactions, such as credit card charges or withdrawals, impacting the monthly fee calculation.

Monthly fees are structured as follows:

• If there are fewer than 20 debit transactions or their combined total is less than 1000, a fee of 5 is charged.
• For 20 to 25 debit transactions, a fee of 3 applies.
• If there are more than 25 debit transactions, no monthly fee is levied.

Compute the year-end balance in the account by summing up all credits and debits, adjusting for monthly fees based on the criteria mentioned above.

CREATE TABLE transactions (
    dt DATETIME,
    amount DECIMAL(10, 2)
);


INSERT INTO transactions (dt, amount) VALUES
('2020-01-01 12:00:00', -10.00),
('2020-01-02 13:00:00', -20.00),
('2020-01-03 14:00:00', 100.00),
('2020-02-01 15:00:00', -5.00),
('2020-02-02 16:00:00', -15.00)…