← Atlassian Interview Insights

Atlassian·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
May 2026

Summary

SQL technical screen for a Data Scientist role at Atlassian. One question, medium difficulty, e-commerce data with a couple of tables. Pretty standard stuff but the date filtering piece is where people slip up.

Questions Asked (1)

Q1

Given a users table and an orders table, write a SQL query that returns the top 3 users by total spend in the last 30 days. Output should include user_id, user name, and total amount spent.

Product Analytics & MetricsData Modeling
Author's notes

I got the join and the aggregation fine but almost forgot the date filter entirely.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and assumptions (e.g., date column, status filters, user name field). Then write a query that joins users and orders, filters orders to the last 30 days, aggregates total spend per user, and returns the top 3 using ORDER BY and LIMIT. Finally, discuss edge cases like time zones, refunds, and performance considerations.

Pro tip: Mention that you would exclude cancelled or refunded orders and handle time zones consistently, showing you think about data quality and business context beyond just writing SQL.

1. Clarify requirements and schema

Ask about table structures, date column, order status, and how to define 'last 30 days' (e.g., relative to current date or a fixed date). Confirm the output columns and any filters.

2. Write the core query

Join users and orders on user_id, filter orders to the last 30 days, group by user, sum the order amount, and select user_id, user name, and total spend.

3. Apply ranking and limit

Order the aggregated results by total spend descending and limit to the top 3 users. Consider using a window function if ties or additional ranking logic is needed.

4. Discuss edge cases and optimizations

Address handling of ties, time zones, refunds/cancellations, and performance (e.g., indexing on order date and user_id).

Key Points to Mention

  • Use of JOIN between users and orders tables
  • Filtering with a date range (e.g., WHERE order_date >= CURRENT_DATE - INTERVAL '30 days')
  • Aggregation with SUM and GROUP BY
  • Sorting with ORDER BY total_spend DESC and LIMIT 3
  • Handling ties (e.g., using DENSE_RANK or RANK window functions)
  • Excluding cancelled/refunded orders and considering time zones

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.