Highest Daily Spend

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Shopify

Problem

A day's highest spend can be shared by several users, and each user's spend for a day is all of their transactions on that date added together. For every date from March 1 through June 1, 2026, list the top spenders by username with that daily total, earliest date first and tied users alphabetically.

Somewhere in that window, someone broke the spending record.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

This is a per-date top spot with ties kept, sitting on top of a second aggregation people forget is there. Two grains are in play: the single transaction and the user-day. Score raw transactions and a user with three modest orders on one date loses to someone with one big order, even when the first user's day is bigger. Collapse to user-days first, then find the top within each date, and keep everyone who shares it. Pick one row per date with ROW_NUMBER() and a shared top spot quietly loses a winner the question said to return.

Two grains, two stages

Step 1: Scope the window and attach names

Inner-join users to transactions on user_id and keep transaction_date BETWEEN '2026-03-01' AND '2026-06-01'. BETWEEN includes both ends, and because transaction_date is a plain date string, June 1 rows match exactly. The inner join is correct here: someone with no spend that day can never be its top spender.

Step 2: Build the user-day total

Group by username and transaction_date, emitting SUM(t.total_amount) AS total_daily_spend. This is the stage that turns several same-day orders into one number per person per date. Everything after this point compares days, not orders.

Step 3: Find the top inside each date

DENSE_RANK() OVER (PARTITION BY transaction_date ORDER BY total_daily_spend DESC) restarts the count on every date, and rnk = 1 keeps every user-day equal to that date's maximum. Drop the partition and you get the single biggest user-day of the whole spring instead of one top tier per date.

WITH daily_spend AS (
    SELECT u.username,
           t.transaction_date,
           SUM(t.total_amount) AS total_daily_spend
    FROM users u
    INNER JOIN transactions t ON u.user_id = t.user_id
    WHERE t.transaction_date BETWEEN '2026-03-01' AND '2026-06-01'
    GROUP BY u.username, t.transaction_date
),
ranked AS (
    SELECT username,
           total_daily_spend,
           transaction_date,
           DENSE_RANK() OVER (PARTITION BY transaction_date ORDER BY total_daily_spend DESC) AS rnk
    FROM daily_spend
)
SELECT username, total_daily_spend, transaction_date
FROM ranked
WHERE rnk = 1
ORDER BY transaction_date, username

One row per date

ROW_NUMBER() ... = 1 always returns exactly one row per date. When two users post the same total_daily_spend, the engine keeps whichever it happened to number first and drops the other, so the output changes between runs.

Everyone at the top

DENSE_RANK() ... = 1 (or RANK(), which behaves identically at position one) gives every tied user-day the same number, so a shared top spot returns every holder. The result is the same on every run.

Common follow-up questions

• How would you return only the five biggest user-days across the whole window? (Drops the per-date scope for one global ordering and a `LIMIT`, and a tie at the cut then needs a stated rule.)
• What if each date should list its top three spenders, ties included? (With `rnk <= 3`, `DENSE_RANK()` keeps three distinct totals while `RANK()` keeps three positions; say which one you mean.)
• How would you show each user's single best day instead? (The same two stages, with the partition moved to `username` or `user_id`.)
• What changes if `transaction_date` becomes a timestamp? (The June 1 bound stops matching later-in-the-day rows, and the day has to be derived before grouping.)