How many days or months are there between two dates in SQL and pandas?

The loyalty program

Café Altura has a loyalty program. The clientes (customers) table has 24 synthetic customers with their fecha_nacimiento (birth date), their fecha_alta (the date they joined the program), and their ultima_visita (last visit). All three columns are already dates.

In this module, "today" is always June 30, 2026, the last day in ventas_diarias. That's on purpose: if a report uses the date you happen to run it, it gives a different answer every time. Write the reference date into the query and anyone can reproduce the result.

The simplest question about two dates is how much time passed between them. Make a prediction.

Three date calculations in DuckDB, run from Python. Write what you think each one prints and why.

Subtracting two dates

Example 1 of 3

restar.py

import duckdb

print(duckdb.sql("SELECT DATE '2026-06-30' - DATE '2026-01-01' AS dias").fetchone()[0])

How many days do you think there are from January 1 to June 30, 2026?

Why?

A guess is fine: it's kept as you wrote it, so you can compare. Only you and your Maestro see your predictions.

Example 2 of 3, opens once you explain this one

Make your prediction

Boundaries or whole periods

DuckDB has two functions that look alike and answer different questions. Its documentation defines them like this:

date_diff(part, start, end) counts the boundaries of that part between the two dates: how many starts of a month, a year, or a day you cross.
date_sub(part, start, end) counts how many whole parts fit, and drops whatever is left over.

For days they agree, and so does plain subtraction. For months and years they don't. If the question is "in how many different months was the account active?", use date_diff. If it's "how many full months has it been?", use date_sub. And if the business rule is in days ("inactive after 90 days"), count days: three months aren't 90 days. Not sure which one fits your question? Ask your Maestro.

Subtracting two date columns gives a duration; .dt.days turns it into days:

Python

data["dias_en_programa"] = (data["ultima_visita"] - data["fecha_alta"]).dt.days

For the reference date, use pd.Timestamp("2026-06-30") instead of today's date.

SQL

SELECT
  nombre,
  ultima_visita - fecha_alta AS dias_en_programa,
  DATE '2026-06-30' - ultima_visita AS dias_sin_venir
FROM clientes;

date_diff('day', fecha_alta, ultima_visita) gives the same result as the subtraction.

Try it

Café Altura calls a customer "dormant" when they haven't come in for more than 90 days. The query below counts them with date_diff('month', ...) > 3, which isn't the rule. Change it to count days from the last visit to June 30, 2026. A good answer counts 21 customers; the months version counts 20.

Your turn

Count the dormant customers: those who, as of June 30, 2026, haven't come in for more than 90 days.

Tables: clientes (cliente_id, nombre, fecha_nacimiento, fecha_alta, ultima_visita)

clientes · 24 rows

Column	Type
cliente_id	BIGINT
nombre	VARCHAR
fecha_nacimiento Fecha de nacimiento.	DATE
fecha_alta Día en que se inscribió al programa de clientes frecuentes.	DATE
ultima_visita Día de su visita más reciente.	DATE

First rows

cliente_id	nombre	fecha_nacimiento	fecha_alta	ultima_visita
1	Ana Ruiz	1990-07-01	2026-01-31	2026-02-01
2	Beto Garza	2000-02-29	2025-01-28	2025-06-15
3	Carla Méndez	1985-06-30	2024-02-06	2025-10-21

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

Try it on your own

Now in pandas, from scratch: work out how many days passed between each customer's fecha_alta and ultima_visita, and print who has the most days and how many. A good answer gives one name and a whole number of days, not a duration with hours. If you get something like 100 days 00:00:00, share your code with your Maestro.

Calculate the days between fecha_alta and ultima_visita for each customer, and print the name of the customer with the most days and how many days that is.

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: clientes-frecuentes, 24 rows

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

The exercise's data, "clientes-frecuentes". 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 2

Run it yourself

Check yourself: What does date_diff('month', DATE '2026-03-31', DATE '2026-04-01') return?

Date functions: date_diff, date_sub, date_trunc (DuckDB documentation)