Identify Top-3 Users by Recent Total Spend

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Atlassian.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Atlassian during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Identify Top-3 Users by Recent Total Spend