Dates: Months and Days Between in Python

SQLite keeps dates as text like '2026-03-02', and it has functions to work with them. strftime formats a date with a pattern: %Y is the year, %m the month and %d the day, and anything else in the pattern is copied as is. Group by a date's month, and the rows of each month make one group.

julianday turns a date into a number of days, counted from a fixed day long ago. One julianday minus another is the number of days between the two dates.

SELECT strftime('%Y-%m', '2026-07-19')
-- 2026-07

SELECT julianday('2026-07-19') - julianday('2026-07-01')
-- 18.0

This lesson has two queries. query counts the orders of each month. days_query shows, for each order, how many days after joining the customer placed it: ordered is in orders and joined is in customers, so it needs a join. Together they must print:

month | order_count
2026-03 | 2
2026-04 | 2
2026-05 | 3
(3 rows)
id | name | days_after_joining
1 | Ana Pop | 51
2 | Ben Ortiz | 40
3 | Ana Pop | 85
4 | Chloe Ng | 57
5 | Dan Weber | 54
6 | Ben Ortiz | 107
7 | Chloe Ng | 99
(7 rows)

Your task: in query, select the month of ordered as month and COUNT(*) as order_count from orders, one group per month. In days_query, select each order's id, its customer's name, and the days from joined to ordered as days_after_joining.

Assignment
Follow the Coding Tutorial to fill in both queries, one clause per line, each in its own string.

Hint
You can GROUP BY month, the name you gave the column. In days_query, name the tables o and c and join them ON c.id = o.customer_id.