SQL Quest › SQL Interview Questions › Aggregation & Grouping

Spend by Day of Week

EasyFreeQuerying BasicsAggregation & GroupingConditional LogicDate Functions

Card volume has a weekly shape and every issuer's staffing plan depends on it. strftime('%w', txn_at) turns a timestamp into a weekday code — a one-character string, '0' for Sunday through '6' for Saturday. There is no day-name function in SQLite, so the label comes from a CASE over that code.

Return dow (the '%w' code as it comes, a string), day_name (Sunday … Saturday), txn_count, total_spend (SUM of amount, rounded to 2 decimals) and avg_ticket (AVG of amount, rounded to 2 decimals). Seven rows. Order by dow ascending.

Read the answer before you move on: the busiest day and the biggest-basket day are not the same day.

Solve it in the browser editor →

Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect". Free accounts get 10 free challenge solves, and this can be one of them.

Schema

transactions

txn_id	account_id	amount	txn_at	merchant_id	lat	lng	status
1	149	151.84	2026-03-04T12:39:39.078Z	24	40.342	-74.4092	completed
2	21	10.72	2026-03-04T13:04:50.641Z	7	52.9366	13.3229	completed
3	35	158.78	2026-03-04T13:27:15.528Z	25	52.7397	13.3134	completed

Expected output: 0 Sunday 316 40860.8 129.31; 1 Monday 294 41105.72 139.82; ...

Hint

CASE strftime('%w', txn_at) WHEN '0' THEN 'Sunday' WHEN '1' THEN 'Monday' … ELSE 'Saturday' END AS day_name. Compare against the STRING '0', not the number 0 — strftime returns text.

Concepts

SELECT GROUP BY CASE Date Functions strftime GROUP BY + CASE

Practise the topic: SQL practice questions · GROUP BY exercises · CASE WHEN practice · Date function practice

Read the concept: SQL GROUP BY tutorial

In these company practice sets

Capital One · Ramp · Bloomberg

A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.

Preparing for Capital One? How the CodeSignal data analyst assessment works.

Spend by Day of Week

Schema

Hint

Concepts

In these company practice sets

Related questions