Here are some common SQL interview questions for finance roles, formatted for easy readability in HTML:
Finance SQL Interview Questions
Data Aggregation and Summary
- Question: You have a table of daily stock prices (date, ticker, open, high, low, close, volume). How would you calculate the average closing price for each stock in a given month?
SQL Solution (PostgreSQL):SELECT ticker, DATE_TRUNC('month', date) AS month, AVG(close) AS average_closing_price FROM stock_prices WHERE date BETWEEN '2023-01-01' AND '2023-12-31' -- Optional: Filter by year GROUP BY ticker, month ORDER BY ticker, month;Explanation: The `DATE_TRUNC` function extracts the first day of the month from the `date` column. `AVG(close)` calculates the average closing price. `GROUP BY` aggregates the data for each stock and month combination.
- Question: How do you calculate the cumulative sum of revenue by month?
SQL Solution (PostgreSQL):SELECT DATE_TRUNC('month', date) AS month, SUM(revenue) AS monthly_revenue, SUM(SUM(revenue)) OVER (ORDER BY DATE_TRUNC('month', date)) AS cumulative_revenue FROM revenue_table GROUP BY month ORDER BY month;Explanation: This uses a window function `SUM(SUM(revenue)) OVER (ORDER BY …)` to calculate the running total. The inner `SUM(revenue)` calculates the monthly revenue, and the outer `SUM` with the `OVER` clause calculates the cumulative sum ordered by month.
Data Manipulation and Filtering
- Question: You have a table of transactions (transaction_id, account_id, transaction_date, amount, transaction_type). How would you find all accounts that have had more than 5 transactions of type ‘DEBIT’ in the last 30 days?
SQL Solution (PostgreSQL):SELECT account_id FROM transactions WHERE transaction_type = 'DEBIT' AND transaction_date >= CURRENT_DATE - INTERVAL '30 days' GROUP BY account_id HAVING COUNT(*) > 5;Explanation: The `WHERE` clause filters for ‘DEBIT’ transactions within the last 30 days. `GROUP BY` groups the transactions by `account_id`. The `HAVING` clause filters the grouped results to include only accounts with more than 5 debit transactions.
- Question: How would you identify duplicate records in a table based on specific columns (e.g., account_number, transaction_date, amount)?
SQL Solution (PostgreSQL):SELECT account_number, transaction_date, amount, COUNT(*) FROM transactions GROUP BY account_number, transaction_date, amount HAVING COUNT(*) > 1;Explanation: This groups by the columns you suspect might define duplicates and uses `HAVING COUNT(*) > 1` to filter for groups where the count is greater than 1, indicating duplicates.
Joining Tables
- Question: You have two tables: `customers` (customer_id, name, country) and `orders` (order_id, customer_id, order_date, total_amount). How would you list all customers and their total order amounts, even if they haven’t placed any orders?
SQL Solution (PostgreSQL):SELECT c.customer_id, c.name, SUM(o.total_amount) AS total_order_amount FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.name ORDER BY c.customer_id;Explanation: A `LEFT JOIN` ensures that all rows from the `customers` table are included, even if there’s no matching row in the `orders` table. `SUM(o.total_amount)` will be `NULL` for customers without orders.
Window Functions
- Question: How do you calculate the moving average of a stock’s closing price over the last 7 days?
SQL Solution (PostgreSQL):SELECT date, close, AVG(close) OVER (ORDER BY date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_average_7_day FROM stock_prices ORDER BY date;Explanation: The `OVER (ORDER BY date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)` clause defines the window frame. It calculates the average of the `close` price for the current row and the 6 preceding rows based on the `date` order.
These examples cover common SQL concepts frequently tested in finance interviews. Remember to tailor your answers to the specific database system (e.g., PostgreSQL, MySQL, SQL Server) and be prepared to explain your reasoning clearly. Good luck!
675×675 sql interview archives page techbeamers from www.techbeamers.com 
702×389 top finance interview questions answers from www.educba.com 
940×1023 asked corporate finance interview questions from efinancemanagement.com 
4000×1562 top finance interview questions sample answers from www.shrofile.com 
1200×628 sql interview questions answers digitmg from 360digitmg.com 
281×364 finance manager interview questions template from www.businessformtemplate.com 
1280×760 sql interview questions campus placements talent battle from talentbattle.in 
1920×1032 top sql interview questions answers crack interview from data-flair.training 
1008×567 finance interview questions examples key tips from www.wikijob.co.uk 
620×336 frequently asked finance interview questions wallstreet school from www.thewallstreetschool.com 
1000×500 sql interview questions geeksforgeeks from www.geeksforgeeks.org 
1200×630 finance interview questions adaface from www.adaface.com 
800×449 sql interview questions business analyst from www.vantagelens.com 
1536×864 top sql interview questions answers from sampletestcases.com 
640×521 sql interview questions real companies stratascratch from www.stratascratch.com 
900×500 top finance interview question answers updated from www.educba.com 
768×691 financial manager interview questions accounting field jobs from accountingfieldjobs.com 
240×320 finance interview questions finance interview questionspdf pdfpro from pdf4pro.com 
1068×559 top finance interview questions great learning from www.mygreatlearning.com 
672×280 top finance interview questions answers internshala from blog.internshala.com 
