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.

The two tables
stores      (id, name, channel, city, opened_on)
daily_sales (day, store_id, orders, revenue)
The three stores3 rows
idnamechannelcityopened_on
1WebsitewebNULL2022-03-01
2LeedsstoreLeeds2019-09-14
3HarrogatestoreHarrogate2025-04-12
The query
SELECT * FROM stores;
Leeds, 2–14 June 2025 (Sunday the 8th has no row)12 rows
dayordersrevenue
2025-06-02141120
2025-06-03181350
2025-06-0413949
2025-06-0513962
2025-06-06171326
2025-06-07211365
2025-06-09181206
2025-06-10141176
2025-06-11151230
2025-06-12131079
2025-06-13171139
2025-06-14271998
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).

The website's revenue per month
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.0

Half-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.

Leeds, week by week
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.0

Two 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:

Leeds in 2025, averaged two ways
averagedaysper_day
per trading day3101009.68
per calendar day365857.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…