Friday Spending Analysis

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

Domain

SQL

Difficulty

hard

Seniority

L3

Asked in

IBM

Problem

Each row of transactions is one purchase with its total_amount and transaction_date, and only the Fridays dated in January, February and March count here. Report the average Friday amount for each week of that quarter, with the week as an integer counted from the year's first Monday (the January days before it fall in week 0).

Friday spending during Q1.

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

What this is really asking

This is date-part bucketing wearing a spending-report costume. The table stores a date and nothing else, so the weekday, the month and the week all have to be pulled out of transaction_date. Anyone can write the average. What separates candidates is the quarter fence: week 13 starts on 30 March and its Friday is 3 April, so fence the quarter by week number and an April Friday walks into the result. The quieter trap is the week itself: it comes back as zero-padded text, and it counts from the year's first Monday, not ISO.

Build it in three moves

Step 1: Keep only Fridays

strftime('%w', transaction_date) returns the weekday as text with Sunday as '0', so Friday is '5'. Compare text to text and the filter is unambiguous on every engine.

Step 2: Fence the quarter by month

strftime('%m', transaction_date) IN ('01', '02', '03') fences the first quarter by the calendar, which is the definition the question uses. A bound like week 13 or lower looks equivalent and keeps the 3 April Friday.

Step 3: Bucket by week_number and average

%W is the Monday-based week of year, and it comes back as text: '07'. Wrapping it in CAST(... AS INTEGER) is what makes week_number the integer 7 the question asks for. Name it once and group by that name, then AVG(total_amount) gives the mean per week, not the sum.

SELECT
    CAST(strftime('%W', transaction_date) AS INTEGER) AS week_number,
    AVG(total_amount) AS avg_amount
FROM transactions
WHERE strftime('%w', transaction_date) = '5'
  AND strftime('%m', transaction_date) IN ('01', '02', '03')
GROUP BY week_number;

Where the interviewer goes next

Common follow-up questions

• How would you adapt this for a configurable day of week and quarter? (Parameterize the weekday and the month list; the week bucket stays the same.)
• What if a week has no Friday transactions? (The query silently omits it. Generate the week series with a recursive CTE and `LEFT JOIN` the averages onto it.)
• How would you report median Friday spend per week instead of mean? (`PERCENTILE_CONT(0.5)` where supported, or number the rows per week with `ROW_NUMBER()` and pick the middle.)