Bookends of the Week

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

L4

Asked in

Meta

Problem

Find what percentage of each week's spending in transactions landed on Monday and what percentage landed on Sunday, with each week identified by its week-of-year number. Report both shares as integer percentage points with any fraction dropped rather than rounded, so a 37.9% share reads 37.

Every week has two edges. Weigh what lands on each.

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

Beneath the calendar language this is a share-of-total done twice against one denominator. For each week you need Monday's slice and Sunday's slice of the same weekly pie. The trap is filtering to Monday and Sunday first: the week total then holds only those two days, both shares inflate until they sum to about 100, and the numbers look tidy and mean nothing. The fix is folding both numerators and the full-week denominator into one grouped pass. The quieter tell is the last step: whole points with the fraction dropped is a type change, and reaching for ROUND turns 37.9 into 38.

Break down the requirements

Step 1: Read the date twice

strftime('%W', transaction_date) gives the week bucket ('00' to '53', Monday-start), and strftime('%w', transaction_date) gives the day of week as text where Sunday is '0' and Monday is '1'. Comparing against those two codes is enough.

Step 2: One grouped pass, three sums

Group by week_num. Inside that group SUM(total_amount) is the denominator, and two conditional sums, SUM(CASE WHEN ... = '1' ...) and SUM(CASE WHEN ... = '0' ...), are the Monday and Sunday numerators. The ELSE 0 keeps a week with no Monday at 0 instead of dropping it.

Step 3: Scale, then cast to INTEGER

Divide monday_total * 100.0 by week_total, then CAST(... AS INTEGER). The cast is the requirement: it drops the fraction toward zero and hands back an integer, exactly what 'whole points, not rounded' asks for.

The solution

WITH weekly AS (
  SELECT
    strftime('%W', transaction_date) AS week_num,
    SUM(total_amount) AS week_total,
    SUM(CASE WHEN strftime('%w', transaction_date) = '1' THEN total_amount ELSE 0 END) AS monday_total,
    SUM(CASE WHEN strftime('%w', transaction_date) = '0' THEN total_amount ELSE 0 END) AS sunday_total
  FROM transactions
  GROUP BY week_num
)
SELECT
  week_num,
  CAST(monday_total * 100.0 / week_total AS INTEGER) AS monday_pct,
  CAST(sunday_total * 100.0 / week_total AS INTEGER) AS sunday_pct
FROM weekly;

Round it

ROUND(monday_total * 100.0 / week_total) returns 38.0 for a 37.9 share: the wrong number, and still a decimal.

Cast it

CAST(monday_total * 100.0 / week_total AS INTEGER) returns 37: the fraction is dropped and the type is an integer, both of which the grade checks.

Common follow-up questions

Common follow-up questions

• How would you report every weekday's share in one row? (Generalizes the two conditional sums to seven while the denominator stays fixed.)
• What happens for a week with zero Monday transactions? (The conditional sum stays 0, so the share is 0 without any calendar join.)
• How does `%W` behave at year boundaries, and what breaks over multiple years? (Week '00' holds the days before the first Monday, and every year reuses the same week numbers, so a year column is needed to keep years apart.)