#522 User Purchases Before a Ban and Total Purchase Amount

Companies:
Ozon
Themes:
SQLJoinsAggregationPractice

The following tables are given:

// user
id | firstname | lastname | birth
1  | Ivan      | Petrov   | 1996-05-01
2  | Anna      | Petrova  | 1999-06-01
3  | Anna      | Petrova  | 1990-10-02

// purchase
sku | price | user_id | date
1   | 5500  | 1       | 2021-02-15
1   | 5700  | 1       | 2021-01-15
2   | 4000  | 1       | 2021-02-14
3   | 8000  | 2       | 2021-03-01
4   | 400   | 2       | 2021-03-02

// ban_list
user_id | date_from
1       | 2021-03-08

Required:

  1. Return unique combinations of user and product id for all purchases made before the users were banned. Sort first by the user's first name and then by SKU.
  2. Find users whose total purchase amount is greater than 5000 RUB. Return their names in the following format:
    user id | first name | last name | total purchase amount

Question review

Mentor
Answer as you would in a real interview. I’ll ask follow-up questions, then compare your answer with the reference answer and show your accuracy as a %.
Sign in to start the dialogue The mentor keeps the context of the current question and your previous messages.
Sign in