Data Analyst Interview Questions & Answers: Set 3, Mobile Banking & Fintech

SQL

1. 1.Find users whose total transactions in a day exceed 50,000 taka.

   Hide answer

   Answer: Group by user and calendar day, sum the amounts, and keep groups above 50,000 with HAVING. Assumed table: transactions(txn_id, user_id, amount, txn_time, status). I count only successful transactions, because failed attempts never moved money. Trap: you cannot filter an aggregate with WHERE; SUM() conditions belong in HAVING. If the database stores time in UTC, convert to Asia/Dhaka before casting to a date, or the day boundaries will be wrong.

   SELECT user_id,
          CAST(txn_time AS DATE) AS txn_date,
          SUM(amount)            AS daily_total
   FROM transactions
   WHERE status = 'SUCCESS'
   GROUP BY user_id, CAST(txn_time AS DATE)
   HAVING SUM(amount) > 50000
   ORDER BY daily_total DESC;

2. 2.Write a query to find each user's first and last transaction date.

   Hide answer

   Answer: Use MIN() and MAX() on the transaction timestamp, grouped by user. Table: transactions(user_id, txn_time, status). I add a count and the gap in days, because the gap tells you the user's tenure, which is useful for churn and lifecycle analysis. Decide whether failed transactions should count; usually you filter to successful ones so the first date is the first real usage.

   SELECT user_id,
          MIN(txn_time) AS first_txn,
          MAX(txn_time) AS last_txn,
          COUNT(*)      AS txn_count,
          MAX(txn_time)::date - MIN(txn_time)::date AS active_span_days
   FROM transactions
   WHERE status = 'SUCCESS'
   GROUP BY user_id;