Katkat
Back

Find top buyer in earliest 10-year window

WayfairData Scientist · Online Assessment
EasyData ScientistOnline AssessmentData Manipulation (SQL/Python)

Last updated

This question tests the ability to filter data by date ranges, group transactional records by user, and use window functions or aggregations to identify a specific record based on criteria.

Problem Statement

You are provided with two tables: transactions and users.

Table transactions contains columns:

  • transaction_id (int)
  • user_id (int)
  • amount (float)
  • transaction_date (date)

Table users contains columns:

  • user_id (int)
  • signup_date (date)

Find the user who spent the most money within the first 10 years of the company's existence. The company's existence begins on the date of the very first transaction recorded in the transactions table. If there is a tie for the highest spender, return all tied users.

Input Example

Transactions:

transaction_iduser_idamounttransaction_date
11015002010-01-05
21028002010-06-01
31014002015-02-01
410320002022-01-01

Output:

user_idtotal_spent
101900

Note: The first transaction occurred in 2010-01-05, so the 10-year window ends on 2020-01-05. Transaction 4 is excluded.

Hints

Hint 1

First, determine the start date of the 10-year window by finding the minimum value in the transaction_date column.

Hint 2

Filter the transactions table to include only those records where the transaction_date is between the start date and 10 years after that start date.

Hint 3

Group the filtered data by user_id, sum the amounts, and use a ranking function or a subquery to select the maximum total_spent.

Follow-up questions

  • How would your query change if you needed to exclude users who signed up after the transaction window started?
  • What changes are required if the 10-year window must be a rolling window rather than fixed from the first transaction?
  • How would you optimize this query if the transactions table contained billions of rows?