Coding questions · Atlassian
Identify Top-3 Users by Recent Total Spend
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.
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.
Top-3 Users by Recent Spend (Including Zeros)
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.
Table Users
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY |
| name | VARCHAR(100) | |
| joined_date | DATE |
Sample data:
| id | joined_date | name |
|---|---|---|
| 1 | 2023-01-02 | Alice |
| 2 | 2023-02-14 | Bob |
| 3 | 2023-02-20 | Carol |
Table Orders
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY |
| user_id | INTEGER | FOREIGN KEY REFERENCES Users(id) |
| order_date | DATE | |
| amount | DECIMAL(10,2) |
Sample data:
| amount | id | order_date | user_id |
|---|---|---|---|
| 120 | 101 | 2025-05-10 | 1 |
| 80 | 102 | 2025-05-20 | 2 |
| 240 | 103 | 2025-05-25 | 1 |
Expected output
Top 3 users by total spend between 2025-05-03 and 2025-06-01 (inclusive). Users with no orders in that window are included (with total_amount = 0) if needed to reach 3 rows. Sorted by total_amount descending, then user_id ascending.
| total_amount | user_id | user_name |
|---|---|---|
| 360 | 1 | Alice |
| 80 | 2 | Bob |
| 0 | 3 | Carol |
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.
- Use COALESCE around SUM to treat missing order amounts as zero, then ORDER BY the sum and LIMIT 3.