Weekly Transaction Volume

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Amazon

Problem

In transactions, how many units (quantity) moved in each week of Q1 2026, where a week runs Sunday through Saturday and is labelled by the date of the Sunday that opens it? The weeks read as a timeline, earliest first.

Weekly volume. The pulse.

Tables

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

Why this problem exists in real interviews

This tests date truncation combined with a date range filter and aggregation. Interviewers check whether you can compute weekly buckets starting on Sunday and filter to a specific quarter.

Break down the requirements

Step 1: Filter to Q1

Filter transactions to Q1 of 2026: transaction_date >= '2026-01-01' AND < '2026-04-01' (half-open range, no time-of-day boundary issues since transaction_date is a plain date).

Step 2: Truncate to week starting Sunday

Map each transaction to the Sunday that STARTS its week: date(transaction_date, '-' || strftime('%w', transaction_date) || ' days'). strftime('%w', d) is 0 for Sunday..6 for Saturday, so subtracting that many days rolls back to the week's Sunday (a Sunday maps to itself).

Step 3: Sum quantity per week

Group by that week_start and SUM(quantity) to get total transaction quantity per week.

The solution

SELECT date(transaction_date, '-' || strftime('%w', transaction_date) || ' days') AS week_start,
       SUM(quantity) AS total_quantity
FROM transactions
WHERE transaction_date >= '2026-01-01' AND transaction_date < '2026-04-01'
GROUP BY week_start
ORDER BY week_start

Common follow-up questions

• How would you compute week-over-week growth? (Use LAG on the weekly totals: `current_week` - LAG(`total_quantity`) OVER (ORDER BY `week_start`).)
• What if you needed daily granularity instead of weekly? (Remove the date truncation and GROUP BY `transaction_date` directly.)
• How would you handle weeks that span the quarter boundary? (The date filter truncates mid-week; discuss whether to include or exclude partial weeks.)