SQL for Analysis · 15 min · 170 XP
Dates, weeks and months
Bucket days into weeks and months, and know which average you're reporting.
The shop's 15 orders are too few to see anything happen over time, so this module uses a second database: two years of daily sales from a small chain of outdoor-gear stores, 2024 and 2025. It has a website, a store in Leeds, and a store in Harrogate that opened on 12 April 2025. There's one row per store per day it traded.
stores (id, name, channel, city, opened_on) daily_sales (day, store_id, orders, revenue)
The three stores3 rows
| id | name | channel | city | opened_on |
|---|---|---|---|---|
| 1 | Website | web | NULL | 2022-03-01 |
| 2 | Leeds | store | Leeds | 2019-09-14 |
| 3 | Harrogate | store | Harrogate | 2025-04-12 |
The query
SELECT * FROM stores;
Leeds, 2–14 June 2025 (Sunday the 8th has no row)12 rows
| day | orders | revenue |
|---|---|---|
| 2025-06-02 | 14 | 1120 |
| 2025-06-03 | 18 | 1350 |
| 2025-06-04 | 13 | 949 |
| 2025-06-05 | 13 | 962 |
| 2025-06-06 | 17 | 1326 |
| 2025-06-07 | 21 | 1365 |
| 2025-06-09 | 18 | 1206 |
| 2025-06-10 | 14 | 1176 |
| 2025-06-11 | 15 | 1230 |
| 2025-06-12 | 13 | 1079 |
| 2025-06-13 | 17 | 1139 |
| 2025-06-14 | 27 | 1998 |
The query
SELECT day, orders, revenue FROM daily_sales WHERE store_id = 2 AND day >= '2025-06-02' AND day < '2025-06-15' ORDER BY day;
Dates are text
SQLite has no date type. Dates are text in the form YYYY-MM-DD, and that form was chosen on purpose: because the year comes first and every part is zero-padded, sorting and comparing the text gives the same answer as comparing the dates. '2025-06-09' < '2025-06-10' is true. Stored as '9/6/2025', neither sorting nor < would work.
Buckets with strftime
strftime pulls parts out of a date, which is how you bucket days. strftime('%Y-%m', day) gives the month, '%Y' the year, and '%w' the weekday as a number from 0 (Sunday) to 6 (Saturday).
SELECT strftime('%Y-%m', day) AS month,
SUM(revenue) AS revenue
FROM daily_sales
WHERE store_id = 1
AND day >= '2025-01-01' AND day < '2025-04-01'
GROUP BY month;
-- month revenue
-- 2025-01 22709.0
-- 2025-02 22447.0
-- 2025-03 28682.0Half-open ranges
Notice the range: >= the first day and < the day after the last. That's called a half-open range, and it's worth the habit. BETWEEN '2025-06-01' AND '2025-06-30' works on plain dates, but if the column holds timestamps, '2025-06-30 14:05' sorts after '2025-06-30' and everything after midnight on the last day silently drops out.
Date arithmetic and weeks
date() does arithmetic with modifiers. date(day, '+1 day') is tomorrow and date(day, 'start of month') the 1st. Weeks need two steps: date(day, '-6 days', 'weekday 1') goes back six days, then forward to the next Monday (staying put if it's already one), which lands on the Monday that starts the day's week.
For the number of days between two dates, subtract their julianday() values. julianday('2025-07-01') - julianday('2025-06-01') is 30.0: the length of June.
SELECT date(day, '-6 days', 'weekday 1') AS week_start,
COUNT(*) AS days,
SUM(revenue) AS revenue
FROM daily_sales
WHERE store_id = 2
AND day >= '2025-06-02' AND day < '2025-06-16'
GROUP BY week_start;
-- week_start days revenue
-- 2025-06-02 6 7072.0
-- 2025-06-09 6 7828.0Two averages, both true
Each week has 7 days but only 6 rows, because Leeds is closed on Sundays and a closed day has no row at all. So AVG(revenue) divides by the days Leeds traded, not the days that passed. In 2025 that's 310 days: 365, less 52 Sundays and the three holidays it always closes for, 1 January and 25–26 December. The difference it makes:
| average | days | per_day |
|---|---|---|
| per trading day | 310 | 1009.68 |
| per calendar day | 365 | 857.54 |
The query
SELECT 'per trading day' AS average, COUNT(*) AS days,
ROUND(AVG(revenue), 2) AS per_day
FROM daily_sales
WHERE store_id = 2 AND day >= '2025-01-01' AND day < '2026-01-01'
UNION ALL
SELECT 'per calendar day', 365, ROUND(SUM(revenue) / 365, 2)
FROM daily_sales
WHERE store_id = 2 AND day >= '2025-01-01' AND day < '2026-01-01';Per trading day says how good a typical open day is. Per calendar day says what the store earns over time, closed days included. Neither is wrong; the mistake is reporting one when the reader thinks it's the other, so put which one it is in the column's name.
Loading your workspace…