First Transaction — SQL Question Asked By Etsy

Solving SQL Question Asked By Etsy

Suraj Mishra

--

Originally Published in https://asyncq.com/

Photo by Caspar Camille Rubin on Unsplash

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

--

--