Write a SQL query to find the top 5 merchants by transaction volume in the last quarter from a transactions table with columns: transaction_id, merchant_id, transaction_date, amount. Explain your approach.
RoleBusiness Analyst
DifficultyDifficult
TopicTechnical
Asked at
Why This Is Asked
This question assesses foundational data extraction skills essential for a Business Analyst in fintech; no verbatim Maya question is publicly documented, but SQL proficiency is a confirmed technical screening topic.
General Approach
Outline the SQL logic step-by-step, mention handling of date filters and aggregation, and briefly discuss performance considerations to show depth.
General Answer
Since "transaction volume" can mean either number of transactions or total value, a good first step is to state that assumption explicitly, then write the query to cover it. Assuming volume means number of transactions and "last quarter" means the most recently completed calendar quarter, the query would be: SELECT merchant_id, COUNT(*) AS transaction_count, SUM(amount) AS total_amount FROM transactions WHERE transaction_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months' AND transaction_date < DATE_TRUNC('quarter', CURRENT_DATE) GROUP BY merchant_id ORDER BY transaction_count DESC LIMIT 5; The approach: filter transaction_date to the prior quarter's date range using DATE_TRUNC to get clean quarter boundaries, group the remaining rows by merchant_id, aggregate both a count and a sum so either definition of volume is available, order by the count (or by total_amount if volume means peso value), and limit to 5 rows. On a large transactions table, performance would benefit from a composite index on (transaction_date, merchant_id) so the date filter and grouping can both use the index, and from partitioning the table by date if it is very large, since the WHERE clause only touches one quarter's worth of data.
PH Interview Gotcha
Some candidates might say 'I will just use Excel or ask IT to do it,' which shows a lack of hands-on analytical skills. Instead, demonstrate the SQL steps even if you need to think through the logic aloud.
Sample STAR Answer▾
S
Situation
At my previous role in a fintech startup, our operations team needed a weekly report on the highest-grossing merchants to prioritize partnership outreach.
T
Task
I had to extract the top 5 merchants by total transaction volume from our database for the most recent quarter, ensuring accuracy and efficiency.
A
Action
I wrote a SQL query grouping by merchant_id, summing the amount, filtering for the last quarter using date functions, ordering by total amount descending, and limiting to 5. I validated the results by cross-checking with a few known high-volume merchants and optimized the query by indexing transaction_date and merchant_id.
R
Result
The report was generated in under 2 seconds and helped the partnerships team close three new deals, increasing merchant acquisition by 15% that quarter.
Key Takeaway
Always validate query results against business knowledge to avoid data blind spots.
Attempt Your Answer
Write your own answer, then get instant AI feedback graded against:
- •STAR Structure
- •Specificity & Numbers
- •Ownership Language
- •PH Workplace Context
Get AI feedback on your answer — free.
3 free AI-graded answers + 1 free mock interview, no card needed.
Sign Up FreeAlready have an account? Log in
Share Your Answer (0)
Sort
Sign in to join the conversation.
No answers shared yet — be the first to show how you'd approach this.