logo

Back to questions

First Transaction

Medium

This is the same question as problem #9 in the SQL Chapter of Ace the Data Science Interview!

Assume you are given the table below on user transactions. Write a query to obtain the list of customers whose first transaction was valued at $50 or more. Output the number of users.

Clarification:

  • Use the field to determine which transaction should be labeled as the first for each user.
  • Use a specific function (we can't give too much away!) to account for scenarios where a user had multiple transactions on the same day, and one of those was the first.

Table:

Column NameType
transaction_idinteger
user_idinteger
spenddecimal
transaction_datetimestamp

Example Input:

transaction_iduser_idspendtransaction_date
75927411149.5002/03/2022 00:00:00
85037111151.0003/15/2022 00:00:00
61534814536.3003/22/2022 00:00:00
137424156151.0004/04/2022 00:00:00
24847515687.0004/16/2022 00:00:00

Example Output:

users
1

Explanation: Only user 156 has a first transaction valued over $50.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.