[Practice] SQL Practice with Datalemur

I use datalemur to practice SQL currently, you can also join me to practice your SQL skills for the most common data interviews: https://datalemur.com?referralCode=EoXbX6vn

User’s Third Transaction [Uber SQL Interview Question]

Level: Medium

Desciption

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.

transactions Table:

transactions Example Input:

Example Output:

The dataset you are querying against may have different input & output — this is just an example!

The Answer:

To obtain the third transaction of every user from the transactions table, you can use a combination of the ROW_NUMBER() window function and a Common Table Expression (CTE) or a subquery. The ROW_NUMBER() function allows you to assign a unique number to each transaction for a user, ordered by the transaction date. You can then filter for the third transaction for each user.

Here’s how you can write the query:

WITH ranked_transactions AS (
    SELECT 
        user_id,
        spend,
        transaction_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date) AS rn
    FROM 
        transactions
)
SELECT 
    user_id,
    spend,
    transaction_date
FROM 
    ranked_transactions
WHERE 
    rn = 3;

Explanation

A. ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date) AS rn:

• The ROW_NUMBER() function assigns a unique row number (rn) to each transaction for each user (PARTITION BY user_id), ordered by the transaction date (ORDER BY transaction_date).

B. WITH ranked_transactions AS (...):

• This part of the query creates a Common Table Expression (CTE) named ranked_transactions, where each transaction is ranked for each user.

C. SELECT ... FROM ranked_transactions WHERE rn = 3:

• Finally, the outer query selects the user_id, spend, and transaction_date for the transactions where the row number (rn) is 3, which corresponds to the third transaction of each user.

Output