Identify Top-3 Users by Recent Total Spend
Company: Atlassian
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Users
+----+-------+-------------+
| id | name | joined_date |
+----+-------+-------------+
| 1 | Alice | 2023-01-02 |
| 2 | Bob | 2023-02-14 |
| 3 | Carol | 2023-02-20 |
+----+-------+-------------+
Orders
+-----+---------+------------+--------+
| id | user_id | order_date | amount |
+-----+---------+------------+--------+
| 101 | 1 | 2023-03-01 | 120.0 |
| 102 | 2 | 2023-03-02 | 80.0 |
| 103 | 1 | 2023-03-05 | 240.0 |
+-----+---------+------------+--------+
##### Scenario
SQL technical screen assessing medium-difficulty query skills on e-commerce data.
##### Question
Write a SQL query that returns the top-3 users by total spend in the last 30 days, including user_id, user_name, and total_amount.
##### Hints
Use window functions or ORDER BY/LIMIT; remember date filtering.
Overview: This question evaluates SQL data manipulation competencies such as aggregation, joins, time-based filtering, and ranking to compute recent user spending; it falls under the Data Manipulation (SQL/Python) domain and targets practical application.
Write a SQL query that returns the top 3 users by total spend between 2025-05-03 and 2025-06-01 (inclusive). If fewer than three users have placed orders in that period, still return exactly three users by including users whose total spend is zero. Output columns: user_id, user_name, and total_amount. Order the result by total_amount in descending order; break ties by user_id in ascending order.
Tables
Users(id INTEGER, name VARCHAR(100), joined_date DATE)
Orders(id INTEGER, user_id INTEGER, order_date DATE, amount DECIMAL(10,2))
Hints
- Use a LEFT JOIN so users without orders in the date range are still returned.
- Filter orders to the range 2025-05-03 through 2025-06-01 in the join condition.