Challenge: a monthly report that doesn't break

The report management wants

Café Altura's management wants a table for its annual meeting: each shop's sales for each month of 2025. All three shops, all twelve months, even in a month when a shop was closed. That's 36 rows, and the Coyoacán row for August says 0.

Before you write the query, go through the list of things that can break a report with dates. Not all of them apply to every report; checking them is what keeps it from breaking.

Check the type and the format
If the date is text, convert it with an explicit format you chose from the data, not from one row.
Move times to the business's time zone
If they come in UTC, convert them with the zone name, such as America/Mexico_City, before you take days or months.
Filter with half-open intervals
From the first instant of the period up to, but not including, the first instant of the next: >= '2025-01-01' AND < '2026-01-01'.
Group by a period that includes the year
Truncate to the month, the quarter, or the week's Monday. Never group by the month number alone.
Join a calendar
So every period shows up, with rows or without, and decide what value an empty period gets.
Compare matching stretches
The same stretch of the calendar, complete, and say so in the report.

Try it

The query below already has a calendar of the months of 2025 (meses) and the sales by shop and month (por_mes), but it returns 35 rows: Coyoacán in August is missing. You have two steps left. First, a shop-by-month calendar: take the distinct shops and combine them with every month using CROSS JOIN, which pairs every row of one table with every row of the other. Then attach por_mes to that calendar with a LEFT JOIN on shop and month, and put 0 where there are no sales.

A good answer has 36 rows sorted by shop and month, with Coyoacán's 2025-08-01 row at 0. If you get stuck on the CROSS JOIN, ask your Maestro.

Your turn

Build the 2025 report of sales by shop and month, with all 3 shops and all 12 months (36 rows) and 0 where there were no sales, sorted by shop and month.

Tables: ventas_diarias (fecha, sucursal, ventas)

ventas_diarias · 1,598 rows

Column	Type
fecha El día de la venta.	DATE
sucursal Roma, Coyoacán o Del Valle.	VARCHAR
ventas Ventas del día en pesos.	BIGINT

First rows

fecha	sucursal	ventas
2025-01-02	Roma	12120
2025-01-02	Coyoacán	10650
2025-01-02	Del Valle	8880

Your query (SQL)

Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your query.

Run it yourself

FROM and JOIN, including CROSS JOIN (DuckDB documentation)

Or in pandas

If you'd rather use pandas, build the same report: group by shop and month, make a calendar with pd.MultiIndex.from_product (every shop with every month), and attach it with reindex(..., fill_value=0). A good answer prints 36 rows, with Coyoacán's August row at 0.

In pandas, build the 2025 report of sales by shop and month, with all 3 shops and all 12 months and 0 where there were no sales. Print how many rows it has and the rows that are 0.

Your code (Python)

Run your code to see what it prints and the value of its last expression. Every run starts fresh. Ctrl+Enter runs it. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your code.

Run your code to see its output here.

Data: ventas-diarias, 1,598 rows

Enter saves a cell and Escape leaves it as it was. Leave a cell empty for a missing value.

The exercise's data, "ventas-diarias". Your edits run with your code.
Row				Remove
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Page 1 of 80

Run it yourself

The questions from the start

At the start of the course you answered six questions. Here are six similar ones, with different data. Compare what you answer now with what you answered then.

Check yourself: A text column in day/month/year format holds 10/02/2026, 03/03/2026, and 25/01/2026. With ORDER BY fecha, which one comes out last?

Check yourself: In your file, 11/12/2026 is December 11 (day first). If you convert it with pd.to_datetime and no format, what date do you get?

Check yourself: The creado column has a date and a time. Which filter counts every order in the week from Monday, March 9 to Sunday, March 15, 2026?

Check yourself: A system that runs on UTC stores an order as 2026-04-01 05:15:00. For a shop in Mexico City, which month does that order count in?

Check yourself: An online store had no orders on May 1. You count orders per day with GROUP BY. What happens to May 1?

Check yourself: Luis was born on November 3, 1984. How old is he on June 30, 2026?

What you can do now

In pandas and in DuckDB, you can now:

Tell whether a date column is text, and why that changes a sort, a filter, or a maximum.
Decide from the data whether a file puts the day or the month first, and convert it with an explicit format.
Store and share dates in ISO 8601.
Filter dates that have a time without losing the last day, using half-open intervals.
Convert UTC to Mexico City time with the zone name.
Group by month, quarter, and ISO week without mixing up years.
Make periods with no data show up, and decide what value they get.
Calculate days, whole months, and ages, and compare against the same period last year.

What's next

In this course you used WITH, LEFT JOIN, and GROUP BY as tools. If you want to really learn them from the ground up, or they gave you trouble in the exercises, SQL for Data People: Think in Tables, Not Spreadsheets teaches SQL from scratch in DuckDB: filtering, grouping, joins, CTEs, and CASE WHEN. Not sure it's right for you? Tell your Maestro what you do with data.

SQL for Data People: Think in Tables, Not Spreadsheets

Reflect: Think of a report with dates that you make or read at work. Which step from this page's list is it missing, and what would change in its numbers if you added it?

Only you and your Maestro read this. It's kept on your account and deleted with it.

Write your answer