Introduction
- In this article we will solve First Transaction SQL question which is asked by Microsoft as per DataLemur website.
If you don’t know DatLemur then please do visit , it’s one of the best website to practice SQL questions and improve your SQL skills.
Question
- We have been given user_transaction table , and we need to query users who made their first transaction of worth $50 or more.
- As we can see there is only one user with id 156 made their first transaction of $87.
Solution
- At first , we need to partition entire table based on user_id and sort them based on transaction data in ascending order.
- We can do this using window function as shown below. we are ranking each record with row number here.
- As we can see the result is partitioned by each user_id and sorted according to transaction date.
- Now its become convenient for us to search the all the user_ids where rnk is 1 and spend amount is $50 or more
- We can achieve that below query, we are using window query as subquery here which forms the base table for us to query result from.
Final Solution
- Here is the final SQL solution.
Submission
- We can submit our solution to the platform and it’s accepted.
Conclusion
- In this article we solved sql question asked by Etsy. We used SQL Windowing operation to partition and rank each record within partition with row number. We also learnt how we can use subquery as base table.
Bonus Tip
- If you want to upskill your SQL interview game, you can definitely check out this bestseller course