Back to questions
This is the same question as problem #11 in the SQL Chapter of Ace the Data Science Interview!
Assume you are given the table below on Uber transactions made by users. Write a query to obtain the third transaction of every user. Output the user id, spend and transaction date.
The dataset you are querying against may have different input & output - this is just an example!
First, we obtain the order of transaction numbers for each user. We can do this by using the window function where we the all transactions by and the transaction_date.
Here's how the first 5 rows of output looks like:
From there on, we can simply convert the query into a subquery and filter for the users' third transaction which is their third transaction sorted by the transaction date (and is denoted as ).
Apart from using subquery to solve this question, you can also use a CTE. Do you know the differences between a subquery and a CTE?
A CTE is a temporary data set to be used as part of a query and it exists during the entire query session. A subquery is a nested query. It’s a query within a query and unlike CTE, it can be used within that query only. Read here and here for more understanding.
Both methods give the same output and perform fairly similarly. Differences are CTE is reusable during the entire session and more readable, whereas subquery can be used in FROM and WHERE clauses and can act as a column with a single value. We share more resources here (1, 2, 3) on their use cases.
Solution #1: Using Subquery
Solution #2: Using CTE