Amazon easy GROUP BYstrftimeCOALESCESUMCOUNTORDER BY

For each customer, calculate the total number of orders and total order amount per month. Treat any NULL order_amount as 0. Return customer_id, order_month (in YYYY-MM format), total_orders, and total_amount, sorted by customer_id and then order_month.

Amazon · Order & Customer Analytics — practice this real-world SQL scenario live in your browser.

📖 Story Amazon · Order & Customer Analytics

Monday morning at Amazon's Customer Analytics team. Leadership wants a monthly breakdown of order volume and spend for every customer, to feed into an upcoming loyalty-tier review. Some orders in the system have a missing amount — likely a data sync issue — and those need to be treated as zero rather than breaking the totals.

🎯 Your Mission

📋 Table Structure

🗂 customers

customer_id INTEGER 1

customer_name TEXT Aaron Wells

is_prime INTEGER 1

signup_date TEXT 2023-01-05

🗂 orders

order_id INTEGER 101

customer_id INTEGER 1

order_date TEXT 2024-01-05

order_amount REAL 120.50

⚡ Step-by-Step Walkthrough

Extract the order month from order_date

query.sql

SELECT order_id,
       customer_id,
       order_date,
       strftime('%Y-%m', order_date) AS order_month,
       order_amount
FROM orders
ORDER BY customer_id, order_date;

💡 Explanation

The orders table stores one row per order with a full order_date, but the question needs monthly totals, so the first step is to turn each date into a YYYY-MM month bucket.
strftime('%Y-%m', order_date) extracts just the year and month from the date string. SQLite has no DATE_TRUNC, so strftime is the standard way to bucket dates by month.
Nothing is aggregated yet — this just confirms the month extraction works. Check a few rows: an order placed on 2024-01-05 should show order_month '2024-01'.
Order 105 has a NULL order_amount on purpose. The next step decides how to treat it — for now just notice it sitting there in the raw output.

Group by customer and month, treating NULL spend as zero

query.sql

SELECT customer_id,
       strftime('%Y-%m', order_date) AS order_month,
       COUNT(order_id) AS total_orders,
       SUM(COALESCE(order_amount, 0)) AS total_amount
FROM orders
GROUP BY customer_id, strftime('%Y-%m', order_date)
ORDER BY customer_id, order_month;

💡 Explanation

GROUP BY customer_id, strftime('%Y-%m', order_date) collapses all of a customer's orders in the same month into a single row.
COUNT(order_id) counts orders per group. Since order_id is never NULL, this gives an accurate order count even for the row with the missing amount.
SUM(order_amount) alone would silently skip NULL rows instead of counting them as zero — SQLite's SUM() ignores NULLs rather than erroring, which understates spend with no warning. COALESCE(order_amount, 0) converts NULL to 0 before summing, so a missing amount counts as zero spend instead of vanishing.
Run this and check customer 2's January total: with the NULL treated as 0, it should land on exactly the 200.00 from their one priced order — not more, not less.

Final query with clean output

query.sql

SELECT customer_id,
       strftime('%Y-%m', order_date) AS order_month,
       COUNT(order_id) AS total_orders,
       ROUND(SUM(COALESCE(order_amount, 0)), 2) AS total_amount
FROM orders
GROUP BY customer_id, strftime('%Y-%m', order_date)
ORDER BY customer_id, order_month;

💡 Explanation

ROUND(SUM(COALESCE(order_amount, 0)), 2) rounds the total to two decimal places — floating-point addition can return extra trailing decimals even when every input has exactly two.
ORDER BY customer_id, order_month sorts the output so each customer's months appear together in chronological order, instead of whatever order SQLite happens to return groups in.
Look at customer 1, 3, and 5's January rows: all three land on 165.50 in total_amount despite feeding in different order counts (2, 2, and 1 respectively) — a genuine tie in the output, not a bug in the query.
This is the complete answer: customer_id, order_month, total_orders, and total_amount for every customer-month combination that has at least one order. A customer with zero orders in a given month simply produces no row for that month.

▶ Practice this live ← Browse all questions