# Top Users by Recent Spend

> Big spenders in the last 30 days.

Canonical URL: <https://datadriven.io/problems/top_users_by_recent_spend>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Our loyalty program selects VIP tiers based on recent activity. Return the top 10 users by total spending in the last 30 days, only counting users with a positive total. Show user_id and their total spend, highest to lowest.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests date filtering combined with aggregation and ordering. Interviewers check whether you correctly compute a rolling date window and apply the positive-total filter.

> **Trick to Solving**
>
> "Last 30 days" requires a relative date filter. The key decision is whether to use `CURRENT_DATE` or `DATE('now')` depending on the SQL dialect. In SQLite, use `DATE('now', '-30 days')`.
> 
> 1. Filter to recent transactions using a date comparison
> 2. Aggregate per user
> 3. Apply HAVING for the positive-total constraint
> 4. Order and limit

---

### Break down the requirements

#### Step 1: Filter to last 30 days

`WHERE transaction_date >= DATE('now', '-30 days')` restricts the 120M row table to recent activity.

#### Step 2: Aggregate and filter positive totals

`GROUP BY user_id` with `HAVING SUM(total_amount) > 0` ensures only users with net positive spending are included.

#### Step 3: Order and limit to top 10

`ORDER BY total_spend DESC LIMIT 10` returns the highest spenders.

---

### The solution

**Date-filtered aggregation with positive spend filter**

```sql
SELECT user_id, SUM(total_amount) AS total_spend
FROM transactions
WHERE transaction_date >= DATE('now', '-30 days')
GROUP BY user_id
HAVING SUM(total_amount) > 0
ORDER BY total_spend DESC
LIMIT 10
```

> **Cost Analysis**
>
> The date filter narrows 120M rows to roughly 10M (30 of 365 days). An index on `(transaction_date, user_id, total_amount)` would allow a range scan and covering index access.

> **Interviewers Watch For**
>
> Correct date arithmetic. Using `DATE('now') - 30` instead of `DATE('now', '-30 days')` is a common SQLite mistake that produces integer subtraction instead of date math.

> **Common Pitfall**
>
> Omitting the `HAVING` clause for positive totals. Users with only refunds could have negative totals, and the prompt explicitly excludes them.

---

## Common follow-up questions

- What if the date column included timestamps, not just dates? _(Tests whether you use `>=` with a date or need to truncate the timestamp.)_
- How would you handle ties at the 10th position? _(Switching from LIMIT to DENSE_RANK for tie inclusion.)_
- What if transactions spanned multiple time zones? _(Tests awareness of UTC normalization before date filtering.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/top_users_by_recent_spend)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.