First Transaction — SQL Question Asked By Etsy
Originally Published in https://asyncq.com/
- 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.
- 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.
- 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.